CREATE SCHEMA v14
CREATE SCHEMA -- define a new schema.
This variation of the
CREATE SCHEMA command creates a new schema owned by
username and populated with one or more objects. The creation of the schema and objects occur within a single transaction so either all objects are created or none of them including the schema. (If you're using an Oracle database, no new schema is created –
username, and therefore the schema, must pre-exist.)
A schema is essentially a namespace: it contains named objects (tables, views, etc.) whose names may 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). Unqualified objects are created in the current schema (the one at the front of the search path, which can be determined with the function
CURRENT_SCHEMA). (The search path concept and the
CURRENT_SCHEMA function are not compatible with Oracle databases.)
CREATE SCHEMA includes subcommands to create objects within the schema. The subcommands are treated essentially the same as separate commands issued after creating the schema. All the created objects will be owned by the specified user.
The name of the user who will own the new schema. The schema will be named the same as
username. Only superusers may 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 (equivalently, the schema) must exist.)
An SQL statement defining an object to be created within the schema.
CREATE VIEW, and
GRANT are accepted as clauses within
CREATE SCHEMA. Other kinds of objects may be created in separate commands after the schema is created.
To create a schema, the invoking user must have the
CREATE privilege for the current database. (Of course, superusers bypass this check.)
In EDB Postgres Advanced Server, there are other forms of the
CREATE SCHEMA command that are not compatible with Oracle databases.