CREATE SCHEMA¶

CREATE SCHEMA creates a new schema in an existing database. A schema is a virtual space for storing tables.

The default schema in SQream DB is public.

Tip

Use schemas to separate between use-cases, such as staging and production.

Permissions¶

The role must have the CREATE permission at the database level.

Syntax¶

create_schema_statement ::=
    CREATE SCHEMA schema_name
    ;

schema_name ::= identifier

Parameters¶

Parameter	Description
`schema_name`	The name of the schema to create.

Examples¶

Creating a schema¶

CREATE SCHEMA staging;

CREATE TABLE staging.users AS SELECT * FROM public.users;

SELECT * FROM staging.users;

Altering the default schema for a role¶

SELECT * FROM users; -- Refers to public.users

ALTER DEFAULT SCHEMA FOR bgilfoyle TO staging;

SELECT * FROM users; -- Now refers to staging.users, rather than public.users