2 $PostgreSQL: pgsql/doc/src/sgml/ref/create_schema.sgml,v 1.13 2004/06/25 21:55:50 tgl 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;
166 <title>Compatibility</title>
169 The SQL standard allows a <literal>DEFAULT CHARACTER SET</> clause
170 in <command>CREATE SCHEMA</command>, as well as more subcommand
171 types than are presently accepted by
172 <productname>PostgreSQL</productname>.
176 The SQL standard specifies that the subcommands in <command>CREATE
177 SCHEMA</command> may appear in any order. The present
178 <productname>PostgreSQL</productname> implementation does not
179 handle all cases of forward references in subcommands; it may
180 sometimes be necessary to reorder the subcommands in order to avoid
185 According to the SQL standard, the owner of a schema always owns
186 all objects within it. <productname>PostgreSQL</productname>
187 allows schemas to contain objects owned by users other than the
188 schema owner. This can happen only if the schema owner grants the
189 <literal>CREATE</> privilege on his schema to someone else.
194 <title>See Also</title>
196 <simplelist type="inline">
197 <member><xref linkend="sql-alterschema" endterm="sql-alterschema-title"></member>
198 <member><xref linkend="sql-dropschema" endterm="sql-dropschema-title"></member>
199 <member><xref linkend="sql-createtablespace" endterm="sql-createtablespace-title"></member>
205 <!-- Keep this comment at the end of the file
210 sgml-minimize-attributes:nil
211 sgml-always-quote-attributes:t
214 sgml-parent-document:nil
215 sgml-default-dtd-file:"../reference.ced"
216 sgml-exposed-tags:nil
217 sgml-local-catalogs:"/usr/lib/sgml/catalog"
218 sgml-local-ecat-files:nil