CREATE SCHEMA¶

The CREATE SCHEMA command is used to establish a new schema within an existing database. A schema represents a logical space where tables are organized and stored.

By default, the primary schema in Blue is named public.

Schemas are instrumental for organizing and separating different use-cases within a database, for instance, segregating staging and production environments.

Syntax¶

CREATE SCHEMA ["<database_name>".]"<schema_name>"

Parameters¶

The following table shows the schema_name parameters:

Parameter	Description
`schema_name`	The name of the schema to create
`database_name`	The name of the database within to create the schema

Examples¶

Creating a Schema¶

CREATE SCHEMA staging;

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

SELECT *
FROM staging.users;

Querying Tables from Different Schemas Without Providing Alias¶

SELECT
  public.users.column1
FROM public.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

Permissions¶

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