CREATE SCHEMA
CREATE SCHEMA — Define a new schema
Synopsis
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification
where role_specification can be:
user_name
| 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). Objects can have the same name as objects existing in other schemas. They can be accessed by "qualifying" their names with the schema name as a prefix, or by including the required schema in the search path.
A CREATE command that specifies an unqualified object name creates the object in the current schema (the first schema in the search path, determined by the current_schema function).
CREATE SCHEMA can optionally include subcommands to create objects in the new schema. These subcommands are treated as essentially independent commands issued after the schema is created, except that if the AUTHORIZATION clause is used, all created objects will be owned by the specified user.
Parameters
schema_name
The name of the schema to be created. If omitted, user_name will be used as the schema name. The name cannot begin with pg_, as such names are reserved for system schemas.
user_name
The role name of the user who will own the new schema. If omitted, it 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 a superuser.
schema_element
SQL statements defining objects to be created in the schema. Currently, only CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, CREATE TRIGGER, and GRANT are accepted as subcommands in CREATE SCHEMA. Other types of objects can be created after the schema is created using separate commands.
IF NOT EXISTS
Does nothing (but issues a notice) if a schema with the same name already exists. This option cannot include schema_element subcommands.
Notes
To create a schema, the calling user must have CREATE privilege on 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 named test owned by user joe, unless a schema named test already exists (regardless of whether joe owns the existing schema):
CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;
# Create a schema and create a table and a 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;
# Note that the subcommands do not end with semicolons.
# Here is an equivalent way to achieve 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;