How to use Post­greSQL schemas

A schema in Post­greSQL lets you group multiple tables together, allowing for better or­ga­ni­za­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­greSQL schema?

In Post­greSQL, 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 di­rec­to­ries, schemas cannot be nested. This method of or­ga­niz­ing database objects is es­pe­cial­ly useful for managing large databases that have lots of tables.

Schemas exist between the database level and in­di­vid­ual table. Here’s an overview of the full hi­er­ar­chi­cal structure of the popular database man­age­ment system:

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

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

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

CREATE SCHEMA name;

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

When you create new tables using the Post­greSQL 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_usa and add it to the customers schema:

CREATE TABLE customers.customer_list_usa (
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­greSQL INSERT INTO command.

How to delete a schema with DROP SCHEMA

You can also remove a Post­greSQL 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­ma­nent­ly removes the schema and all its data, so be sure to use it carefully.