1<!--
2doc/src/sgml/ref/create_schema.sgml
3PostgreSQL documentation
4-->
5
6<refentry id="sql-createschema">
7 <indexterm zone="sql-createschema">
8  <primary>CREATE SCHEMA</primary>
9 </indexterm>
10
11 <refmeta>
12  <refentrytitle>CREATE SCHEMA</refentrytitle>
13  <manvolnum>7</manvolnum>
14  <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18  <refname>CREATE SCHEMA</refname>
19  <refpurpose>define a new schema</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23<synopsis>
24CREATE SCHEMA <replaceable class="parameter">schema_name</replaceable> [ AUTHORIZATION <replaceable class="parameter">role_specification</replaceable> ] [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
25CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">role_specification</replaceable> [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
26CREATE SCHEMA IF NOT EXISTS <replaceable class="parameter">schema_name</replaceable> [ AUTHORIZATION <replaceable class="parameter">role_specification</replaceable> ]
27CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable class="parameter">role_specification</replaceable>
28
29<phrase>where <replaceable class="parameter">role_specification</replaceable> can be:</phrase>
30
31    <replaceable class="parameter">user_name</replaceable>
32  | CURRENT_USER
33  | SESSION_USER
34</synopsis>
35 </refsynopsisdiv>
36
37 <refsect1>
38  <title>Description</title>
39
40  <para>
41   <command>CREATE SCHEMA</command> enters a new schema
42   into the current database.
43   The schema name must be distinct from the name of any existing schema
44   in the current database.
45  </para>
46
47  <para>
48   A schema is essentially a namespace:
49   it contains named objects (tables, data types, functions, and operators)
50   whose names can duplicate those of other objects existing in other
51   schemas.  Named objects are accessed either by <quote>qualifying</quote>
52   their names with the schema name as a prefix, or by setting a search
53   path that includes the desired schema(s).  A <literal>CREATE</literal> command
54   specifying an unqualified object name creates the object
55   in the current schema (the one at the front of the search path,
56   which can be determined with the function <function>current_schema</function>).
57  </para>
58
59  <para>
60   Optionally, <command>CREATE SCHEMA</command> can include subcommands
61   to create objects within the new schema.  The subcommands are treated
62   essentially the same as separate commands issued after creating the
63   schema, except that if the <literal>AUTHORIZATION</literal> clause is used,
64   all the created objects will be owned by that user.
65  </para>
66 </refsect1>
67
68 <refsect1>
69  <title>Parameters</title>
70
71    <variablelist>
72     <varlistentry>
73      <term><replaceable class="parameter">schema_name</replaceable></term>
74      <listitem>
75       <para>
76        The name of a schema to be created.  If this is omitted, the
77        <replaceable class="parameter">user_name</replaceable>
78        is used as the schema name.  The name cannot
79        begin with <literal>pg_</literal>, as such names
80        are reserved for system schemas.
81       </para>
82      </listitem>
83     </varlistentry>
84
85     <varlistentry>
86      <term><replaceable class="parameter">user_name</replaceable></term>
87      <listitem>
88       <para>
89        The role name of the user who will own the new schema.  If omitted,
90        defaults to the user executing the command.  To create a schema
91        owned by another role, you must be a direct or indirect member of
92        that role, or be a superuser.
93       </para>
94      </listitem>
95     </varlistentry>
96
97     <varlistentry>
98      <term><replaceable class="parameter">schema_element</replaceable></term>
99      <listitem>
100       <para>
101        An SQL statement defining an object to be created within the
102        schema. Currently, only <command>CREATE
103        TABLE</command>, <command>CREATE VIEW</command>, <command>CREATE
104        INDEX</command>, <command>CREATE SEQUENCE</command>, <command>CREATE
105        TRIGGER</command> and <command>GRANT</command> are accepted as clauses
106        within <command>CREATE SCHEMA</command>. Other kinds of objects may
107        be created in separate commands after the schema is created.
108       </para>
109      </listitem>
110     </varlistentry>
111
112     <varlistentry>
113      <term><literal>IF NOT EXISTS</literal></term>
114      <listitem>
115       <para>
116        Do nothing (except issuing a notice) if a schema with the same name
117        already exists.  <replaceable class="parameter">schema_element</replaceable>
118        subcommands cannot be included when this option is used.
119       </para>
120      </listitem>
121     </varlistentry>
122    </variablelist>
123 </refsect1>
124
125 <refsect1>
126  <title>Notes</title>
127
128  <para>
129   To create a schema, the invoking user must have the
130   <literal>CREATE</literal> privilege for the current database.
131   (Of course, superusers bypass this check.)
132  </para>
133 </refsect1>
134
135 <refsect1>
136  <title>Examples</title>
137
138  <para>
139   Create a schema:
140<programlisting>
141CREATE SCHEMA myschema;
142</programlisting>
143  </para>
144
145  <para>
146   Create a schema for user <literal>joe</literal>; the schema will also be
147   named <literal>joe</literal>:
148<programlisting>
149CREATE SCHEMA AUTHORIZATION joe;
150</programlisting>
151  </para>
152
153  <para>
154   Create a schema named <literal>test</literal> that will be owned by user
155   <literal>joe</literal>, unless there already is a schema named <literal>test</literal>.
156   (It does not matter whether <literal>joe</literal> owns the pre-existing schema.)
157<programlisting>
158CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;
159</programlisting>
160  </para>
161
162  <para>
163   Create a schema and create a table and view within it:
164<programlisting>
165CREATE SCHEMA hollywood
166    CREATE TABLE films (title text, release date, awards text[])
167    CREATE VIEW winners AS
168        SELECT title, release FROM films WHERE awards IS NOT NULL;
169</programlisting>
170   Notice that the individual subcommands do not end with semicolons.
171  </para>
172
173  <para>
174   The following is an equivalent way of accomplishing the same result:
175<programlisting>
176CREATE SCHEMA hollywood;
177CREATE TABLE hollywood.films (title text, release date, awards text[]);
178CREATE VIEW hollywood.winners AS
179    SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;
180</programlisting></para>
181
182 </refsect1>
183
184 <refsect1>
185  <title>Compatibility</title>
186
187  <para>
188   The SQL standard allows a <literal>DEFAULT CHARACTER SET</literal> clause
189   in <command>CREATE SCHEMA</command>, as well as more subcommand
190   types than are presently accepted by
191   <productname>PostgreSQL</productname>.
192  </para>
193
194  <para>
195   The SQL standard specifies that the subcommands in <command>CREATE
196   SCHEMA</command> can appear in any order.  The present
197   <productname>PostgreSQL</productname> implementation does not
198   handle all cases of forward references in subcommands; it might
199   sometimes be necessary to reorder the subcommands in order to avoid
200   forward references.
201  </para>
202
203  <para>
204   According to the SQL standard, the owner of a schema always owns
205   all objects within it.  <productname>PostgreSQL</productname>
206   allows schemas to contain objects owned by users other than the
207   schema owner.  This can happen only if the schema owner grants the
208   <literal>CREATE</literal> privilege on their schema to someone else, or a
209   superuser chooses to create objects in it.
210  </para>
211
212  <para>
213   The <literal>IF NOT EXISTS</literal> option is a
214   <productname>PostgreSQL</productname> extension.
215  </para>
216 </refsect1>
217
218 <refsect1>
219  <title>See Also</title>
220
221  <simplelist type="inline">
222   <member><xref linkend="sql-alterschema"/></member>
223   <member><xref linkend="sql-dropschema"/></member>
224 </simplelist>
225 </refsect1>
226
227</refentry>
228