doc/src/sgml/ref/create_schema.sgml - master - githubmirror/postgres

doc/src/sgml/ref/create_schema.sgml
251 строка9 KB
Tom Lane
Support more object types within CREATE SCHEMA.
06 апр 2026, 22:16
06 апр 2026, 22:16d516974
<!--
doc/src/sgml/ref/create_schema.sgml
PostgreSQL documentation
-->

<refentry id="sql-createschema">
 <indexterm zone="sql-createschema">
  <primary>CREATE SCHEMA</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>CREATE SCHEMA</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE SCHEMA</refname>
  <refpurpose>define a new schema</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
CREATE SCHEMA <replaceable class="parameter">schema_name</replaceable> [ AUTHORIZATION <replaceable class="parameter">role_specification</replaceable> ] [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">role_specification</replaceable> [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
CREATE SCHEMA IF NOT EXISTS <replaceable class="parameter">schema_name</replaceable> [ AUTHORIZATION <replaceable class="parameter">role_specification</replaceable> ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable class="parameter">role_specification</replaceable>

<phrase>where <replaceable class="parameter">role_specification</replaceable> can be:</phrase>

    <replaceable class="parameter">user_name</replaceable>
  | CURRENT_ROLE
  | CURRENT_USER
  | SESSION_USER
</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 can duplicate those of other objects existing in other
   schemas.  Named objects are accessed either by <quote>qualifying</quote>
   their names with the schema name as a prefix, or by setting a search
   path that includes the desired schema(s).  A <literal>CREATE</literal> 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</literal> 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">schema_name</replaceable></term>
      <listitem>
       <para>
        The name of a schema to be created.  If this is omitted, the
        <replaceable class="parameter">user_name</replaceable>
        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">user_name</replaceable></term>
      <listitem>
       <para>
        The role name of the user who will own the new schema.  If omitted,
        defaults to the user executing the command.  To create a schema
        owned by another role, you must be able to
        <literal>SET ROLE</literal> to that role.
       </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 AGGREGATE</command>,
        <command>CREATE COLLATION</command>,
        <command>CREATE DOMAIN</command>,
        <command>CREATE FUNCTION</command>,
        <command>CREATE INDEX</command>,
        <command>CREATE OPERATOR</command>,
        <command>CREATE PROCEDURE</command>,
        <command>CREATE SEQUENCE</command>,
        <command>CREATE TABLE</command>,
        <command>CREATE TEXT SEARCH CONFIGURATION</command>,
        <command>CREATE TEXT SEARCH DICTIONARY</command>,
        <command>CREATE TEXT SEARCH PARSER</command>,
        <command>CREATE TEXT SEARCH TEMPLATE</command>,
        <command>CREATE TRIGGER</command>,
        <command>CREATE TYPE</command>,
        <command>CREATE VIEW</command>,
        and <command>GRANT</command> are accepted as clauses
        within <command>CREATE SCHEMA</command>. Other kinds of objects may
        be created within the schema in separate commands after the schema
        is created.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>IF NOT EXISTS</literal></term>
      <listitem>
       <para>
        Do nothing (except issuing a notice) if a schema with the same name
        already exists.  <replaceable class="parameter">schema_element</replaceable>
        subcommands cannot be included when this option is used.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
 </refsect1>

 <refsect1>
  <title>Notes</title>

  <para>
   To create a schema, the invoking user must have the
   <literal>CREATE</literal> privilege for the current database.
   (Of course, superusers bypass this check.)
  </para>

  <para>
   The <replaceable class="parameter">schema_element</replaceable>
   subcommands, if any, are executed in the order they are written.
   An exception is that foreign key constraint clauses in <command>CREATE
   TABLE</command> subcommands are postponed and added at the end.
   This allows circular foreign key references, which are sometimes useful.
  </para>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   Create a schema:
<programlisting>
CREATE SCHEMA myschema;
</programlisting>
  </para>

  <para>
   Create a schema for user <literal>joe</literal>; the schema will also be
   named <literal>joe</literal>:
<programlisting>
CREATE SCHEMA AUTHORIZATION joe;
</programlisting>
  </para>

  <para>
   Create a schema named <literal>test</literal> that will be owned by user
   <literal>joe</literal>, unless there already is a schema named <literal>test</literal>.
   (It does not matter whether <literal>joe</literal> owns the pre-existing schema.)
<programlisting>
CREATE SCHEMA IF NOT EXISTS test 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</literal> clause
   in <command>CREATE SCHEMA</command>, as well as more subcommand
   types than are presently accepted by
   <productname>PostgreSQL</productname>.
  </para>

  <para>
   Some other SQL implementations attempt to allow more kinds of forward
   references to objects defined in
   later <replaceable class="parameter">schema_element</replaceable>
   subcommands than just foreign key constraints.  This is difficult or
   impossible to do correctly in general, and it is not clear that the SQL
   standard requires any such behavior except for foreign keys.
  </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</literal> privilege on their schema to someone else, or a
   superuser chooses to create objects in it.
  </para>

  <para>
   The <literal>IF NOT EXISTS</literal> option is a
   <productname>PostgreSQL</productname> extension.
  </para>
 </refsect1>

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-alterschema"/></member>
   <member><xref linkend="sql-dropschema"/></member>
 </simplelist>
 </refsect1>

</refentry>