How to use PostgreSQL schemas

A schema in PostgreSQL lets you group multiple tables together, allowing for better organisation and easier management of databases. You can create a schema using the CREATE SCHEMA command. If you no longer need a schema, you can simply remove it with the DROP SCHEMA command.

What is a PostgreSQL schema?

In PostgreSQL, a schema is a collection of tables. It can also include indexes, sequences, data types and functions, making it similar to a directory. However, in contrast to directories, schemas cannot be nested. This method of organising database objects is especially useful for managing large databases that have lots of tables.

Schemas exist between the database level and individual table. Here’s an overview of the full hierarchical structure of the popular database management system:

1. Instance (often referred to as the server in PostgreSQL, contains multiple databases)
2. Database
3. Schema
4. Table
5. Row

To create a new schema in PostgreSQL, you can use the CREATE SCHEMA command. To delete a schema, use the DROP SCHEMA command. We’ll take a look at both commands in detail below.

What is the syntax for PostgreSQL schemas?

The syntax for creating a schema in PostgreSQL is as follows:

CREATE SCHEMA name;

This command instructs PostgreSQL to create a schema in the database. After the command itself, you need to assign a unique name to the schema.

How to assign a new table to a PostgreSQL schema

When you create new tables using the PostgreSQL CREATE TABLE command, you can assign them to a schema. The syntax for doing so looks like this:

CREATE TABLE your_schema.new_table (
…
);

To demonstrate how this works, let’s first create a schema named customers:

CREATE SCHEMA customers;

Next, create a new table called customer_list_uk and add it to the customers schema:

CREATE TABLE customers.customer_list_uk (
id INT PRIMARY KEY NOT NULL,
name VARCHAR(50) NOT NULL,
country VARCHAR(50),
address VARCHAR(255)
);

This creates an empty table in the customers schema. You can now populate this table using the PostgreSQL INSERT INTO command.

How to delete a schema with DROP SCHEMA

You can also remove a PostgreSQL schema if you no longer need it. If the schema is empty, you can use the following command:

DROP SCHEMA customers;

To delete a schema and all the objects in it, use CASCADE:

DROP SCHEMA customers CASCADE;

This command permanently removes the schema and all its data, so be sure to use it carefully.