PostgreSQL Schemas

A schema is a named folder inside a database. It holds tables, views, functions, and other objects. A database without schemas would be like a computer with every file dumped on the desktop. Schemas bring order.

  Database: school
     |
     +-- Schema: public
     |       +-- students
     |       +-- teachers
     |
     +-- Schema: library
     |       +-- books
     |       +-- members
     |
     +-- Schema: canteen
             +-- menu
             +-- orders

Every new database starts with one schema named public. Tables you create without a schema name land there.

Why Schemas Matter

  • Two tables can share the same name when they live in different schemas.
  • Teams can own separate areas of one database without stepping on each other.
  • Permissions can be granted to a whole schema in one command.
  • Extensions and add-ons can stay apart from your own tables.

Picture an apartment building. Each floor is a schema. Two floors can both have a room numbered 101. The floor number tells you which room you mean.

Creating Schemas

Schema creation takes one short command, and tables join a schema through a qualified name.

Creating a Schema

CREATE SCHEMA library;

To create a schema only when it does not exist yet, add a safety check.

CREATE SCHEMA IF NOT EXISTS library;

Creating a Table Inside a Schema

Write the schema name, a dot, and the table name. This style is called a qualified name.

CREATE TABLE library.books (
  book_id SERIAL PRIMARY KEY,
  title   TEXT NOT NULL,
  author  TEXT
);

INSERT INTO library.books (title, author)
VALUES ('River Stories', 'Maya Rao');

SELECT * FROM library.books;

A table in the canteen schema can carry the name menu without clashing with any menu table in another schema.

Managing Schemas

Search paths, listings, renames, and drops keep schemas tidy.

The Search Path

You do not have to type the schema name every time. PostgreSQL keeps a list of schemas called the search path. When you write a table name alone, the server checks each schema in the list from left to right and uses the first match.

  Query:  SELECT * FROM books;

  Search path: library, public
                  |
                  +--> found in library --> use library.books

View the current setting with this command.

SHOW search_path;

Change it for your current session like this.

SET search_path TO library, public;

Put the schema you use most at the front of the list.

Listing Schemas

In psql, run \dn. In plain SQL, query the catalog.

SELECT schema_name
FROM information_schema.schemata;

Moving and Renaming

Rename a schema when its purpose changes.

ALTER SCHEMA library RENAME TO school_library;

Move a table into another schema with this command.

ALTER TABLE public.books SET SCHEMA school_library;

Dropping a Schema

A plain DROP works only on an empty schema. The CASCADE option removes the schema and every object inside it. Use it with great care, because the action cannot be undone.

DROP SCHEMA library;
DROP SCHEMA library CASCADE;

Schemas and Permissions

Schemas give you a clean way to control access. A user needs USAGE permission on a schema before touching anything inside it.

GRANT USAGE ON SCHEMA library TO reader_role;
GRANT SELECT ON ALL TABLES IN SCHEMA library TO reader_role;

The reader_role can now look at every table in the library schema and nothing else.

Real-World Patterns

Teams organize schemas in a few popular ways. Pick the pattern that matches how your data is owned.

One Schema Per Department

Finance, sales, and support each get a schema. Each team manages its own tables.

One Schema Per Customer

A software product that serves many clients can create one schema per client. Each client sees only its own data.

A Schema for Extensions

Keep add-on functions in a separate schema so your own table list stays short and readable.

Practice Exercise

Create schemas named shop and warehouse. Add a table called items to each. Insert one row in both tables. Query both with qualified names. Set the search path to shop and query items without a prefix. Check which table answers.

Previous lesson
Back to course
Next lesson