create_schema.sgml

PostgreSQL7.4.6 for Linux

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_schema.sgml,v 1.8 2003/09/09 18:28:52 tgl 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> will enter 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).  Unqualified objects are
   created 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.
       </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</>, 
	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 <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 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>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->