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