Home > APIs > YSQL > The SQL language > SQL statements >

CREATE SCHEMA

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.

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

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

schema_element ::= '<YSQL DDL statement>'

role_specification ::= role_name | CURRENT_USER | SESSION_USER

create_schema_name

create_schema_role

schema_element

role_specification

Where

schema_name is the name of the schema being created. If no schema_name is specified, the role_name is used.
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.
schema_element is a YSQL statement defining an object to be created within the schema. Currently, only CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, CREATE TRIGGER and GRANT are supported as clauses within CREATE SCHEMA. Other kinds of objects may be created in separate commands after the schema is created.

Examples

Create a schema.

yugabyte=# CREATE SCHEMA IF NOT EXIST 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;

Create a schema and an object within that schema.

yugabyte=# CREATE SCHEMA branch
               CREATE TABLE dept(
                   dept_id INT NOT NULL,
                   dept_name TEXT NOT NULL,
                   PRIMARY KEY (dept_id)
               );