PostgreSQL ER Diagram Guide

PostgreSQL is the most diagrammed database, partly because its dumps are the ones teams actually keep in git. Here is what to know when turning a PostgreSQL schema into an ER diagram.

Types you will meet in the boxes

• SERIAL / BIGSERIAL — auto-increment integers, the default PK choice; drawn with PK + AUTO badges.
• GENERATED ALWAYS AS IDENTITY — the modern equivalent of SERIAL; same meaning.
• TIMESTAMPTZ vs TIMESTAMP — with or without time zone; a diagram that preserves the distinction saves real bugs.
• UUID, JSONB, ARRAY — first-class types PostgreSQL diagrams should not flatten to 'string'.

Two more appear constantly: NUMERIC(precision, scale) for money — never float — and ENUM types from CREATE TYPE. An enum column should show its type name; the definition is catalog metadata, not a table, so it gets no box.

Foreign keys: two syntaxes, one meaning

-- inline
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

-- pg_dump style
ALTER TABLE ONLY public.posts
  ADD CONSTRAINT posts_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;

The second form is what pg_dump emits, with schema-qualified names (public.posts) and named constraints. Any tool that only reads the inline form will silently produce a diagram with no edges for real-world dumps — always test with an actual dump file, not a hand-written sample.

What a pg_dump actually looks like

A plain pg_dump interleaves DDL with bookkeeping: SET statements, SQL comments, a -- Name: header per object. Tables appear in dependency order, but the constraints do not — pg_dump collects every ALTER TABLE ... ADD CONSTRAINT statement, foreign keys included, and emits them at the end, after all CREATE TABLEs.

SET statement_timeout = 0;
SET search_path = public, pg_catalog;

CREATE TABLE public.users (
  id integer NOT NULL,
  email character varying(255) NOT NULL
);

CREATE SEQUENCE public.users_id_seq
  AS integer START WITH 1 INCREMENT BY 1;
ALTER SEQUENCE public.users_id_seq OWNED BY public.users.id;

ALTER TABLE ONLY public.users
  ADD CONSTRAINT users_pkey PRIMARY KEY (id);

-- ... every other table ...

ALTER TABLE ONLY public.posts
  ADD CONSTRAINT posts_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES public.users(id);

CREATE INDEX idx_posts_user_id ON public.posts USING btree (user_id);

Two consequences for diagramming. A parser must resolve FKs declared at the very end of the file. And much of the file is noise — sequences, btree indexes, ownership clauses, SET lines — that matters to the database but carries nothing the diagram needs. A tool that treats every statement as significant will choke on perfectly valid dumps.

Schema-qualified names and search_path

pg_dump qualifies every name with its schema: public.users, billing.invoices. Unqualified DDL relies on search_path, which is why a script that works in psql can fail under a different search_path. A diagram tool should strip the schema when everything lives in public and preserve it otherwise.

CREATE SCHEMA billing;

CREATE TABLE billing.invoices (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id INTEGER NOT NULL
);

-- FK crosses schemas: invoices -> public.customers
ALTER TABLE ONLY billing.invoices
  ADD CONSTRAINT invoices_customer_id_fkey
  FOREIGN KEY (customer_id)
  REFERENCES public.customers(id);

Cross-schema foreign keys are common in modular schemas — one schema per domain, sharing a core public.users. If the diagram flattens schemas away, two tables both named settings collapse into one box and the edges attach to the wrong thing. Group tables by schema, or at least label them.

Identity columns vs SERIAL

SERIAL is syntactic sugar: an integer column plus an owned sequence. The SQL-standard replacement (PostgreSQL 10+) states intent directly. Dumps reflect the difference: an identity column appears as ALTER TABLE ... ADD GENERATED ALWAYS AS IDENTITY, while SERIAL appears as a separate CREATE SEQUENCE plus a DEFAULT nextval(...) expression.

-- written as identity
CREATE TABLE public.invoices (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  total NUMERIC(12,2) NOT NULL
);

-- what pg_dump emits for a SERIAL column instead
CREATE SEQUENCE public.orders_id_seq;
ALTER TABLE ONLY public.orders
  ALTER COLUMN id SET DEFAULT nextval('public.orders_id_seq'::regclass);
ALTER TABLE ONLY public.orders
  ADD CONSTRAINT orders_pkey PRIMARY KEY (id);

A diagram tool has to recognize both spellings as 'auto-generated primary key'. GENERATED ALWAYS also changes write behavior: inserting an explicit id fails without OVERRIDING SYSTEM VALUE.

Dump quirks that trip up parsers

• Enums: CREATE TYPE order_status AS ENUM (...) defines a type, not a table; columns typed order_status keep the name, the type gets no box.
• Partial and expression indexes (WHERE archived_at IS NULL, ON lower(email)) are real DDL but say nothing about relationships; they are usually omitted from diagrams.
• Partitioned tables: the parent declared with PARTITION BY RANGE carries the FKs; CREATE TABLE ... PARTITION OF children are storage detail.
• Views and materialized views reference tables but have no primary keys; some tools draw them as dashed boxes, many skip them.
• Composite and deferrable FKs: FOREIGN KEY (a, b) REFERENCES t(x, y) is one edge with two column pairs; DEFERRABLE changes when it fires, not what it connects.
• Dollar-quoted function bodies: $$ ... $$ strings inside CREATE FUNCTION can contain semicolons and FK-looking phrases; a line-based parser will invent tables from them.

Generating a dump for diagramming

You rarely want the full pg_dump output. The schema-only, no-owner, no-privileges form is the smallest faithful representation; --no-comments trims noise:

pg_dump --schema-only \
        --no-owner \
        --no-privileges \
        --no-comments \
        -d mydb > schema.sql

For one schema of a multi-schema database, add -n billing. Extensions that install their own tables (PostGIS) will show up, so dump into a scratch database or filter them. pg_dump from a newer major version than the server refuses to run — a common reason a colleague's dump will not reproduce your diagram.

Common mistakes

• Testing a parser only with hand-written inline DDL. Real dumps put constraints at the end, qualify every name, and interleave noise; textbook-SQL-only validation produces an edge-free diagram on the first real file.
• Assuming search_path. An unqualified REFERENCES users(id) means whatever search_path says; in a dump names are always qualified, and the difference matters when schemas overlap.
• Treating every CREATE TABLE in a dump as application schema. Extension tables (postgis_geometry_columns, cron.job), audit tables, and migration bookkeeping will dominate a large diagram unless you exclude them.
• Reading ON DELETE SET NULL as nullable-by-design on columns declared NOT NULL — a contradiction PostgreSQL itself would refuse to create.
• Assuming statement order in hand-merged scripts. Constraint statements can legally precede table creation if re-ordered by hand; resolution should not depend on it.

Best practices

• Keep the schema dump in git and regenerate the diagram from it in review — the dump is the source of truth.
• Prefer GENERATED ... AS IDENTITY in new schemas: it is standard, dumps cleanly, and removes the sequence-default indirection.
• Name your foreign key constraints (posts_user_id_fkey). Named constraints appear in error messages and dumps, which makes both easier to read.
• Group domains into schemas when a database serves several services, and require schema-qualified references in code — the diagram then becomes a module map for free.
• Filter extension and infrastructure tables out of the dump first, or the first page of your diagram will be PostGIS internals.

Frequently asked questions

Why does my diagram have no relationships when my CREATE TABLE script clearly has them? Almost always the pg_dump form: the foreign keys live in ALTER TABLE statements at the end of the file, schema-qualified. ERDGenerator parses both forms — paste the raw dump as-is.

Should ENUM types appear as tables in the diagram? No. An enum is catalog metadata; it belongs on the column as a type name. If the values need to be referenced by foreign key, that is a lookup table, not an enum.

How do I diagram a database with 300 tables? Dump only the schemas under discussion (-n sales), or start from one FK-connected cluster and grow outward. A full diagram at that size is accurate and unreadable; a per-domain diagram is usable in review.

Do indexes belong on an ER diagram? Only the ones that express structure, like unique indexes backing a constraint. Ordinary btree indexes are physical tuning, not logical structure; the dump keeps them either way, so nothing is lost.

Try it

Paste your CREATE TABLE script — or a full pg_dump — into ERDGenerator's SQL tab with the PostgreSQL dialect. Every table, key, default and referential action carries over to the diagram, and you can export the result as PNG, SVG or clean DDL for another database.