PostgreSQL Schemas

PostgreSQL - Schemas

In PostgreSQL, a schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, and operators. You can think of schemas as directories or folders within a database, but unlike folders, they cannot be nested.

Schemas are a powerful feature for organizing and managing your database, especially in large-scale applications or multi-tenant environments.

Why Use Schemas?

• Organization: Group related objects into logical units, making the database structure cleaner and more manageable.
• Multi-tenancy: Allow multiple users or applications to use a single database without their objects colliding. Each tenant can have their own schema.
• Permissions Management: Apply different security privileges at the schema level, simplifying access control.
• Avoiding Conflicts: Isolate third-party extensions or applications in their own schemas to prevent name conflicts with your own objects.

Creating a Schema

The CREATE SCHEMA statement is used to create a new schema. You can also optionally specify an owner for the schema.

Syntax

CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION user_name ];

• IF NOT EXISTS: (Optional) Prevents an error if a schema with the same name already exists.
• schema_name: The name of the new schema.
• AUTHORIZATION user_name: (Optional) Assigns ownership of the schema to the specified user. If omitted, the user executing the command becomes the owner.

Example

Let’s create a schema named ecommerce for an online store application.

testdb=# CREATE SCHEMA ecommerce;
CREATE SCHEMA

The “CREATE SCHEMA” message confirms that the schema was created successfully. By default, every new database has a public schema. When you create objects without specifying a schema, they are created in the current schema, which is determined by the search_path.

Working with Schemas and the search_path

To create a table within our new schema, you must qualify the table name:

CREATE TABLE ecommerce.products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

To avoid typing ecommerce. every time, you can add the schema to your session’s search_path.

testdb=# SHOW search_path;
   search_path
-------------------
 "$user", public
(1 row)

testdb=# SET search_path TO ecommerce, public;
SET

testdb=# -- Now you can create tables without the schema prefix
testdb=# CREATE TABLE customers ( id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL );
CREATE TABLE

PostgreSQL will now look for tables in the ecommerce schema first, and then in the public schema.

Listing Schemas

You can list all schemas in the current database using the psql command \dn or by querying the information_schema.

Using psql

testdb=# \dn
      List of schemas
        Name        |  Owner
--------------------+----------
 ecommerce          | myuser
 public             | postgres
(2 rows)

Using SQL Query

SELECT schema_name FROM information_schema.schemata;

Dropping a Schema

The DROP SCHEMA command removes a schema from the database.

Syntax

DROP SCHEMA [ IF EXISTS ] schema_name [ CASCADE | RESTRICT ];

• IF EXISTS: (Optional) Prevents an error if the schema does not exist.
• RESTRICT: (Default) Refuses to drop the schema if it contains any objects. This is a safety measure.
• CASCADE: Drops the schema and all of its contained objects (tables, functions, etc.). Use this with extreme caution as it can lead to irreversible data loss.

Example

To drop the ecommerce schema and all tables within it:

-- This will fail because the schema is not empty
testdb=# DROP SCHEMA ecommerce;
ERROR:  cannot drop schema ecommerce because other objects depend on it
DETAIL:  table ecommerce.products depends on schema ecommerce

-- This will succeed, deleting the schema and all its contents
testdb=# DROP SCHEMA ecommerce CASCADE;
DROP SCHEMA