CREATE SCHEMA v16

Suggest edits

Name

CREATE SCHEMA — Define a new schema.

Synopsis

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

Description

This variation of the CREATE SCHEMA command creates a schema owned by username and populated with one or more objects. Creating the schema and objects occurs in a single transaction, so either all objects are created or none of them are, including the schema. If you're using an Oracle database, no new schema is created. username, and therefore the schema, must exist before you use the command.

A schema is essentially a namespace. It contains named objects, such as tables and views, whose names might duplicate those of other objects existing in other schemas. You can access named objects either by qualifying their names with the schema name as a prefix or by setting a search path that includes the desired schemas. Unqualified objects are created in the current schema. The current scheme is the one at the front of the search path. Use the function CURRENT_SCHEMA to determine the value.

Note

The search path concept and the CURRENT_SCHEMA function aren't compatible with Oracle databases.

CREATE SCHEMA includes subcommands to create objects in the schema. The subcommands are treated the same as separate commands issued after creating the schema. All the created objects are owned by the specified user.

Parameters

username

The name of the user to own the new schema. The schema also gets this name. Only superusers can create schemas owned by users other than themselves. In EDB Postgres Advanced Server, the role username must already exist, but the schema must not exist. In Oracle, the user and schema must exist.

schema_element

A SQL statement defining an object to create in the schema. CREATE TABLE, CREATE VIEW, and GRANT are accepted as clauses in CREATE SCHEMA. You can create other kinds of objects in separate commands after you create the schema.

Notes

Nonsuperusers creating the schema must have the CREATE privilege for the current database.

In EDB Postgres Advanced Server, other forms of the CREATE SCHEMA command aren't compatible with Oracle databases.

Examples

CREATE SCHEMA AUTHORIZATION enterprisedb
    CREATE TABLE empjobs (ename VARCHAR2(10), job VARCHAR2(9))
    CREATE VIEW managers AS SELECT ename FROM empjobs WHERE job = 'MANAGER'
    GRANT SELECT ON managers TO PUBLIC;
← Prev

CREATE ROLE

↑ Up

Compatible SQL commands

Next →

CREATE SEQUENCE