doc/src/sgml/ref/create_schema.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-CREATESCHEMA">
   7  <refmeta>
   8   <refentrytitle id="sql-createschema-title">CREATE SCHEMA</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>CREATE SCHEMA</refname>
  15   <refpurpose>define a new schema</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-createschema">
  19   <primary>CREATE SCHEMA</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 CREATE SCHEMA <replaceable class="parameter">schemaname</replaceable> [ AUTHORIZATION <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
  25 CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">username</replaceable> [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
  26 </synopsis>
  27  </refsynopsisdiv>
  28
  29  <refsect1>
  30   <title>Description</title>
  31
  32   <para>
  33    <command>CREATE SCHEMA</command> enters a new schema
  34    into the current database.
  35    The schema name must be distinct from the name of any existing schema
  36    in the current database.
  37   </para>
  38
  39   <para>
  40    A schema is essentially a namespace:
  41    it contains named objects (tables, data types, functions, and operators)
  42    whose names can duplicate those of other objects existing in other
  43    schemas.  Named objects are accessed either by <quote>qualifying</>
  44    their names with the schema name as a prefix, or by setting a search
  45    path that includes the desired schema(s).  A <literal>CREATE</> command
  46    specifying an unqualified object name creates the object
  47    in the current schema (the one at the front of the search path,
  48    which can be determined with the function <function>current_schema</function>).
  49   </para>
  50
  51   <para>
  52    Optionally, <command>CREATE SCHEMA</command> can include subcommands
  53    to create objects within the new schema.  The subcommands are treated
  54    essentially the same as separate commands issued after creating the
  55    schema, except that if the <literal>AUTHORIZATION</> clause is used,
  56    all the created objects will be owned by that user.
  57   </para>
  58  </refsect1>
  59
  60  <refsect1>
  61   <title>Parameters</title>
  62
  63     <variablelist>
  64      <varlistentry>
  65       <term><replaceable class="parameter">schemaname</replaceable></term>
  66       <listitem>
  67        <para>
  68         The name of a schema to be created.  If this is omitted, the user name
  69         is used as the schema name.  The name cannot
  70         begin with <literal>pg_</literal>, as such names
  71         are reserved for system schemas.
  72        </para>
  73       </listitem>
  74      </varlistentry>
  75
  76      <varlistentry>
  77       <term><replaceable class="parameter">username</replaceable></term>
  78       <listitem>
  79        <para>
  80         The name of the user who will own the schema.  If omitted,
  81         defaults to the user executing the command.  Only superusers
  82         can create schemas owned by users other than themselves.
  83        </para>
  84       </listitem>
  85      </varlistentry>
  86
  87      <varlistentry>
  88       <term><replaceable class="parameter">schema_element</replaceable></term>
  89       <listitem>
  90        <para>
  91         An SQL statement defining an object to be created within the
  92         schema. Currently, only <command>CREATE
  93         TABLE</>, <command>CREATE VIEW</>, <command>CREATE
  94         INDEX</>, <command>CREATE SEQUENCE</>, <command>CREATE
  95         TRIGGER</> and <command>GRANT</> are accepted as clauses
  96         within <command>CREATE SCHEMA</>. Other kinds of objects may
  97         be created in separate commands after the schema is created.
  98        </para>
  99       </listitem>
 100      </varlistentry>
 101     </variablelist>
 102  </refsect1>
 103
 104  <refsect1>
 105   <title>Notes</title>
 106
 107   <para>
 108    To create a schema, the invoking user must have the
 109    <literal>CREATE</> privilege for the current database.
 110    (Of course, superusers bypass this check.)
 111   </para>
 112  </refsect1>
 113
 114  <refsect1>
 115   <title>Examples</title>
 116
 117   <para>
 118    Create a schema:
 119 <programlisting>
 120 CREATE SCHEMA myschema;
 121 </programlisting>
 122   </para>
 123
 124   <para>
 125    Create a schema for user <literal>joe</>; the schema will also be
 126    named <literal>joe</>:
 127 <programlisting>
 128 CREATE SCHEMA AUTHORIZATION joe;
 129 </programlisting>
 130   </para>
 131
 132   <para>
 133    Create a schema and create a table and view within it:
 134 <programlisting>
 135 CREATE SCHEMA hollywood
 136     CREATE TABLE films (title text, release date, awards text[])
 137     CREATE VIEW winners AS
 138         SELECT title, release FROM films WHERE awards IS NOT NULL;
 139 </programlisting>
 140    Notice that the individual subcommands do not end with semicolons.
 141   </para>
 142
 143   <para>
 144    The following is an equivalent way of accomplishing the same result:
 145 <programlisting>
 146 CREATE SCHEMA hollywood;
 147 CREATE TABLE hollywood.films (title text, release date, awards text[]);
 148 CREATE VIEW hollywood.winners AS
 149     SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;
 150 </programlisting>
 151   </para>
 152
 153  </refsect1>
 154
 155  <refsect1>
 156   <title>Compatibility</title>
 157
 158   <para>
 159    The SQL standard allows a <literal>DEFAULT CHARACTER SET</> clause
 160    in <command>CREATE SCHEMA</command>, as well as more subcommand
 161    types than are presently accepted by
 162    <productname>PostgreSQL</productname>.
 163   </para>
 164
 165   <para>
 166    The SQL standard specifies that the subcommands in <command>CREATE
 167    SCHEMA</command> can appear in any order.  The present
 168    <productname>PostgreSQL</productname> implementation does not
 169    handle all cases of forward references in subcommands; it might
 170    sometimes be necessary to reorder the subcommands in order to avoid
 171    forward references.
 172   </para>
 173
 174   <para>
 175    According to the SQL standard, the owner of a schema always owns
 176    all objects within it.  <productname>PostgreSQL</productname>
 177    allows schemas to contain objects owned by users other than the
 178    schema owner.  This can happen only if the schema owner grants the
 179    <literal>CREATE</> privilege on his schema to someone else.
 180   </para>
 181  </refsect1>
 182
 183  <refsect1>
 184   <title>See Also</title>
 185
 186   <simplelist type="inline">
 187    <member><xref linkend="sql-alterschema" endterm="sql-alterschema-title"></member>
 188    <member><xref linkend="sql-dropschema" endterm="sql-dropschema-title"></member>
 189  </simplelist>
 190  </refsect1>
 191
 192 </refentry>