PostgreSQL Schemas

Summary: In this tutorial, you’ll learn about PostgreSQL schemas and how to use them to group database objects.

Introduction to PostgreSQL schemas #

A schema is an object inside a database. A schema works like a folder that logically groups database objects like tables, views, indexes, sequences, and functions.

A database may have multiple schemas whereas each schema belongs to a specific database.

When you create a database, PostgreSQL automatically creates a schema called public. The public schema is the default schema of the database.

If you create any database object like a table without specifying a schema name, PostgreSQL will put that object into the public schema.

Creating schemas #

PostgreSQL allows you to create a new schema using the CREATE SCHEMA statement:

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

In this syntax, you specify the schema name after the CREATE SCHEMA keywords.

For example, the following statement creates a new schema called sales:

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

PostgreSQL lets you create database objects with the same name in different schemas.

For example, if you have a table called products in the public schema, you can create a new table with the same name in the sales schema:

CREATE TABLE sales.products(
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
    name VARCHAR(255) NOT NULL,
    price DEC(11,2) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To reference products in the public table, you use products:

SELECT * FROM products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Or

SELECT * FROM public.products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

However, when you reference the products table in the sales schema, you need to prefix the products table with the sales schema:

SELECT * FROM sales.products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Schema search path #

When referencing a table (or any other objects) in a query, you don’t have to specify the schema name explicitly.

The reason is that PostgreSQL uses the schema search path to determine the order of schemas to find the table.

To check the current search path, you use the SHOW search_path statement:

SHOW search_path;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

It usually returns the following by default:

 search_path
--------------
 "$user", public
(1 row)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This means that PostgreSQL first looks for the schema with the same name as the current user ("$user"). If it cannot find the object, it searches in the public schema.

To change the schema search path, you use the SET search_path statement:

SET search_path TO public, sales;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This statement instructs PostgreSQL to search the objects in the public schema first and then the sales schema.

Listing all schemas in a database #

To view all schemas in a database, you use the following query:

SELECT schema_name 
FROM information_schema.schemata;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 schema_name
--------------------
 public
 sales
 information_schema
 pg_catalog
 pg_toastCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The information_schema, pg_catalog, and pg_toast are system schemas that store metadata.

Granting privileges on a schema #

First, create a new role called elephant:

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

The role elephant cannot access the sales schema.

Second, grant all privileges on the sales schema to the role elephant:

GRANT ALL
ON SCHEMA sales
TO elephant;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Now the role elephant can access the sales schema, create and drop database objects within the sales schema.

Applications of PostgreSQL Schemas #

In application development, you can use schemas to organize, secure, and manage data effectively.

Here are some typical applications of the schemas:

• Multi-tenancy applications: In software as a service (SaaS) applications, you can create a schema for each tenant (client) within the same database to isolate their data.
• Organizing large databases: You can use schemas to separate a large database into multiple schemas like sales, finance, and marketing. These schemas help implement more fine-grained security.

Summary #

• Schemas are a logical group of database objects within a database.
• A database can have multiple schemas, while a schema belongs to a database.
• The schema search path determines the order in which schema PostgreSQL searches when executing SQL queries without specifying a schema name.
• Use the CREATE SCHEMA statement to create a new schema.
• Use the GRANT statement to grant privileges on a schema to a role.