PostgreSQL CREATE SCHEMA Statement

Summary: In this tutorial, you’ll learn how to create a new schema in a PostgreSQL database using the PostgreSQL CREATE SCHEMA statement.

In PostgreSQL, a schema is a logical namespace that groups database objects like tables, views, indexes, sequences, and functions. Schemas allow you to organize database objects efficiently by preventing name conflicts and improving security.

Basic PostgreSQL CREATE SCHEMA statement #

Here’s the basic syntax for creating a schema:

CREATE SCHEMA [IF NOT EXISTS] schema_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the name of the schema you want to create.
• Second, use the IF NOT EXISTS option to avoid an error when you attempt to create a schema that already exists.

The role that executes the CREATE SCHEMA statement must have the CREATE privilege for the database. Note that superusers bypass this check.

For example, the following statement creates a schema named finance:

CREATE SCHEMA finance;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The role that executes the statement is the owner of the schema.

Creating a schema with an owner #

To create a new schema with an owner, you can use the CREATE SCHEMA statement with the AUTHORIZATION clause:

CREATE SCHEMA schema_name
AUTHORIZATION role_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This statement creates a new schema and assigns the ownership to the role_name. The role will have full control over database objects within the schema. For example:

First, create a new role with the LOGIN privilege:

CREATE ROLE maria
WITH LOGIN PASSWORD 'securepassword';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You should replace the securepassword with your password.

Second, create a new schema called accounting and assign the role maria as the owner:

CREATE SCHEMA accounting
AUTHORIZATION maria;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Creating a schema along with the database objects #

To create a schema and its database objects at the same time, you use the following syntax:

CREATE SCHEMA schema_name
    CREATE TABLE table1 (...)
    CREATE VIEW view1 (...)
    CREATE INDEX index1(...)
    CREATE SEQUENCE seq1(...)
    CREATE TRIGGER trg1();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL treats the subcommands as separate commands after the schema is created.

For example, the following statement creates a new schema called productions, along with tables:

CREATE SCHEMA productions
CREATE TABLE products (
   id INT PRIMARY KEY,
   name VARCHAR NOT NULL
)
CREATE TABLE orders (
  id INT PRIMARY KEY,
  order_date DATE NOT NULL,
  product_id INT NOT NULL,
  qty INT NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Notice that there is no semicolon (;) after each subcommand.

The statement is equivalent to the following statements:

CREATE SCHEMA productions;

CREATE TABLE productions.products (
  id INT PRIMARY KEY,
  name VARCHAR NOT NULL
);

CREATE TABLE productions.orders (
  id INT PRIMARY KEY,
  order_date DATE NOT NULL,
  product_id INT NOT NULL,
  qty INT NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the CREATE SCHEMA statement to create a new schema.