Skip to main content

CREATE SCHEMA

Creates a schema.

Syntax​

CREATE SCHEMA <schema_name> [AUTHORIZATION <role_spec>] 
   [<schema_element> [ ... ]]

CREATE SCHEMA AUTHORIZATION <role_spec> [<schema_element> [ ... ]]

CREATE SCHEMA IF NOT EXISTS <schema_name> [ AUTHORIZATION <role_spec> ]

CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <role_spec>

where <role_spec> can be:

    <user_name>
  | CURRENT_USER
  | SESSION_USER

Description​

CREATE SCHEMA defines a new schema in the current database. Therefore, the schema name specified in a CREATE SCHEMA must be distinct from any existing schemas in the current database.

In essence, a schema is a namespace. It contains named objects, such as tables, functions, and views. Objects in different schemas can have the same name. When you access a named object, you can either prefix the object name with the schema name or include the desired schema in your search path.

When you run a CREATE command to create a named object without specifying a schema, the named object is created in the current schema. To check the current schema, you can call function current_schema().

You can include subcommands in CREATE SCHEMA to create objects within the new schema. These subcommands are treated as separate commands that are issued after the schema is created. However, if the CREATE SCHEMA also includes an AUTHORIZATION clause, the objects created by these subcommands will be owned by the specified role.

Parameters​

  • <schema_name>

    The name of the schema. If omitted, the <user_name> is used as the schema name.

    Schema names cannot start with pg_, because such names are reserved for system catalog schemas.

  • <role_spec>

    The owner of the new schema. If omitted, the DW user that runs the command will be the owner. Supported values include:

    • <user_name>: specifies the role name of the DW user that owns the new schema. To create a schema owned by another role, you must have direct or indirect membership of that role.
    • CURRENT_USER: specifies that the current user is the schema owner.
    • SESSION_USER: specifies that the current session user is the schema owner.

  • <schema_element>

    The SQL subcommand used to create a named object in the new schema. Currently, only CREATE TABLE, CREATE VIEW, and GRANT are supported.

  • IF NOT EXISTS

    If you include IF NOT EXISTS in your command, no error will be reported when an existing schema with the same name exists. A notice will be issued instead.

Usage notes​

To create a schema, the invoking user must have the CREATE privilege for the current database.

Examples​

Create a schema:

CREATE SCHEMA my_schema;

Create a schema for role [email protected]; the schema will also be named [email protected]:

CREATE SCHEMA AUTHORIZATION "[email protected]";

Create a schema named test that will be owned by user [email protected], unless there already is a schema named [email protected]. (It does not matter whether [email protected] owns the pre-existing schema.)

CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION "[email protected]";

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

CREATE SCHEMA music
    CREATE TABLE myplaylist (singer text, style text, awards text[])
    CREATE VIEW awards_winners AS
        SELECT singer, style FROM myplaylist 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 music
    CREATE TABLE music.myplaylist (singer text, style text, awards text[])
    CREATE VIEW awards_winners AS
        SELECT singer, style FROM music.myplaylist WHERE awards IS NOT NULL;

SQL standard compatibility​

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

The SQL standard specifies that the subcommands in CREATE SCHEMA may appear in any order. The present Relyt implementation does not handle all cases of forward references in subcommands; it may 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. Relyt allows schemas to contain objects owned by users other than the schema owner. This can happen only if the schema owner grants the CREATE privilege on the schema to someone else.

The IF NOT EXISTS option is a Relyt extension.

The SQL standard includes a DEFAULT CHARACTER SET clause in the CREATE SCHEMA command, and allows more subcommand types.

As per the SQL standard, the subcommands in CREATE SCHEMA can be arranged in any order. However, the current Relyt implementation does not support all cases of forward references in subcommands. In some cases, you may need to reorder these subcommands to avoid such forward references.

The owner of a schema is the owner of all objects within it in the SQL standard. However, Relyt permits schemas to include objects owned by users other than the schema owner. This can occur only if the schema owner grants the CREATE privilege on the schema to another DW user.

The IF NOT EXISTS option is a Relyt extension.