This topic describes how to create a schema.

Syntax

CREATE SCHEMA AUTHORIZATION username schema_element [ ... ];

Parameters

ParameterDescription
usernameThe name of the user who will own the new schema.

The schema will be named the same as username.

Note
  • Only superusers may create schemas owned by users other than themselves.
  • In a PolarDB for PostgreSQL(Compatible with Oracle), the role and username must already exist, but the schema may not exist.
  • In Oracle, the user (equivalently, the schema) must exist.
schema_elementAn SQL statement defining an object to be created within the schema.

CREATE TABLE, CREATE VIEW, and GRANT are accepted as clauses within CREATE SCHEMA. Other kinds of objects may be created in separate statements after the schema is created.

Description

This variation of the CREATE SCHEMA statement 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.

A schema is essentially a namespace. It contains named objects such as tables and views 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 schemas. Unqualified objects are created in the current schema. The schema at the front of the search path can be determined with the CURRENT_SCHEMA function. The search path concept and the CURRENT_SCHEMA function are not compatible with Oracle databases.

CREATE SCHEMA includes sub-statements to create objects within the schema. The sub-statements are treated essentially the same as separate statements issued after creating the schema. All the created objects will be owned by the specified user.

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

Examples

CREATE SCHEMA AUTHORIZATION polardb
    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;