CREATE SCHEMA

This page documents the preview version (v2.21). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

Synopsis

Use the CREATE SCHEMA statement to create a 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 objects in other schemas. Named objects in a schema can be accessed by using the schema name as prefix or by setting the schema name in the search path.

Syntax

create_schema_name ::= CREATE SCHEMA [ IF NOT EXISTS ] schema_name 
                       [ AUTHORIZATION role_specification ]

create_schema_role ::= CREATE SCHEMA [ IF NOT EXISTS ] AUTHORIZATION 
                       role_specification

role_specification ::= role_name | CURRENT_USER | SESSION_USER

create_schema_name

CREATESCHEMAIFNOTEXISTSschema_nameAUTHORIZATIONrole_specification

create_schema_role

CREATESCHEMAIFNOTEXISTSAUTHORIZATIONrole_specification

role_specification

role_nameCURRENT_USERSESSION_USER

Where

  • schema_name is the name of the schema being created. If no schema_name is specified, the role_name is used. Schema names must not begin with pg_. The attempt to create a schema with such a name, or to rename an existing schema to have such a name, causes an error.

  • role_name is the role 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 be a superuser.

Examples

  • Create a schema.
yugabyte=# CREATE SCHEMA IF NOT EXISTS branch;
  • Create a schema for a user.
yugabyte=# CREATE ROLE John;
yugabyte=# CREATE SCHEMA AUTHORIZATION john;
  • Create a schema that will be owned by another role.
yugabyte=# CREATE SCHEMA branch AUTHORIZATION john;

See also