Schemas, tables, and types

Create the schemas (migration)

With CREATE SCHEMA we can pick a name other than the default public for a schema. Most of the time, a service's migrations only need one schema, but as this example shows, it's possible to create more than one. Then we can prefix table names with the schema name, like schema_name.table_name, to say which schema we mean.

CREATE SCHEMA IF NOT EXISTS library;
CREATE SCHEMA IF NOT EXISTS lending;

CREATE TABLE IF NOT EXISTS library.authors (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  country TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS library.books (
  id SERIAL PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES library.authors (id),
  title TEXT NOT NULL,
  published_year INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS lending.loans (
  id SERIAL PRIMARY KEY,
  book_id INTEGER NOT NULL REFERENCES library.books (id),
  borrower_name TEXT NOT NULL,
  borrowed_at TIMESTAMP NOT NULL,
  returned_at TIMESTAMP
);

IF NOT EXISTS makes this migration idempotent: running it again is a no-op instead of an error, so it's safe to apply it over and over.

List schemas (after migration)

The migration above was just applied twice in a row before this query ran, and nothing broke. Same query as before, now against a migrated database.

-- pg_% schemas (pg_catalog, pg_toast, ...) are Postgres' own internals, not
-- anything a migration created, so we filter them out to see only real schemas.
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY schema_name;

library and lending now show up alongside the two defaults.

Setting the default schema

Yes, you can change which schema unqualified names resolve to: SET search_path picks the search order. Here we point it at lending, so loans below means lending.loans without spelling it out.

SET search_path TO lending, public;

SELECT * FROM loans;

search_path defaults to "$user", public, which is why public is where unqualified names land until you change it. This only affects the current session's name lookup — lending.loans is still reachable however search_path is set.

A cross-schema join

Joining across schemas works exactly like joining tables in the same schema — a schema is a naming namespace, not a query boundary. This finds which book each loan is for.

SELECT b.title, l.borrower_name, l.borrowed_at, l.returned_at
FROM library.books b
JOIN lending.loans l ON l.book_id = b.id
ORDER BY l.borrowed_at;

This is easy because one migration owns both schemas here. If lending belonged to a separate service instead, this same join would tie your query to another team's internal tables.

List tables

information_schema.tables shows the tables visible in each schema.

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema IN ('library', 'lending')
  AND table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

Inspect table shape

information_schema.columns shows each column name, type, and nullability.

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'library'
  AND table_name = 'books'
ORDER BY ordinal_position;

Putting it together

You don't need to read the migration to know the shape of these tables. Joining information_schema.tables and information_schema.columns reconstructs every column, type, and nullability across both schemas, straight from what Postgres itself tracks.

Watch the column_default column below: it holds the literal default expression Postgres runs whenever a column is left out of an INSERT. For an id column it'll read something like nextval('lending.loans_id_seq'::regclass) — that's what SERIAL actually is. Declaring a column SERIAL silently creates a sequence (a sequence is just a relation) named table_column_seq by convention (here, lending.loans_id_seq) and points the column's default at nextval() on it. The ::regclass cast is there because nextval() takes a regclass argument — a reference to the sequence by object identity (its OID), not by a plain text name — so the default keeps working even if the sequence or its schema gets renamed; Postgres just prints it back as whatever name currently resolves to that OID.

One thing is still missing: information_schema.columns doesn't say which column is the primary key. Here are three ways to get that.

SELECT
  t.table_schema,
  t.table_name,
  c.column_name,
  c.data_type,
  c.is_nullable,
  c.column_default
FROM information_schema.tables t
JOIN information_schema.columns c
  ON c.table_schema = t.table_schema
 AND c.table_name = t.table_name
WHERE t.table_schema IN ('library', 'lending')
  AND t.table_type = 'BASE TABLE'
ORDER BY t.table_schema, t.table_name, c.ordinal_position;

Finding primary keys: information_schema

The portable, ANSI-SQL-standard way — works the same on any SQL database, not just Postgres.

SELECT tc.table_schema, tc.table_name, kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON kcu.constraint_name = tc.constraint_name
 AND kcu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'PRIMARY KEY'
  AND tc.table_schema IN ('library', 'lending')
ORDER BY tc.table_schema, tc.table_name;

Finding primary keys: pg_constraint

Postgres' own catalog, the one information_schema is built on top of. Not portable, but simpler and faster.

SELECT conrelid::regclass AS table_name, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE contype = 'p'
  AND connamespace IN ('library'::regnamespace, 'lending'::regnamespace)
ORDER BY conrelid::regclass::text;

Finding primary keys: pg_index

A primary key is always backed by a unique index, so you can find it there too — useful when you care about the index itself.

SELECT n.nspname AS table_schema, c.relname AS table_name, a.attname AS column_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(i.indkey)
WHERE i.indisprimary
  AND n.nspname IN ('library', 'lending')
ORDER BY table_schema, table_name;