How to use Post­gr­eSQL schemas

A schema in Post­gr­eSQL lets you group multiple tables together, allowing for better or­gan­isa­tion and easier man­age­ment 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 Post­gr­eSQL schema?

In Post­gr­eSQL, a schema is a col­lec­tion of tables. It can also include indexes, sequences, data types and functions, making it similar to a directory. However, in contrast to dir­ect­or­ies, schemas cannot be nested. This method of or­gan­ising database objects is es­pe­cially useful for managing large databases that have lots of tables.

Schemas exist between the database level and in­di­vidu­al table. Here’s an overview of the full hier­arch­ic­al structure of the popular database man­age­ment system:

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

To create a new schema in Post­gr­eSQL, 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 Post­gr­eSQL schemas?

The syntax for creating a schema in Post­gr­eSQL is as follows:

CREATE SCHEMA name;

This command instructs Post­gr­eSQL 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 Post­gr­eSQL schema

When you create new tables using the Post­gr­eSQL 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 demon­strate 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 Post­gr­eSQL INSERT INTO command.

How to delete a schema with DROP SCHEMA

You can also remove a Post­gr­eSQL 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 per­man­ently removes the schema and all its data, so be sure to use it carefully.