2 doc/src/sgml/ref/create_schema.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-CREATESCHEMA">
8 <refentrytitle>CREATE SCHEMA</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>CREATE SCHEMA</refname>
15 <refpurpose>define a new schema</refpurpose>
18 <indexterm zone="sql-createschema">
19 <primary>CREATE SCHEMA</primary>
24 CREATE SCHEMA <replaceable class="parameter">schema_name</replaceable> [ AUTHORIZATION <replaceable class="parameter">user_name</replaceable> ] [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
25 CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">user_name</replaceable> [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
26 CREATE SCHEMA IF NOT EXISTS <replaceable class="parameter">schema_name</replaceable> [ AUTHORIZATION <replaceable class="parameter">user_name</replaceable> ]
27 CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable class="parameter">user_name</replaceable>
32 <title>Description</title>
35 <command>CREATE SCHEMA</command> enters a new schema
36 into the current database.
37 The schema name must be distinct from the name of any existing schema
38 in the current database.
42 A schema is essentially a namespace:
43 it contains named objects (tables, data types, functions, and operators)
44 whose names can duplicate those of other objects existing in other
45 schemas. Named objects are accessed either by <quote>qualifying</>
46 their names with the schema name as a prefix, or by setting a search
47 path that includes the desired schema(s). A <literal>CREATE</> command
48 specifying an unqualified object name creates the object
49 in the current schema (the one at the front of the search path,
50 which can be determined with the function <function>current_schema</function>).
54 Optionally, <command>CREATE SCHEMA</command> can include subcommands
55 to create objects within the new schema. The subcommands are treated
56 essentially the same as separate commands issued after creating the
57 schema, except that if the <literal>AUTHORIZATION</> clause is used,
58 all the created objects will be owned by that user.
63 <title>Parameters</title>
67 <term><replaceable class="parameter">schema_name</replaceable></term>
70 The name of a schema to be created. If this is omitted, the
71 <replaceable class="parameter">user_name</replaceable>
72 is used as the schema name. The name cannot
73 begin with <literal>pg_</literal>, as such names
74 are reserved for system schemas.
80 <term><replaceable class="parameter">user_name</replaceable></term>
83 The role name of the user who will own the new schema. If omitted,
84 defaults to the user executing the command. To create a schema
85 owned by another role, you must be a direct or indirect member of
86 that role, or be a superuser.
92 <term><replaceable class="parameter">schema_element</replaceable></term>
95 An SQL statement defining an object to be created within the
96 schema. Currently, only <command>CREATE
97 TABLE</>, <command>CREATE VIEW</>, <command>CREATE
98 INDEX</>, <command>CREATE SEQUENCE</>, <command>CREATE
99 TRIGGER</> and <command>GRANT</> are accepted as clauses
100 within <command>CREATE SCHEMA</>. Other kinds of objects may
101 be created in separate commands after the schema is created.
107 <term><literal>IF NOT EXISTS</literal></term>
110 Do nothing (except issuing a notice) if a schema with the same name
111 already exists. <replaceable class="parameter">schema_element</>
112 subcommands cannot be included when this option is used.
123 To create a schema, the invoking user must have the
124 <literal>CREATE</> privilege for the current database.
125 (Of course, superusers bypass this check.)
130 <title>Examples</title>
135 CREATE SCHEMA myschema;
140 Create a schema for user <literal>joe</>; the schema will also be
141 named <literal>joe</>:
143 CREATE SCHEMA AUTHORIZATION joe;
148 Create a schema named <literal>test</> that will be owned by user
149 <literal>joe</>, unless there already is a schema named <literal>test</>.
150 (It does not matter whether <literal>joe</> owns the pre-existing schema.)
152 CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;
157 Create a schema and create a table and view within it:
159 CREATE SCHEMA hollywood
160 CREATE TABLE films (title text, release date, awards text[])
161 CREATE VIEW winners AS
162 SELECT title, release FROM films WHERE awards IS NOT NULL;
164 Notice that the individual subcommands do not end with semicolons.
168 The following is an equivalent way of accomplishing the same result:
170 CREATE SCHEMA hollywood;
171 CREATE TABLE hollywood.films (title text, release date, awards text[]);
172 CREATE VIEW hollywood.winners AS
173 SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;
174 </programlisting></para>
179 <title>Compatibility</title>
182 The SQL standard allows a <literal>DEFAULT CHARACTER SET</> clause
183 in <command>CREATE SCHEMA</command>, as well as more subcommand
184 types than are presently accepted by
185 <productname>PostgreSQL</productname>.
189 The SQL standard specifies that the subcommands in <command>CREATE
190 SCHEMA</command> can appear in any order. The present
191 <productname>PostgreSQL</productname> implementation does not
192 handle all cases of forward references in subcommands; it might
193 sometimes be necessary to reorder the subcommands in order to avoid
198 According to the SQL standard, the owner of a schema always owns
199 all objects within it. <productname>PostgreSQL</productname>
200 allows schemas to contain objects owned by users other than the
201 schema owner. This can happen only if the schema owner grants the
202 <literal>CREATE</> privilege on his schema to someone else, or a
203 superuser chooses to create objects in it.
207 The <literal>IF NOT EXISTS</literal> option is a
208 <productname>PostgreSQL</productname> extension.
213 <title>See Also</title>
215 <simplelist type="inline">
216 <member><xref linkend="sql-alterschema"></member>
217 <member><xref linkend="sql-dropschema"></member>