<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.38 2002/04/24 02:49:50 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.39 2002/04/25 21:47:07 tgl Exp $

PostgreSQL documentation

Complete list of usable sgml source files in this directory.

-->

<!entity createLanguage system "create_language.sgml">

<!entity createOperator system "create_operator.sgml">

<!entity createRule system "create_rule.sgml">

<!entity createSchema system "create_schema.sgml">

<!entity createSequence system "create_sequence.sgml">

<!entity createTable system "create_table.sgml">

<!entity createTableAs system "create_table_as.sgml">

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_schema.sgml,v 1.1 2002/04/25 21:47:07 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>

<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>

<refsect2 id="R2-SQL-CREATESCHEMA-1">

<title>

Inputs

</title>

<para>

<variablelist>

<varlistentry>

<term><replaceable class="parameter">schemaname</replaceable></term>

<listitem>

<para>

The name of a schema to be created. If this is omitted, the username

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>

</para>

</refsect2>

<refsect2 id="R2-SQL-CREATESCHEMA-2">

<title>

Outputs

</title>

<para>

<variablelist>

<varlistentry>

<term><computeroutput>

CREATE

</computeroutput></term>

<listitem>

<para>

Message returned if the command is successful.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><computeroutput>

ERROR: namespace "<replaceable class="parameter">schemaname</replaceable>" already exists

</computeroutput></term>

<listitem>

<para>

If the schema specified already exists.

</para>

</listitem>

</varlistentry>

</variablelist>

</para>

</refsect2>

</refsynopsisdiv>

<refsect1 id="R1-SQL-CREATESCHEMA-1">

<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, datatypes, 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).

</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>

<refsect2 id="R2-SQL-CREATESCHEMA-3">

<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>

<para>

Use <command>DROP SCHEMA</command> to remove a schema.

</para>

</refsect2>

</refsect1>

<refsect1 id="R1-SQL-CREATESCHEMA-2">

<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 id="R1-SQL-CREATESCHEMA-3">

<title>

Compatibility

</title>

<refsect2 id="R2-SQL-CREATESCHEMA-4">

<title>

SQL92

</title>

<para>

SQL92 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>

SQL92 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>

In SQL92, 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 <literal>CREATE</> rights on his schema to someone

else.

</para>

</refsect2>

</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:

-->

<!-- reference.sgml

$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.27 2002/04/24 02:49:50 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.28 2002/04/25 21:47:06 tgl Exp $

PostgreSQL Reference Manual

-->

&createLanguage;

&createOperator;

&createRule;

&createSchema;

&createSequence;

&createTable;

&createTableAs;