| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/create_schema.sgml,v 1.18 2006/09/16 00:30:17 momjian Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-CREATESCHEMA"> |
| <refmeta> |
| <refentrytitle id="sql-createschema-title">CREATE SCHEMA</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>CREATE SCHEMA</refname> |
| <refpurpose>define a new schema</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-createschema"> |
| <primary>CREATE SCHEMA</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| CREATE SCHEMA <replaceable class="parameter">schemaname</replaceable> [ AUTHORIZATION <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ] |
| CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">username</replaceable> [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ] |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| <command>CREATE SCHEMA</command> enters a new schema |
| into the current database. |
| The schema name must be distinct from the name of any existing schema |
| in the current database. |
| </para> |
| |
| <para> |
| A schema is essentially a namespace: |
| it contains named objects (tables, data types, functions, and operators) |
| whose names may duplicate those of other objects existing in other |
| schemas. Named objects are accessed either by <quote>qualifying</> |
| their names with the schema name as a prefix, or by setting a search |
| path that includes the desired schema(s). A <literal>CREATE</> command |
| specifying an unqualified object name creates the object |
| in the current schema (the one at the front of the search path, |
| which can be determined with the function <function>current_schema</function>). |
| </para> |
| |
| <para> |
| Optionally, <command>CREATE SCHEMA</command> can include subcommands |
| to create objects within the new schema. The subcommands are treated |
| essentially the same as separate commands issued after creating the |
| schema, except that if the <literal>AUTHORIZATION</> clause is used, |
| all the created objects will be owned by that user. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <variablelist> |
| <varlistentry> |
| <term><replaceable class="parameter">schemaname</replaceable></term> |
| <listitem> |
| <para> |
| The name of a schema to be created. If this is omitted, the user name |
| is used as the schema name. The name cannot |
| begin with <literal>pg_</literal>, as such names |
| are reserved for system schemas. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">username</replaceable></term> |
| <listitem> |
| <para> |
| The name of the user who will own the schema. If omitted, |
| defaults to the user executing the command. Only superusers |
| may create schemas owned by users other than themselves. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">schema_element</replaceable></term> |
| <listitem> |
| <para> |
| An SQL statement defining an object to be created within the |
| schema. Currently, only <command>CREATE |
| TABLE</>, <command>CREATE VIEW</>, <command>CREATE |
| INDEX</>, <command>CREATE SEQUENCE</>, <command>CREATE |
| TRIGGER</> and <command>GRANT</> are accepted as clauses |
| within <command>CREATE SCHEMA</>. Other kinds of objects may |
| be created in separate commands after the schema is created. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| </refsect1> |
| |
| <refsect1> |
| <title>Notes</title> |
| |
| <para> |
| To create a schema, the invoking user must have the |
| <literal>CREATE</> privilege for the current database. |
| (Of course, superusers bypass this check.) |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Examples</title> |
| |
| <para> |
| Create a schema: |
| <programlisting> |
| CREATE SCHEMA myschema; |
| </programlisting> |
| </para> |
| |
| <para> |
| Create a schema for user <literal>joe</>; the schema will also be |
| named <literal>joe</>: |
| <programlisting> |
| CREATE SCHEMA AUTHORIZATION joe; |
| </programlisting> |
| </para> |
| |
| <para> |
| Create a schema and create a table and view within it: |
| <programlisting> |
| CREATE SCHEMA hollywood |
| CREATE TABLE films (title text, release date, awards text[]) |
| CREATE VIEW winners AS |
| SELECT title, release FROM films WHERE awards IS NOT NULL; |
| </programlisting> |
| Notice that the individual subcommands do not end with semicolons. |
| </para> |
| |
| <para> |
| The following is an equivalent way of accomplishing the same result: |
| <programlisting> |
| CREATE SCHEMA hollywood; |
| CREATE TABLE hollywood.films (title text, release date, awards text[]); |
| CREATE VIEW hollywood.winners AS |
| SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL; |
| </programlisting> |
| </para> |
| |
| </refsect1> |
| |
| <refsect1> |
| <title>Compatibility</title> |
| |
| <para> |
| The SQL standard allows a <literal>DEFAULT CHARACTER SET</> clause |
| in <command>CREATE SCHEMA</command>, as well as more subcommand |
| types than are presently accepted by |
| <productname>PostgreSQL</productname>. |
| </para> |
| |
| <para> |
| The SQL standard specifies that the subcommands in <command>CREATE |
| SCHEMA</command> may appear in any order. The present |
| <productname>PostgreSQL</productname> implementation does not |
| handle all cases of forward references in subcommands; it may |
| sometimes be necessary to reorder the subcommands in order to avoid |
| forward references. |
| </para> |
| |
| <para> |
| According to the SQL standard, the owner of a schema always owns |
| all objects within it. <productname>PostgreSQL</productname> |
| allows schemas to contain objects owned by users other than the |
| schema owner. This can happen only if the schema owner grants the |
| <literal>CREATE</> privilege on his schema to someone else. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="sql-alterschema" endterm="sql-alterschema-title"></member> |
| <member><xref linkend="sql-dropschema" endterm="sql-dropschema-title"></member> |
| </simplelist> |
| </refsect1> |
| |
| </refentry> |