CREATE SCHEMA

The CREATE SCHEMA command creates a new schema in an existing database. A schema is a virtual space for storing tables. The default schema in SQream is public.

Tip

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

For more information, see the following:

Overview

The CREATE SCHEMA page describes the following:

Syntax

The following is the correct syntax for CREATE_SCHEMA:

create_schema_statement ::=
    CREATE SCHEMA schema_name
    ;

schema_name ::= identifier

Parameters

The following table shows the CREATE_SCHEMA parameters:

Parameter

Description

schema_name

The name of the schema to create.

Examples

This section includes the following examples:

Creating a Schema

The following example shows how to create 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

The following example shows how to alter 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

Permissions

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