CREATE SCHEMA(7)                                          SQL Commands                                          CREATE SCHEMA(7)



NAME
       CREATE SCHEMA - define a new schema


SYNOPSIS
       CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [ ... ] ]
       CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ]


DESCRIPTION
       CREATE  SCHEMA  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.

       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 ``qualifying'' their
       names with the schema name as a prefix, or by setting a search path that includes the desired schema(s). A CREATE 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 current_schema).

       Optionally, CREATE SCHEMA 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 AUTHORIZATION clause is
       used, all the created objects will be owned by that user.

PARAMETERS
       schemaname
              The name of a schema to be created. If this is omitted, the user name is used as the schema name. The name  cannot
              begin with pg_, as such names are reserved for system schemas.

       username
              The  name  of the user who will own the schema. If omitted, defaults to the user executing the command. Only supe-
              rusers can create schemas owned by users other than themselves.

       schema_element
              An SQL statement defining an object to be created within the schema. Currently, only CREATE  TABLE,  CREATE  VIEW,
              CREATE  INDEX, CREATE SEQUENCE, CREATE TRIGGER and GRANT are accepted as clauses within CREATE SCHEMA. Other kinds
              of objects may be created in separate commands after the schema is created.

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

EXAMPLES
       Create a schema:

       CREATE SCHEMA myschema;


       Create a schema for user joe; the schema will also be named joe:

       CREATE SCHEMA AUTHORIZATION joe;


       Create a schema and create a table and view within it:

       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;

       Notice that the individual subcommands do not end with semicolons.

       The following is an equivalent way of accomplishing the same result:

       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;


COMPATIBILITY
       The  SQL  standard  allows  a  DEFAULT  CHARACTER  SET clause in CREATE SCHEMA, as well as more subcommand types than are
       presently accepted by PostgreSQL.

       The SQL standard specifies that the subcommands in CREATE SCHEMA can appear in any order. The present  PostgreSQL  imple-
       mentation  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.

       According to the SQL standard, the owner of a schema always owns all objects within it. PostgreSQL allows schemas to con-
       tain objects owned by users other than the schema owner. This can happen only if the schema owner grants the CREATE priv-
       ilege on his schema to someone else.

SEE ALSO
       ALTER SCHEMA [alter_schema(7)], DROP SCHEMA [drop_schema(7)]



SQL - Language Statements                                  2011-09-22                                           CREATE SCHEMA(7)