Movatterモバイル変換

CREATE SCHEMA

CREATE SCHEMA — define a new schema

Synopsis

CREATE SCHEMAschema_name [ AUTHORIZATIONrole_specification ] [schema_element [ ... ] ]CREATE SCHEMA AUTHORIZATIONrole_specification [schema_element [ ... ] ]CREATE SCHEMA IF NOT EXISTSschema_name [ AUTHORIZATIONrole_specification ]CREATE SCHEMA IF NOT EXISTS AUTHORIZATIONrole_specificationwhererole_specification can be:user_name  | CURRENT_ROLE  | CURRENT_USER  | SESSION_USER

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). ACREATE 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 functioncurrent_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 theAUTHORIZATION clause is used, all the created objects will be owned by that user.

Parameters

Notes

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

Examples

Create a schema:

CREATE SCHEMA myschema;

Create a schema for userjoe; the schema will also be namedjoe:

CREATE SCHEMA AUTHORIZATION joe;

Create a schema namedtest that will be owned by userjoe, unless there already is a schema namedtest. (It does not matter whetherjoe owns the pre-existing schema.)

CREATE SCHEMA IF NOT EXISTS test 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 aDEFAULT CHARACTER SET clause inCREATE SCHEMA, as well as more subcommand types than are presently accepted byPostgreSQL.

The SQL standard specifies that the subcommands inCREATE SCHEMA can appear in any order. The presentPostgreSQL 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.

According to the SQL standard, the owner of a schema always owns all objects within it.PostgreSQL allows schemas to contain objects owned by users other than the schema owner. This can happen only if the schema owner grants theCREATE privilege on their schema to someone else, or a superuser chooses to create objects in it.

TheIF NOT EXISTS option is aPostgreSQL extension.

See Also