diff options
Diffstat (limited to 'doc/src/sgml/ref/create_schema.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_schema.sgml | 228 |
1 files changed, 228 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml new file mode 100644 index 0000000..3c2dddb --- /dev/null +++ b/doc/src/sgml/ref/create_schema.sgml @@ -0,0 +1,228 @@ +<!-- +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 a direct or indirect member of + that role, or be a superuser. + </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>, <command>CREATE VIEW</command>, <command>CREATE + INDEX</command>, <command>CREATE SEQUENCE</command>, <command>CREATE + TRIGGER</command> and <command>GRANT</command> are accepted as clauses + within <command>CREATE SCHEMA</command>. Other kinds of objects may + be created 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> + </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> + The SQL standard specifies that the subcommands in <command>CREATE + SCHEMA</command> can appear in any order. The present + <productname>PostgreSQL</productname> implementation does not + handle all cases of forward references in subcommands; it might + 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</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> |