Debian Codegraph @

doc/src/sgml/ref/create_schema.sgml

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