2 $PostgreSQL: pgsql/doc/src/sgml/ref/create_schema.sgml,v 1.14 2004/07/12 01:22:53 momjian Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-CREATESCHEMA">
8 <refentrytitle id="sql-createschema-title">CREATE SCHEMA</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
13 <refname>CREATE SCHEMA</refname>
14 <refpurpose>define a new schema</refpurpose>
17 <indexterm zone="sql-createschema">
18 <primary>CREATE SCHEMA</primary>
23 CREATE SCHEMA <replaceable class="parameter">schemaname</replaceable> [ AUTHORIZATION <replaceable class="parameter">username</replaceable> ] [ TABLESPACE <replaceable class="parameter">tablespace</replaceable> ] [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
24 CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">username</replaceable> [ TABLESPACE <replaceable class="parameter">tablespace</replaceable> ] [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
29 <title>Description</title>
32 <command>CREATE SCHEMA</command> will enter a new schema
33 into the current database.
34 The schema name must be distinct from the name of any existing schema
35 in the current database.
39 A schema is essentially a namespace:
40 it contains named objects (tables, data types, functions, and operators)
41 whose names may duplicate those of other objects existing in other
42 schemas. Named objects are accessed either by <quote>qualifying</>
43 their names with the schema name as a prefix, or by setting a search
44 path that includes the desired schema(s). Unqualified objects are
45 created in the current schema (the one at the front of the search path,
46 which can be determined with the function <function>current_schema</function>).
50 Optionally, <command>CREATE SCHEMA</command> can include subcommands
51 to create objects within the new schema. The subcommands are treated
52 essentially the same as separate commands issued after creating the
53 schema, except that if the <literal>AUTHORIZATION</> clause is used,
54 all the created objects will be owned by that user.
59 <title>Parameters</title>
63 <term><replaceable class="parameter">schemaname</replaceable></term>
66 The name of a schema to be created. If this is omitted, the user name
67 is used as the schema name. The name cannot
68 begin with <literal>pg_</literal>, as such names
69 are reserved for system schemas.
75 <term><replaceable class="parameter">username</replaceable></term>
78 The name of the user who will own the schema. If omitted,
79 defaults to the user executing the command. Only superusers
80 may create schemas owned by users other than themselves.
86 <term><replaceable class="parameter">tablespace</replaceable></term>
89 The name of the tablespace that is to be the default tablespace
90 for all new objects created in the schema. If not supplied, the schema
91 will inherit the default tablespace of the database.
97 <term><replaceable class="parameter">schema_element</replaceable></term>
100 An SQL statement defining an object to be created within the
101 schema. Currently, only <command>CREATE
102 TABLE</>, <command>CREATE VIEW</>, <command>CREATE
103 INDEX</>, <command>CREATE SEQUENCE</>, <command>CREATE
104 TRIGGER</> and <command>GRANT</> are accepted as clauses
105 within <command>CREATE SCHEMA</>. Other kinds of objects may
106 be created in separate commands after the schema is created.
117 To create a schema, the invoking user must have the
118 <literal>CREATE</> privilege for the current database.
119 Also, the <literal>TABLESPACE</> option requires having
120 <literal>CREATE</> privilege for the specified tablespace.
121 (Of course, superusers bypass these checks.)
126 <title>Examples</title>
131 CREATE SCHEMA myschema;
136 Create a schema for user <literal>joe</>; the schema will also be
137 named <literal>joe</>:
139 CREATE SCHEMA AUTHORIZATION joe;
144 Create a schema and create a table and view within it:
146 CREATE SCHEMA hollywood
147 CREATE TABLE films (title text, release date, awards text[])
148 CREATE VIEW winners AS
149 SELECT title, release FROM films WHERE awards IS NOT NULL;
151 Notice that the individual subcommands do not end with semicolons.
155 The following is an equivalent way of accomplishing the same result:
157 CREATE SCHEMA hollywood;
158 CREATE TABLE hollywood.films (title text, release date, awards text[]);
159 CREATE VIEW hollywood.winners AS
160 SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;
165 Create a schema <literal>sales</> whose tables, indexes and sequences
166 will be stored in the tablespace <literal>mirrorspace</> by default:
169 CREATE SCHEMA sales TABLESPACE mirrorspace;
176 <title>Compatibility</title>
179 The SQL standard allows a <literal>DEFAULT CHARACTER SET</> clause
180 in <command>CREATE SCHEMA</command>, as well as more subcommand
181 types than are presently accepted by
182 <productname>PostgreSQL</productname>.
186 The SQL standard specifies that the subcommands in <command>CREATE
187 SCHEMA</command> may appear in any order. The present
188 <productname>PostgreSQL</productname> implementation does not
189 handle all cases of forward references in subcommands; it may
190 sometimes be necessary to reorder the subcommands in order to avoid
195 According to the SQL standard, the owner of a schema always owns
196 all objects within it. <productname>PostgreSQL</productname>
197 allows schemas to contain objects owned by users other than the
198 schema owner. This can happen only if the schema owner grants the
199 <literal>CREATE</> privilege on his schema to someone else.
204 <title>See Also</title>
206 <simplelist type="inline">
207 <member><xref linkend="sql-alterschema" endterm="sql-alterschema-title"></member>
208 <member><xref linkend="sql-dropschema" endterm="sql-dropschema-title"></member>
209 <member><xref linkend="sql-createtablespace" endterm="sql-createtablespace-title"></member>
215 <!-- Keep this comment at the end of the file
220 sgml-minimize-attributes:nil
221 sgml-always-quote-attributes:t
224 sgml-parent-document:nil
225 sgml-default-dtd-file:"../reference.ced"
226 sgml-exposed-tags:nil
227 sgml-local-catalogs:"/usr/lib/sgml/catalog"
228 sgml-local-ecat-files:nil