A PostgreSQL install is two programs. The server (postgres) runs in the background, owns the data directory, and listens for connections on port 5432. A client connects to it, sends SQL, and shows the results. psql is the client that ships with PostgreSQL; your Python, Go, or Node program is a client too, through a driver library. This split is the big difference from SQLite: with SQLite your program opens a file, but with PostgreSQL it opens a connection, and many connections can be open at once.

Every connection needs four things: the host (which machine), the port, the role (which PostgreSQL user you are), and the database. psql fills in defaults for all four: the local machine, port 5432, a role with your operating-system login name, and a database with the same name as the role. That's why a bare psql works once you've created a role and database named after yourself in the Installation tab, and why you can override any of them with -h, -p, -U, and -d. The prompt shows which database you're in, followed by => for a normal role or =# for a superuser.

Server

The background program that owns the data and answers queries; clients connect to it.

Client

Any program that connects to the server and sends it SQL, such as psql.

Role

PostgreSQL's name for a user account; roles are separate from your computer's accounts.

You can type two kinds of input at the psql prompt. SQL statements such as SELECT and CREATE TABLE go to the server. They end with a semicolon, and they're the same SQL your programs will send. Meta-commands start with a backslash and are handled by psql itself; the server never sees them. They take up one line and need no semicolon. Behind the scenes, many of them run ordinary SQL against PostgreSQL's built-in system catalogs, the tables where the server records its own tables, columns, and roles.

The names follow a pattern: \d means describe, and the letter after it says what to describe. \dt lists tables, \dv views, \di indexes, \dn schemas, and \du roles; \d users describes one table in full. \l lists databases and \c connects to a different one. Two display toggles are worth learning early. \x switches to expanded display, printing each row as a list of column | value lines, which is far easier to read for wide rows. \timing prints how long each query took. \? lists every meta-command, and \h gives the syntax of any SQL statement, such as \h CREATE TABLE.

Meta-command

A backslash command handled by psql itself; it is not SQL and needs no semicolon.

System catalog

PostgreSQL's built-in tables that describe the database itself.

Expanded display

The \x mode that prints each row as a vertical list of column/value pairs.

A PostgreSQL server holds many databases, and each one is completely separate: a query in one can't see tables in another. It's normal to make one database per project. Inside a database, tables live in schemas, which are named folders; every database starts with one called public, which is where your tables go unless you say otherwise. Since PostgreSQL 15, ordinary roles may not create tables in the public schema of a database they don't own. Creating your own database, as you do here, makes you its owner and avoids that error.

A table's columns each have a data type, and PostgreSQL enforces it strictly: an integer column refuses 'abc', and a date column refuses '2026-02-30'. The everyday types are integer (and bigint for very large numbers), numeric(10,2) for exact decimals such as money, text for strings of any length, boolean, date, and timestamptz for a moment in time. GENERATED ALWAYS AS IDENTITY makes the server number the rows itself; it's the modern replacement for the older serial. Constraints add rules: PRIMARY KEY identifies each row, NOT NULL requires a value, UNIQUE forbids duplicates, CHECK enforces any condition you write, and DEFAULT fills in a value you leave out.

Database

A separate container of schemas and tables on the server; one per project is typical.

Schema

A named folder of tables inside a database; the default one is called public.

Identity column

A column whose values the server generates automatically: 1, 2, 3, and so on.

INSERT INTO names the table, lists the columns you're filling, and gives the values in the same order after VALUES. Columns you leave out get their DEFAULT, or NULL if they have none, and an identity column is numbered for you. One statement can insert many rows: list several parenthesised groups separated by commas. psql reports what happened with a command tag such as INSERT 0 3; the 3 is the number of rows, and the 0 is a leftover from older versions that you can ignore.

RETURNING is a PostgreSQL feature worth using from day one. It makes an INSERT, UPDATE, or DELETE return rows, just like a SELECT, so you get the generated id back without a second query. Two rules about quoting: text values go in single quotes, and an apostrophe inside one is written twice, as in 'O''Brien'. Double quotes are for the names of tables and columns. When a program inserts user input, it must pass values as parameters and never paste them into the SQL text, or a crafted value can rewrite the query. That attack is called SQL injection. When a row breaks a constraint, the server rejects it and names the constraint in the error.

Command tag

The short status psql prints after a statement, such as INSERT 0 3 or UPDATE 1.

RETURNING

A clause that makes INSERT, UPDATE, or DELETE return the rows it affected.

SQL injection

An attack where untrusted input is pasted into SQL text; prevented by using parameters.

SELECT is the statement you'll write most, and its answer is always a small table called a result set. A query is made of clauses, each with one job. SELECT lists the columns (* means all of them), FROM names the table, WHERE keeps only the rows where a condition is true, ORDER BY sorts (add DESC for descending), and LIMIT caps the number of rows. OFFSET skips rows first, which is how page 2 of a list is built. AS renames a column in the output; the new name is called an alias.

SQL is declarative: you describe the result you want and the server's query planner decides how to fetch it. Logically, the clauses run in a different order from how you write them: FROM, then WHERE, then SELECT, then ORDER BY, then LIMIT. That's why an alias defined in SELECT can be used in ORDER BY but not in WHERE. Rows have no guaranteed order unless you ask for one. Without ORDER BY, PostgreSQL returns rows in whatever order is cheapest, and that order can change after an update. count(*) counts rows; it's the first of the aggregate functions covered at the Intermediate level.

Result set

The table of rows a query returns.

Clause

One part of a statement with a single job, such as WHERE or ORDER BY.

Query planner

The part of the server that decides how to execute a query efficiently.

UPDATE names a table, sets one or more columns with SET, and changes every row that matches its WHERE clause. DELETE FROM removes every matching row. Neither asks for confirmation, and without a WHERE clause, both affect every row in the table. The command tag tells you how many rows were hit: UPDATE 1 is what you expected, and UPDATE 0 means your condition matched nothing, which is usually a typo. Adding RETURNING * shows you exactly which rows changed.

There are three ways to remove data, and they do different things. DELETE removes rows and can be filtered with WHERE. TRUNCATE empties a whole table at once; it's much faster on big tables and resets nothing unless you add RESTART IDENTITY. DROP TABLE removes the table itself: its structure, data, indexes, and all. The safe habit is to write the WHERE clause first, run it as a SELECT to see which rows it matches, and only then turn it into an UPDATE or DELETE. Transactions, in the Advanced section, give you an undo button on top of that.

WHERE clause

The condition that chooses which rows a statement reads, changes, or deletes.

TRUNCATE

Empties a table in one fast operation, without scanning its rows.

DROP TABLE

Removes a table completely: structure, data, and indexes.

A WHERE condition is a boolean expression: for each row it works out to true or false, and only rows where it's true are kept. AND needs both sides true and OR needs either. AND binds tighter than OR, the way multiplication binds tighter than addition, so a OR b AND c means a OR (b AND c). Use parentheses whenever you mix them. BETWEEN x AND y is an inclusive range, and IN (...) matches any value in a list, which is tidier than a chain of ORs.

For text patterns, % matches any run of characters and _ matches exactly one. Here PostgreSQL differs from SQLite and MySQL: LIKE is case-sensitive, so 'alice' LIKE 'A%' is false. Use ILIKE for case-insensitive matching. NULL means unknown, and any comparison with an unknown value is itself unknown, so email = NULL is never true, even for rows with no email. SQL calls this three-valued logic. Test for missing values with IS NULL and IS NOT NULL, and use IS DISTINCT FROM when you want a comparison that treats two NULLs as equal.

Boolean expression

A condition that is true, false, or unknown for each row.

ILIKE

PostgreSQL's case-insensitive version of LIKE.

Three-valued logic

SQL's true / false / unknown; any comparison with NULL is unknown.

Good table design stores each fact once. Rather than copying a customer's name onto every order, an orders table stores the customer's id in a user_id column. Declaring that column REFERENCES users makes it a foreign key: the server refuses any order whose user_id doesn't exist in users, and refuses to delete a user who still has orders. That guarantee is called referential integrity. ON DELETE CASCADE changes the second rule, so deleting a user deletes their orders too. The alternative, ON DELETE SET NULL, keeps the orders and blanks the link.

A join combines rows from two tables where a condition matches, usually a foreign key equal to a primary key. INNER JOIN (or just JOIN) returns only the pairs that match, so a user with no orders disappears from the result. LEFT JOIN keeps every row from the left-hand table and fills the right-hand columns with NULL where there's no match, which is how you find users who have never ordered. Short table aliases like u and o keep joins readable, and prefixing columns with them (u.name) removes any doubt about which table a column comes from.

Foreign key

A column whose values must exist as a key in another table.

Referential integrity

The guarantee that every foreign key points at a row that really exists.

LEFT JOIN

A join that keeps every left-hand row, with NULLs where the right side has no match.

Aggregate functions take a whole set of rows and return one value: count counts, sum adds, avg averages, and min and max find the extremes. They skip NULLs, which is why count(*) (rows) and count(email) (rows that have an email) can differ. round(value, 2) tidies an average to two decimal places. Run an aggregate on its own and you get a single summary row for the whole table.

GROUP BY splits the rows into groups that share a value, such as one group per order status, and runs the aggregates once per group. WHERE filters individual rows before grouping, and HAVING filters whole groups after the aggregates are calculated, which is why HAVING sum(amount) > 100 works and WHERE sum(amount) > 100 is an error. Every column in the SELECT list must either appear in GROUP BY or sit inside an aggregate, and PostgreSQL enforces this where SQLite lets it slide. PostgreSQL also has a tidy FILTER clause for counting only some rows inside an aggregate.

Aggregate function

A function that summarises many rows into one value, such as sum or count.

GROUP BY

Splits rows into groups that share a value, giving one result row per group.

HAVING

Filters groups after aggregation; WHERE filters rows before it.

Real schemas change over time, and ALTER TABLE makes those changes in place without losing data. ADD COLUMN adds a column; existing rows get the column's default, or NULL. RENAME COLUMN and RENAME TO change names. DROP COLUMN removes a column and its data. ALTER COLUMN ... TYPE converts a column to a new type, and when PostgreSQL can't convert automatically, a USING expression tells it how. SET DEFAULT, SET NOT NULL, and ADD CONSTRAINT tighten the rules after the fact, and the server checks the existing rows first: if any break the new rule, the change is refused.

Unlike many databases, PostgreSQL has transactional DDL. DDL means the statements that define structure, such as CREATE, ALTER, and DROP, and in PostgreSQL they can run inside a transaction and be rolled back like any UPDATE. That makes schema changes much safer: wrap a risky migration in BEGIN and check the result before you COMMIT. On large tables, some changes rewrite the whole table and lock it while they run, such as changing a column's type. Adding a nullable column, or one with a constant default, is instant.

ALTER TABLE

The statement that changes the structure of an existing table.

DDL

Data Definition Language: statements that define structure, such as CREATE and ALTER.

Transactional DDL

The ability to roll back structure changes, which PostgreSQL supports.

Functions transform values. For text: upper, lower, and length do what they say, || joins strings together, left(s, n) takes the first characters, and split_part cuts a string at a delimiter, which is handy for pulling the domain out of an email address. For numbers: round, ceil, floor, and abs. coalesce(a, b, ...) returns the first argument that isn't NULL, which is the standard way to show a fallback value. CASE WHEN ... THEN ... ELSE ... END is SQL's if/else: it picks a value row by row.

Dates and times are a PostgreSQL strength. current_date and now() give today and this moment. Subtracting two dates gives a number of days, and adding an interval such as interval '30 days' moves a date. age() gives the time between two dates in years, months, and days, extract(year FROM d) pulls out one part, and date_trunc('month', d) rounds down to the start of the month, which is the usual way to group sales by month. The :: operator casts a value to another type, for example '2025-01-31'::date or price::integer, and generate_series produces a run of numbers or dates, which is useful for test data.

Cast

Converting a value to another type, written value::type in PostgreSQL.

Interval

A length of time, such as interval '30 days', that can be added to dates.

coalesce

Returns its first argument that is not NULL.

Without an index, finding rows means a sequential scan: reading every row in the table and testing each one. That's fine for a few hundred rows and slow for ten million. An index is a separate, sorted structure (a B-tree by default) that maps column values to row locations, so the server can find matches the way you'd use the index at the back of a book. PostgreSQL creates one automatically for every PRIMARY KEY and UNIQUE constraint. You add others for columns you often search, join, or sort on. The price is disk space and slightly slower writes, because every insert and update must keep the index current.

EXPLAIN shows the query plan, the steps the planner chose, without running the query; EXPLAIN ANALYZE runs it and adds real timings. Read plans from the innermost line outward. Seq Scan means the whole table was read. Index Scan or Bitmap Index Scan means an index was used. The cost numbers are the planner's estimates in arbitrary units, and rows is how many rows it expects. The planner decides from statistics that ANALYZE gathers (autovacuum does this in the background), so on a tiny table it will often ignore your index, correctly, because reading a few pages is cheaper than using the index.

Sequential scan

Reading every row of a table to find the matches.

Index

A sorted lookup structure that lets the server find rows without reading them all.

Query plan

The steps the server will take to run a query, shown by EXPLAIN.

A view gives a name to a SELECT. Once CREATE VIEW customer_totals AS ... exists, you can query customer_totals like a table, filter it, and join it to other tables, but no data is copied: each time you select from it, PostgreSQL runs the underlying query again, so a view is always up to date. Views are useful for hiding complicated joins behind a simple name, and for giving a role access to some columns of a table without granting access to the whole table. CREATE OR REPLACE VIEW updates the definition.

A materialized view runs its query once and stores the result like a real table. Reading from it is as fast as reading a table, which makes it ideal for expensive reports, but it's a snapshot: it doesn't change when the underlying tables do. REFRESH MATERIALIZED VIEW runs the query again and replaces the stored rows. On a busy system, adding CONCURRENTLY lets people keep reading the old contents during the refresh; it requires a unique index on the materialized view. \dv lists views and \dm lists materialized views.

View

A named query that runs every time you select from it; it stores no data.

Materialized view

A query whose results are stored; it's fast to read but only as fresh as the last refresh.

REFRESH

Re-runs a materialized view's query and replaces its stored rows.

A subquery is a SELECT inside another statement. A scalar subquery returns one value and can go anywhere a value can, for example WHERE amount > (SELECT avg(amount) FROM orders). A subquery that returns a column can feed IN, and EXISTS (SELECT 1 ...) is true when the inner query returns any row at all. A correlated subquery refers to the outer row, as in users who have at least one order, and conceptually runs once per outer row, though the planner often rewrites it as a join.

A common table expression (CTE), written with WITH name AS (...), gives a subquery a name so you can build a query in readable steps and refer to each step more than once. Since PostgreSQL 12, simple CTEs are folded into the main query, so they cost nothing extra. WITH RECURSIVE lets a CTE refer to itself. It starts from a seed row and repeatedly adds new rows until none are produced, which is how you walk a tree such as an organisation chart or a category hierarchy, or generate a sequence.

Subquery

A query nested inside another statement.

Correlated subquery

A subquery that refers to columns of the outer query.

CTE

A named subquery written with WITH; WITH RECURSIVE can refer to itself.

A transaction makes several statements act as one. Moving money between two accounts is the classic example: take it out of one, put it into the other, and it must never be possible for only half of that to happen. BEGIN starts a transaction, and nothing you change is visible to anyone else until COMMIT. ROLLBACK throws all the changes away. Every statement you run outside BEGIN is its own small transaction that commits immediately; that's called autocommit. Transactions give four guarantees, known as ACID: atomic (all or nothing), consistent (constraints hold), isolated (other sessions don't see half-finished work), and durable (committed data survives a crash).

psql shows the transaction state in the prompt: => outside a transaction, =*> inside one, and =!> inside one that has hit an error. That last state surprises people. After any error, PostgreSQL refuses every further statement in the transaction until you ROLLBACK, so a script can't carry on as if nothing had happened. SAVEPOINT name marks a point inside a transaction, and ROLLBACK TO name undoes back to it without abandoning the whole transaction.

Transaction

A group of statements that commit together or not at all.

Autocommit

The default where each statement outside BEGIN is committed immediately.

ACID

Atomic, consistent, isolated, durable: the guarantees a transaction provides.

A role can log in, own objects, and hold privileges. Whoever creates a table owns it and can do anything with it; everyone else can do nothing until they're granted it. Creating roles needs the CREATEROLE attribute or a superuser, which is why the first command below runs as postgres. LOGIN lets a role connect, and PASSWORD sets its password. A role without LOGIN acts as a group: grant privileges to the group, then GRANT groupname TO person, and every member inherits them.

Privileges work at several levels, and a role needs all of them to read a table: CONNECT on the database, USAGE on the schema, and SELECT on the table itself. INSERT, UPDATE, and DELETE are granted separately, so a reporting role can read everything and change nothing. GRANT SELECT ON ALL TABLES IN SCHEMA public covers the tables that exist now, and ALTER DEFAULT PRIVILEGES covers tables created later. REVOKE takes privileges away. The principle of least privilege says each program should connect as a role with only the privileges it needs, never as a superuser.

Privilege

A permission such as SELECT or INSERT on a specific object.

Group role

A role without LOGIN used to bundle privileges for its members.

Least privilege

Giving each role only the access it actually needs.

An upsert inserts a row, or updates the existing one if it would break a unique constraint. PostgreSQL writes it as INSERT ... ON CONFLICT (column) DO UPDATE SET .... Inside the DO UPDATE, the special name EXCLUDED refers to the row you tried to insert, so SET age = EXCLUDED.age copies the new value over. ON CONFLICT DO NOTHING silently skips duplicates instead, which is handy when importing data that might already be there. The conflict target must match a real UNIQUE or PRIMARY KEY constraint.

Window functions compute a value for each row from a set of related rows, its window, without collapsing them the way GROUP BY does. The OVER (...) clause defines the window: PARTITION BY splits rows into groups and ORDER BY orders them within each group. row_number() numbers rows 1, 2, 3; rank() gives ties the same rank; sum(amount) OVER (ORDER BY ordered_at) is a running total; and lag() reads a value from the previous row, which is how you compute the change from one row to the next.

Upsert

Insert a row, or update the existing one if it conflicts: INSERT ... ON CONFLICT.

EXCLUDED

In ON CONFLICT DO UPDATE, the row that was proposed for insertion.

Window function

A function computed over related rows while keeping every row in the result.

Sometimes part of your data has no fixed shape: product attributes that differ from one product to the next, or settings that change every release. PostgreSQL's jsonb type stores a whole JSON document in one column. It's parsed and stored in a binary form, so it's validated on the way in (malformed JSON is refused) and fast to query. There's also a plain json type that keeps the exact text, but jsonb is the one to use.

The operators are short: -> gets a field as JSON, ->> gets it as text, and #>> follows a path. @> means contains: attrs @> '{"color": "black"}' finds every product whose attributes include that key and value. ? tests whether a key exists. jsonb_set returns a changed copy of a document, and || merges two. A GIN index on a jsonb column makes @> and ? searches fast even across millions of rows. A good rule of thumb: keep the fields you filter and join on in real columns, and use jsonb for the long tail of optional ones.

jsonb

A column type that stores validated JSON in a binary, queryable form.

Containment (@>)

Tests whether a JSON document contains the given keys and values.

GIN index

An index type suited to searching inside jsonb, arrays, and full text.

A logical backup is a file of SQL, or an archive, that can recreate a database. pg_dump shop > shop.sql writes plain SQL that you can read and replay with psql -f. pg_dump -Fc writes PostgreSQL's compressed custom format, which pg_restore can load selectively, for example one table, and in parallel. pg_dump takes a consistent snapshot while the database stays in use, so there's no need to stop anything. pg_dumpall also saves roles, which pg_dump doesn't. Test your backups by actually restoring them into a scratch database: a backup you've never restored is only a hope.

\copy is psql's bridge to spreadsheets. \copy users TO 'users.csv' CSV HEADER writes a CSV file on your machine, and \copy ... FROM reads one back into a table. There's also a server-side SQL command, COPY, which reads and writes files on the server's disk and needs special privileges. That difference trips people up, and \copy is almost always the one you want. Both report the number of rows moved, as in COPY 5. The column list is optional, and it lets you load a CSV whose columns are in a different order from the table's.

Logical backup

A dump of SQL or an archive that can recreate a database; made with pg_dump.

pg_restore

Loads a custom-format dump, optionally in parallel or one table at a time.

\copy

psql's command for moving table data to and from files on your own machine.

Builds on Basics & Commands › Relationships and JOINs

Basics declared user_id integer NOT NULL REFERENCES users (id) and joined on it. A declaration like that is a promise, and this lesson is about how PostgreSQL keeps it. The relational model (Codd, 1970) requires referential integrity: every foreign-key value must match a key in the parent table or be NULL. A row that breaks the rule is an orphan, and every join that touches it silently gives the wrong answer.

Unlike SQLite, PostgreSQL always enforces foreign keys; there is no setting to forget. The orphan insert below fails at once with the constraint’s name and the missing key. The parent column must be a PRIMARY KEY or have a UNIQUE constraint, and PostgreSQL checks that when the foreign key is created, not later. Internally each foreign key is a pair of system triggers, which is why the only ways around one are superuser-only: session_replication_role = replica, which logical replication uses, or disabling the table’s triggers.

Referential actions decide what happens to the children when a parent row is deleted or its key changes. NO ACTION, the default, refuses the change if children would be left behind. RESTRICT refuses too, but its check can never be deferred. CASCADE deletes or updates the children as well, and SET NULL / SET DEFAULT detach them. Choose by what the link means. Use CASCADE when the child can’t exist without its parent (an order line without its order), SET NULL when the link is optional (an employee whose manager leaves), and NO ACTION when deleting the parent would destroy history (a customer with orders). Below, deleting Marketing takes Bob with it, and deleting Alice clears Carol’s manager_id.

When the check runs. A foreign key is NOT DEFERRABLE unless you say otherwise. It is checked at the end of every statement, and SET CONSTRAINTS ALL DEFERRED has no effect on it, as Frank’s insert shows. A key declared DEFERRABLE can be deferred for one transaction, so rows may arrive in any order and the check runs at COMMIT. This is what self-references and cycles need. If a violation is still there at COMMIT, PostgreSQL rolls the whole transaction back: the prompt returns to lab=#, and Eve’s row is gone. SQLite behaves differently and leaves the failed transaction open. Django creates every foreign key on PostgreSQL as DEFERRABLE INITIALLY DEFERRED, so in a Django project these checks happen at COMMIT.

Referential integrity

Every foreign-key value matches a parent key or is NULL. A row that breaks it is an orphan.

Referential action

What a delete or key update on the parent does to the children: NO ACTION, RESTRICT, CASCADE, SET NULL or SET DEFAULT.

DEFERRABLE

A constraint that may be checked at COMMIT instead of after each statement. INITIALLY DEFERRED makes that the default; SET CONSTRAINTS switches it for one transaction.

SQLSTATE 23503

foreign_key_violation: the error code a driver sees when an insert, update or delete breaks a foreign key.

Builds on Basics & Commands › Indexes and EXPLAIN

PostgreSQL stores every table and every index as a file of fixed-size pages, 8,192 bytes each (SHOW block_size). This is where it differs most from SQLite. An SQLite table is a B-tree ordered by rowid. A PostgreSQL table is a heap: rows go wherever there is free space, in no particular order, and the primary key is just another index stored in its own file. Below, the 10,000-row table takes 83 pages, its primary-key index 30, and the email index 52.

Every row version has a physical address, its ctid: (page, line pointer). Rows 1 to 136 fill page 0, and row 137 starts page 1. pageinspect shows the layout inside a page. An array of line pointers grows from the front of the page, and the tuples they point at are packed from the back: the first one starts at byte 8,136 and is 52 bytes long. That is a 24-byte tuple header plus 28 bytes of data. An index entry stores a key and a ctid, so an index lookup is a B-tree descent followed by a read of the heap page the ctid names. A primary-key B-tree with 10,000 keys has a root (level 1) pointing at 28 leaf pages, so any key is two page reads away, plus one for the heap.

A row has to fit in a page, so large values go somewhere else. When a row would be bigger than about 2 kB, PostgreSQL first tries to compress its long values, then moves them out of line into the table’s TOAST table in chunks, leaving a pointer behind. pg_column_size shows the result. 128,000 bytes of MD5 hex digits barely compress, so all of them are stored out of line. 127,996 bytes of a repeated sentence compress to 1,499. TOAST is invisible to SQL; you see it only in sizes and speed. A query that doesn’t read a large column never has to fetch its chunks.

Heap

A table file whose rows are stored in no particular order. Every index on it, the primary key included, is a separate file.

ctid

A row version’s physical address, (page, line pointer). It changes when the row is updated, so it is not a stable ID.

Line pointer

A slot near the start of a page that records where a tuple begins and how long it is.

TOAST

The Oversized-Attribute Storage Technique: long values are compressed and/or moved to a side table in chunks.

Builds on Basics & Commands › Transactions

PostgreSQL never changes a row in place. An UPDATE writes a new version of the row and marks the old one as ended. Each version carries two transaction IDs: xmin, the transaction that created it, and xmax, the one that deleted or replaced it. Below, transaction 1058 creates both rows, and the UPDATE in transaction 1059 writes Alice’s new version at (0,3). The old version at (0,1) stays on the page with xmax = 1059 and a pointer to its successor. A query decides which version it can see by comparing these IDs with its snapshot. This is multiversion concurrency control (MVCC), and it is why readers never wait for writers.

Old versions are garbage once no snapshot can see them, and VACUUM reclaims them. After VACUUM, line pointer 1 has lp_flags = 2, a redirect. This update was a HOT (heap-only tuple) update: no indexed column changed and the new version fit on the same page, so the index still points at line pointer 1, and the redirect forwards it to 3. Autovacuum runs VACUUM in the background. It is not optional maintenance. Transaction IDs are 32-bit and wrap around, so VACUUM must also freeze old rows, and a database that never vacuums eventually stops accepting writes to protect itself.

Writers do wait for writers. In the second session, B reads Alice’s balance while A holds an uncommitted update, and isn’t blocked. When B tries to update the same row it waits, and lock_timeout cancels it after 2 seconds. Row locks are not kept in a lock table: a locked row has the locker’s transaction ID in its xmax, so a waiter waits on that whole transaction. That is why pg_stat_activity reports the wait as transactionid and pg_blocking_pids names A. When A commits, B’s update goes ahead. Under READ COMMITTED it re-reads the newest version of the row, so A’s change is not lost: 70 − 10 + 10 = 70.

A deadlock happens when each transaction holds a row the other needs. Neither can continue, so after deadlock_timeout (1 second by default) the waiting backend checks for a cycle in the wait graph and cancels its own transaction. Here that was A, which had been waiting longer, and its later COMMIT can only report ROLLBACK. Applications that update several rows avoid deadlocks by always locking them in the same order, for example by primary key.

MVCC

Multiversion concurrency control: every update writes a new row version, and each snapshot sees only the versions committed before it began.

xmin / xmax

The transaction that created a row version, and the one that deleted, replaced or locked it.

HOT update

A heap-only tuple update: no index changes because no indexed column changed and the new version fit on the same page.

Deadlock

Two or more transactions each waiting for a lock another holds. PostgreSQL breaks the cycle by cancelling one of them.

Builds on Basics & Commands › Transactions

The SQL standard defines four isolation levels by the anomalies each one allows. PostgreSQL accepts all four names but implements three: READ UNCOMMITTED behaves as READ COMMITTED, because MVCC never needs to show uncommitted data. The level decides when a transaction takes its snapshot.

READ COMMITTED, the default, takes a new snapshot for every statement. In the first session A reads 100, B commits a withdrawal, and A’s second read in the same transaction sees 70: a non-repeatable read. REPEATABLE READ takes one snapshot at the transaction’s first query and keeps it, so A keeps seeing 70 after B has committed 50. If A then tries to update that row, PostgreSQL refuses with could not serialize access due to concurrent update instead of overwriting B’s change. That is the lost-update protection SQLite gets from its single writer.

A single snapshot is still not enough. In the doctors example the rule is that at least one doctor stays on call. Each transaction checks the rule, sees two doctors on call, and takes a different doctor off. Neither one changes a row the other wrote, so REPEATABLE READ (snapshot isolation) lets both commit, and nobody is on call. This anomaly is called write skew. SERIALIZABLE in PostgreSQL uses Serializable Snapshot Isolation (Ports & Grittner, 2012). It records what each transaction read with predicate locks and watches for the dependency pattern that only a non-serial schedule can produce. B is cancelled at COMMIT and the rule holds.

SERIALIZABLE doesn’t make transactions wait; it cancels one of them. Every SERIALIZABLE (and REPEATABLE READ) application therefore needs a retry loop: catch SQLSTATE 40001 (serialization_failure) and run the whole transaction again, as the HINT says. In return you can write each transaction as if it ran alone, without reasoning about every possible interleaving.

Snapshot

The set of transactions whose effects a query can see: everything committed before the snapshot was taken.

Write skew

Two transactions read overlapping data, then update different rows, and together break a rule neither broke alone.

SSI

Serializable Snapshot Isolation: snapshot isolation plus tracking of read/write dependencies, cancelling a transaction when a cycle could form.

SQLSTATE 40001

serialization_failure: retry the whole transaction.

Builds on Basics & Commands › Indexes and EXPLAIN

SQL says what to return, not how to find it. The planner considers the possible plans and estimates each one’s cost in abstract units: reading a page in sequence costs 1 (seq_page_cost), reading one at random costs 4 (random_page_cost), plus small CPU costs per row. Every estimate starts with a guess of how many rows each step returns, and that guess comes from statistics that ANALYZE gathers from a sample of the table and stores in pg_stats.

Below, pg_stats records two distinct statuses with their frequencies, 99% and 1%. The same query shape gets two different plans. For returned the planner expects 2,053 rows (the true count is 2,000) and uses the index. For shipped it expects nearly every row, so reading the whole table in order is cheaper than 198,000 random lookups. EXPLAIN ANALYZE runs the query and prints actual counts beside the estimates. From PostgreSQL 18 it also reports buffer usage by default. The first run’s index-only scan still visits the heap 2,000 times (“Heap Fetches”), because nothing has yet marked those pages all-visible. After VACUUM sets the visibility map, Heap Fetches drops to 0 and the query runs about six times faster.

Most bad plans come from bad row estimates, and the classic cause is correlated columns. The planner assumes conditions are independent, so for city = 'city7' AND country = 'country1' it multiplies 1/50 by 1/10 and expects about 400 rows. Every city belongs to exactly one country, so the true answer is 4,000, ten times more. A ten-fold underestimate is enough to pick the wrong join method or a nested loop that never ends. CREATE STATISTICS ... (dependencies) tells ANALYZE to measure how far one column determines the other, and the estimate becomes 4,173.

The planner also chooses a join method for each join. A hash join builds a hash table from the smaller input and probes it with the larger one, which suits joining whole tables. A nested loop looks up matching rows for each outer row, which wins when the outer side is small and the inner side is indexed, as for one customer. A merge join walks two inputs that are already sorted. Reading plans means comparing estimated with actual rows, finding the first place they diverge, and asking why.

Cost

The planner’s estimate of work, in units of one sequential page read. It compares plans; it is not a time.

Selectivity

The fraction of rows a condition keeps. It decides between an index and a sequential scan.

Visibility map

One bit per heap page meaning “all rows here are visible to everyone”, set by VACUUM. Index-only scans depend on it.

Extended statistics

CREATE STATISTICS: dependencies, distinct counts or most-common values measured across several columns together.

Builds on Basics & Commands › Subqueries and CTEs

A recursive CTE computes a fixed point. The first part (the anchor) produces some rows. The second part runs again and again on the rows the previous round produced, and the query stops when a round produces nothing new. That is enough to walk any graph stored as rows: road networks, org charts, bills of materials, dependency trees.

Whether the walk ends depends on UNION versus UNION ALL. With UNION, a row already produced is discarded, so the reachability query finishes even though the network has a loop (A → B → C → A). To list paths, each row must carry its route, so every row is new and UNION can’t stop the loop. PostgreSQL 14 added the CYCLE clause for this. It records the values of the named column along each path and marks a row as a cycle when a value repeats. The anchor starts at A itself for a reason: CYCLE knows only the values it has seen in that column. If the anchor started from the routes leaving A, A would never appear as a destination, and A → B → C → A → E would slip through once. The cheapest of the three routes found is the shortest path, 16 km.

The companion SEARCH clause sets the order of the walk. SEARCH DEPTH FIRST BY name adds a sort column that keeps each subtree together, which is exactly what an indented org chart needs; BREADTH FIRST lists one level at a time. A recursion with no stopping rule never finishes, so protect anything recursive with a statement_timeout or a depth limit in the recursive part.

Fixed point

The state in which applying the recursive step adds nothing new. A recursive CTE stops there.

Anchor / recursive member

The two halves of a recursive CTE: the starting rows, and the query run repeatedly on the previous round’s output.

CYCLE clause

Marks rows whose path revisits a value, so UNION ALL recursions over graphs with loops can end.

SEARCH clause

Adds an ordering column for depth-first or breadth-first output.

Builds on Basics & Commands › Transactions

The write-ahead log (WAL) is the record of every change, written before the data pages it describes. Changed pages can stay in memory and reach the disk later, at a checkpoint, because the WAL alone is enough to redo the work. Below, inserting 10,000 small rows writes 1,461 kB of WAL, about 150 bytes a row, once the heap tuple, the index entry and the record headers are counted. pg_current_wal_lsn() is the current position in the log, and the difference between two positions is a byte count.

A COMMIT is durable once its WAL record is on stable storage, so by default every COMMIT waits for an fdatasync of the WAL file. Timing 1,000 one-row transactions measures that wait directly. On this server’s disk they took 442 ms, about 0.44 ms per commit, or roughly 2,300 commits a second from one connection. The SQLite lesson measured about 4.2 ms per commit in rollback-journal mode on the same disk, because that mode syncs both a journal and the database file. With synchronous_commit = off the same 1,000 commits take 7 ms. COMMIT returns as soon as the record is in memory, and the WAL writer flushes it shortly afterwards. A crash can then lose the last fraction of a second of commits, at most three times wal_writer_delay, but never corrupts anything, so it is a reasonable setting for data you could re-create. Putting all 1,000 rows in a single transaction takes 3.4 ms and needs only one flush.

Do not confuse that with fsync = off, which skips flushing altogether and can leave a corrupt database after a power cut. full_page_writes protects against a different failure, a torn page half-written when the power fails. The first time a page changes after each checkpoint, its complete image goes into the WAL, so recovery never has to trust a half-written page. The last session stops the server the way a crash would (pg_ctl stop -m immediate skips the shutdown checkpoint). On the next start PostgreSQL finds the shutdown was not clean, replays the WAL from the last checkpoint, and every committed row is there. An immediate stop tests WAL replay, not the disk, because the operating system still had its cache.

WAL

The write-ahead log: every change is logged, and the log flushed, before the data pages it describes may be written.

Checkpoint

The point at which all changed pages have been written, so recovery can start replaying WAL from there.

synchronous_commit

Whether COMMIT waits for its WAL to reach disk. off trades the last moments of commits on a crash for speed, without corruption.

LSN

Log sequence number: a byte position in the WAL, such as 0/2131C90.

Builds on Basics & Commands › Built-in Functions and Types

Most tables store only the current value. A bitemporal design stores two separate timelines. Valid time is when a fact is true in the world, such as the period a price applied. Transaction time is when the database believed a fact. When a company restates last year’s revenue, you don’t overwrite the old figure; you close the period in which it was believed and add the new one. An as-of query then answers “what did we know on 30 June 2025?”. Backtests need this: using today’s restated figures adds look-ahead bias, information nobody had at the time.

PostgreSQL has range types for periods. A daterange such as [2025-02-20,2026-02-18) is half-open, so one period can end on the day the next begins without overlapping, and @> tests whether a range contains a date. PostgreSQL 18 adds temporal keys. PRIMARY KEY (ticker, fiscal_year, known WITHOUT OVERLAPS) means one belief per fact at any moment. It is enforced by a GiST exclusion constraint, and the btree_gist extension lets that index handle the plain columns too. Adding the restated figure without closing the old one is refused, with both conflicting periods in the error. The SQLite lesson needed a hand-written trigger to do the same job.

The second session adds a temporal foreign key, also new in PostgreSQL 18. FOREIGN KEY (product_id, PERIOD sold) REFERENCES prices (product_id, PERIOD valid) requires every day of a sale’s period to fall inside the product’s price periods. A sale may span two periods if they meet, as sale 2 does, but a sale before the first price exists is refused. Joining with the overlap operator && then shows which price applied to each part of each sale.

Valid time

When a fact is true in the modelled world.

Transaction time

When the database recorded a fact, and when it was superseded.

WITHOUT OVERLAPS

PostgreSQL 18: the last column of a primary or unique key is a range, and rows with equal other columns may not have overlapping ranges.

PERIOD foreign key

PostgreSQL 18: the child’s range must be fully covered by the parent rows’ ranges for the same key.

Northwind Traders is a made-up company that imports speciality foods (Ipoh Coffee from Singapore, Chartreuse from France, Gorgonzola from Italy) and sells them to shops and restaurants in 21 countries. Microsoft created it as the sample database for Microsoft Access in the early 1990s, and SQL Server 7.0 and 2000 installed it alongside pubs. From SQL Server 2005, Microsoft replaced both with AdventureWorks, but Northwind stayed the database every SQL tutorial seemed to use. Microsoft still publishes the original install script, instnwnd.sql, on GitHub under the MIT licence.

It lasted because it is small enough to keep in your head and still realistic. In 13 tables it has a business you already understand and every relationship type: one-to-many, many-to-many through a junction table, and a table that points at itself. It has deliberate copies of data, NULLs with a meaning, derived money values, dates, pictures, and a set of saved reports. It also has 830 orders from July 1996 to May 1998, enough data that a wrong query gives an answer that looks wrong.

This tab teaches what Northwind was built to teach, in the order a course would: the model (tables, keys, relationships, normalisation), asking questions (filtering, NULL, joins, aggregates, dates, subqueries), Microsoft’s own reports (views, stored procedures as PostgreSQL functions, and window functions, which Northwind predates), and keeping data correct (constraints, transactions, indexes, binary data). It ends with exercises and an account of what changed in the move to PostgreSQL. The same lessons for SQLite are in the SQLite3 guide, so you can compare the two databases question by question.

Every arrow on the diagram above is a foreign key: a column in one table that holds the primary key of a row in another table. Order 10248 has customer_id = 'VINET', and that value is the entire link. orders holds no copy of the customer’s name or address, only the key, and a query uses the key to look up the rest. Each arrow starts at the column holding the key (the child table, the “many” side) and points to the table that key identifies (the parent table, the “one” side).

Why split the data at all? Northwind could have been a single wide spreadsheet with one row per order line and the customer, product, supplier and salesperson typed out on every row. Each customer’s address would then be repeated on every line of every order they placed. To change a phone number you’d have to find every copy, and a copy you missed would contradict the others. A new customer couldn’t be recorded until they ordered something. Splitting the data into tables stores each fact once, and the foreign keys put the pieces back together when you ask a question. Lesson 5 covers the few copies Northwind keeps on purpose.

Northwind’s 13 tables are joined by 14 foreign keys. The table below groups them into four areas of the business and explains why each one exists.

Northwind Traders imports speciality foods from around the world and resells them. Its database records each step of that business. suppliers provide products, which belong to categories. customers place orders, each order has one or more lines in order_details, an employee takes the order, and one of three shippers delivers it. Employees also cover sales territories, grouped into four regions.

\dt lists the 13 tables, and \dv lists the 16 views (Part 3 covers them). The first query uses UNION ALL to stack five one-row counts into a single result, which is a quick way to size up a database you’ve just opened. \d shippers describes one table: its columns and types, its primary key, and which other tables point at it.

Microsoft wrote names such as OrderID and CustomerID in mixed case, and one table name even has a space: "Order Details". This copy uses lower-case snake_case names because of a PostgreSQL rule. Unquoted identifiers are folded to lower case, so Order_Details finds order_details. A quoted identifier is used exactly as written, so "Order_Details" looks for a table with capital letters and fails. If a table is created with quotes and capitals, every query has to quote it forever. Lower-case names never need quotes.

Identifier

The name of a table, column, view or index. Unquoted, PostgreSQL folds it to lower case; double-quoted, it is used exactly.

UNION ALL

Stacks the rows of two SELECTs with the same number of columns, keeping duplicates.

\d, \dt, \dv

psql meta-commands: describe one object, list tables, list views.

Every table needs a primary key: a column, or group of columns, whose value is different in every row. Northwind uses all three common kinds. Customers have a natural key, a five-letter code made from the company name (ALFKI for Alfreds Futterkiste), which people can read and remember. Orders, products and employees have surrogate keys: plain numbers with no meaning of their own. A surrogate key never has to change. A natural key does when the thing it was made from changes, for example when a company is renamed.

order_details has a composite key: (order_id, product_id). Neither column is unique on its own, but the pair is. The pg_constraint catalog lists every rule on the table with its type: p for the primary key, f for foreign keys, c for checks, and, new in PostgreSQL 18, n for each NOT NULL. The key also enforces a business rule: an order can’t list the same product twice. Try it and PostgreSQL names the constraint and the duplicate key.

Surrogate keys here are identity columns, GENERATED BY DEFAULT AS IDENTITY, the SQL-standard replacement for the older serial. Leave the column out of an INSERT and PostgreSQL supplies the next number: the new shipper gets 4. RETURNING hands the new key straight back. The insert ran inside a transaction that was rolled back, so the database is unchanged.

Natural key

A key made from real data people already use, such as a customer code or an ISBN.

Surrogate key

A meaningless number assigned by the database, here from an identity column.

Composite key

A primary key made of two or more columns that are unique only together.

Identity column

GENERATED ... AS IDENTITY: the column draws its values from a sequence. ALWAYS refuses manual values; BY DEFAULT accepts them.

Most of Northwind is one-to-many. One customer has many orders, one order has many lines, and one category has many products. The foreign key always sits on the many side: orders.customer_id, not a list of order numbers stored in customers. Joining from the one side to the many side and counting is the most common question you’ll ask a database. PostgreSQL lets the first query group by c.customer_id and still select c.company_name, because the company name depends on the primary key it grouped by.

Employees and territories are many-to-many. An employee covers several territories, and the design allows a territory to have several employees. A many-to-many relationship can’t be stored with a single foreign key, so Northwind uses a junction table, employee_territories, with one row per link and a composite key made of two foreign keys. To get from employees to regions you join through it: employees → employee_territories → territories → region.

The data is narrower than the design allows. The 49 links cover 49 different territories, so no territory is actually shared, and the LEFT JOIN finds four of the 53 territories that nobody covers. This is a real modelling lesson: the schema says what may be true, and only a query tells you what is true.

One-to-many

Each row on the "one" side matches zero or more rows on the "many" side. The foreign key lives on the many side.

Many-to-many

Rows on both sides can match several rows on the other. It needs a junction table.

Junction table

A table whose rows each link one row of table A to one row of table B, also called a bridge or link table.

employees.reports_to is a foreign key that points back into employees. A table can reference itself, and this is how org charts, threaded comments, folder trees and bills of materials are usually stored. To read it you join the table to itself under two aliases: e for the employee and m for the manager. It has to be a LEFT JOIN, because Andrew Fuller, the Vice President, reports to nobody. An inner join would drop him.

A self-join climbs only one level. To walk the whole tree, however deep, use a recursive CTE. The first SELECT (the anchor) finds the root, the person with reports_to IS NULL. The second SELECT joins each new level to the rows found so far, and PostgreSQL repeats it until a level comes back empty. The path column builds a breadcrumb like Fuller > Buchanan > King. Sorting by it puts each manager directly above their own staff, and repeat(' ', depth) indents each level. The anchor casts last_name::text because a recursive CTE’s column must have the same type in both halves, and concatenating varchar values produces text.

The tree is three levels deep. Fuller has five direct reports. One of them, Steven Buchanan, the Sales Manager, runs three more representatives of his own. The Master’s Degree tab shows PostgreSQL’s SEARCH and CYCLE clauses for larger graphs.

Self-referencing key

A foreign key that points at the primary key of its own table.

Table alias

A short second name for a table within one query (employees e). It is required when the same table appears twice.

Recursive CTE

WITH RECURSIVE: an anchor query plus a recursive query that runs repeatedly until it adds no new rows.

Normalisation means storing each fact in exactly one place. A category’s name lives only in categories, and a product stores only the number category_id. Rename Dairy Products once and all ten dairy products show the new name, because none of them holds a copy. Copies drift apart; references can’t.

Northwind also breaks the rule twice, and it does so deliberately. Each order_details line has its own unit_price, a copy of the product’s price on the day of the sale. Queso Cabrales was sold at 14.00, then 16.80, then 21.00, and the catalogue now says 21.00. If the order line only pointed at products, every old invoice would be re-priced each time the catalogue changed. Across the database, 658 of the 2,155 lines were charged a price that is no longer the list price. The query counts them with count(*) FILTER (WHERE ...), PostgreSQL’s way to count only the rows that match a condition inside an aggregate.

The second copy is the ship-to address on every order. A customer can ask for delivery somewhere else. Around the Horn’s office is on Hanover Square in London, but all 13 of its orders were shipped to Brook Farm near Colchester. An order also has to keep showing where it actually went after the customer moves. A copy is the right design when it records what was true at that moment, not a second version of a fact that is true now.

Normalisation

Organising tables so each fact is stored once and everything else refers to it by key.

Denormalisation

Storing a copy of data on purpose, usually to record history or to speed up reads.

FILTER

aggregate(...) FILTER (WHERE condition): the aggregate sees only the rows that match.

WHERE keeps the rows for which a condition is true. IN (...) is shorthand for several ORs, and in LIKE, % matches any run of characters. LIKE is case-sensitive in PostgreSQL; ILIKE is the case-insensitive version. Northwind’s data is international, so München and Münster come back with their umlauts intact.

region is where Northwind teaches NULL. Only some countries’ addresses have a state or province, so just 31 of the 91 customers have one, and the other 60 have no region at all. NULL means “unknown”, and any comparison with an unknown value is itself unknown, never true. So region = NULL matches nothing, not even the 60 NULLs. Use IS NULL. The same logic makes region <> 'WA' return 28, not 88: the 60 customers with no region are not counted as “not WA”. PostgreSQL has a comparison that treats NULL as an ordinary value: IS DISTINCT FROM returns 88.

Orders use NULL for “hasn’t happened yet”: 21 orders, the oldest placed on 8 April 1998, have no shipped_date. count(region) counts only non-NULL values, which makes it a quick NULL census, and coalesce() replaces NULL with something readable. psql prints NULL as an empty cell, which looks exactly like an empty string; \pset null '(null)' makes the difference visible.

NULL

A marker for a missing or unknown value. It is not zero and not an empty string.

Three-valued logic

In SQL a condition is TRUE, FALSE or UNKNOWN. WHERE keeps only rows where it is TRUE.

IS DISTINCT FROM

A comparison in which NULL equals NULL and differs from every value, so it never returns UNKNOWN.

An order row on its own is mostly codes: VINET, 5, 3. A join follows each foreign key to the row it names and brings back readable columns. Each JOIN ... ON adds one table, and the ON clause says which key matches which. Four tables give the company, the salesperson’s surname and the shipper’s name for each order.

An inner join keeps only rows that have a match on both sides. A left join keeps every row from the left table, and where there is no match it fills the right table’s columns with NULL. That makes LEFT JOIN ... WHERE right.key IS NULL one way to find rows with nothing related, an anti-join. NOT EXISTS says the same thing more directly. In Northwind they find the two customers who have never ordered, FISSA and Paris spécialités.

The trap comes when you count after a left join. A customer with no orders still produces one row, padded with NULLs, so count(*) reports 1 order for FISSA. count(o.order_id) counts only non-NULL values, so it correctly reports 0. Whenever a left join feeds an aggregate, count a column from the right-hand table.

INNER JOIN

Returns only pairs of rows that satisfy the ON condition.

LEFT JOIN

Returns every left row once or more; missing right-hand values become NULL.

Anti-join

A query for rows that have no match: LEFT JOIN plus IS NULL, or NOT EXISTS.

Northwind has no revenue column. Revenue is derived: each order line is worth unit_price × quantity × (1 − discount), where discount is a fraction such as 0.15. In this copy unit_price is numeric(10,2), exact decimal, but discount is real, a 4-byte floating-point number, as it was in Microsoft’s original. Mixing the two makes PostgreSQL compute in double precision, and 42.40 × 35 × 0.85 comes out as 1261.3999911546707. Casting the discount with discount::numeric keeps the arithmetic exact: 1261.4000. Money calculations belong in numeric.

Summed exactly over all 2,155 lines, revenue is $1,265,793.04. When you round changes the answer. Rounding every line to the cent first, which is what an invoice does, gives $1,265,793.29, 25 cents more. Neither number is wrong: the first is the exact total and the second is what the invoices add up to. Decide which one a report means, and round once, at the step that matches it.

GROUP BY collapses rows into one row per group, and aggregate functions (sum, count, avg) summarise each group. count(DISTINCT od.order_id) counts orders, not lines. WHERE filters rows before grouping, and HAVING filters groups after, so “employees with more than 100 orders” needs HAVING, because the count doesn’t exist until the group does. Beverages leads revenue and Margaret Peacock leads the sales team with 156 orders.

numeric

Exact decimal arithmetic. Use it for money; real and double precision are binary approximations.

GROUP BY

Forms one output row per distinct value of the grouping columns.

HAVING

A filter applied to groups after aggregation. WHERE runs before it.

PostgreSQL has a real date type, so this copy stores Northwind’s dates as dates, not as text. Microsoft’s script wrote them as '7/4/1996', and the DateStyle setting shows why that format is dangerous. With the default ISO, MDY it reads as 4 July 1996. Switch to day-first DMY and the same string becomes 7 April. PostgreSQL always accepts ISO 1996-07-04 unambiguously, and always prints dates that way under ISO output.

Date arithmetic is built in. shipped_date - order_date is an integer number of days, so average shipping time is just avg() of that difference. extract(year FROM d) pulls out a part of a date, date_trunc('quarter', d) rounds it down to the start of its quarter, and to_char() formats it. Adding an integer to a date adds days. Adding an interval such as '1 month' understands calendar months and returns a timestamp. age() gives the span in years, months and days.

The data runs from 4 July 1996 to 6 May 1998, so 1996 and 1998 are partial years. Compare 152 orders with 408 and you’re comparing six months with twelve. Federal Shipping is the fastest carrier at 7.5 days on average, and United Package carries the most orders but is late most often.

date

A calendar date with no time of day. timestamp and timestamptz add the time.

DateStyle

The setting that decides how ambiguous input such as 7/4/1996 is read, and how dates are printed.

interval

A span of time (days, months, years) that can be added to dates and timestamps.

A subquery is a SELECT nested inside another. A scalar subquery returns one value and can go wherever a value can: Microsoft’s view products_above_average_price compares each price with (SELECT avg(unit_price) FROM products). A correlated subquery refers to the outer row (p2.category_id = p.category_id), so it is worked out again for each product. That gives the most expensive product in each category without any grouping. PostgreSQL has a shorter way to write that: DISTINCT ON (category_name) keeps the first row of each category, in the order ORDER BY sets.

EXISTS asks whether a subquery returns any rows at all, and it stops at the first one. Combining EXISTS with NOT EXISTS describes a pattern that is awkward to write any other way: a customer who ordered in 1996 and never came back. Only Centro comercial Moctezuma fits.

Set operators combine whole result sets. UNION merges two lists and removes duplicates, and Microsoft’s view customer_and_suppliers_by_city uses it to put customers and suppliers in one directory. EXCEPT keeps rows from the first query that the second doesn’t have: nine countries where Northwind sells but buys nothing.

Scalar subquery

A subquery that returns exactly one value.

Correlated subquery

A subquery that refers to columns of the outer query, so it is evaluated for each outer row.

DISTINCT ON

PostgreSQL only: one row per distinct value of the listed expressions, the first in ORDER BY order.

A view is a named SELECT. It holds no data of its own: \sv order_subtotals prints the query it stores, and PostgreSQL runs that query every time you select from the view. Northwind ships 16 views, and they were its main teaching aid. Each one is a report someone would really ask for (invoices, sales by category, quarterly orders), saved so that nobody has to rewrite the join.

Views can be built on views. order_subtotals sums each order’s lines, product_sales_for_1997 uses it, and category_sales_for_1997 is built on that. Each layer is simple, and together they answer a hard question. Name the layers well and a report becomes a short chain of readable steps.

All 16 views were written in Microsoft’s dialect, T-SQL, and several needed translating. T-SQL joins strings with +. SQLite silently turns 'Nancy' + ' ' into 0, but PostgreSQL refuses: there is no + operator for text, and the HINT says so. String concatenation is ||. A materialized view is the other kind: it stores its rows when created, so reading it is fast, but it shows the data as of the last REFRESH MATERIALIZED VIEW.

View

A stored SELECT you can query like a table. It is re-evaluated on every use.

Materialized view

A view whose result is stored, and recomputed only by REFRESH MATERIALIZED VIEW.

|| (concatenation)

The SQL-standard string-join operator. In PostgreSQL, + on text is an error.

Northwind’s SQL Server version ships seven stored procedures: queries with named parameters such as CustOrderHist @CustomerID, stored and run by the server. PostgreSQL is a server too, so they translate into real functions, and the download already contains all seven. Each is written LANGUAGE sql and returns a table, so you call it in the FROM clause like a table: SELECT * FROM cust_order_hist('ALFKI'). SET ROWCOUNT 10 became LIMIT 10, and SalesByCategory keeps Microsoft’s default year of 1998, so the second argument is optional.

Writing one yourself takes a few lines. customer_revenue returns a single number, so it can be used inside a SELECT list, once per customer. STABLE tells the planner the function only reads the database and gives the same answer within one statement. PostgreSQL also has CREATE PROCEDURE and CALL: a procedure returns no rows but may commit inside itself, which is what the durability lesson on the Master’s Degree tab uses.

The idea underneath is the parameter: write the SQL once with a placeholder and supply the value separately. PREPARE does that in plain SQL with $1. The value never becomes part of the SQL text, so a customer ID like x' OR '1'='1 is looked up as a strange ID and finds 0 orders, instead of being run as SQL. In application code the driver does the same with its placeholders, %s in Python’s psycopg. That protection against SQL injection was a main reason stored procedures became popular, and parameters give it to every query.

Function

A named routine stored in the database. A set-returning function (RETURNS TABLE) is queried like a table.

Bound parameter

A placeholder ($1, %s) whose value is supplied separately from the SQL text.

SQL injection

An attack in which user input is pasted into SQL text and changes what the statement does.

Northwind’s reports were written for SQL Server 2000, which had no window functions. A window function computes across a set of related rows, as an aggregate does, but it keeps every row instead of collapsing them. OVER (PARTITION BY country ORDER BY revenue DESC) means “within each country, ranked by revenue”, so every customer gets a rank in its own country in a single pass.

sum(sales) OVER (ORDER BY month) is a running total: the sum of this month and every month before it. lag(sales) reads the previous row’s value, which gives month-over-month change. The first month has nothing before it, so its change is NULL. Because order_subtotals is exact numeric, every total in the output is exact to the cent, with no rounding needed.

row_number() numbers the rows within each partition, and filtering on that number in an outer query gives a top-N per group, here the two best-selling products in each category. Window functions have been in PostgreSQL since version 8.4 (2009).

Window function

A function evaluated over a "window" of rows related to the current row, without grouping them away.

PARTITION BY

Splits rows into independent windows, one per value.

rank() / row_number() / lag()

Rank with ties, a unique sequence number, and the value from the previous row.

PostgreSQL enforces foreign keys on every write, with no setting to turn on. An order for a customer called ZZZZZ, who doesn’t exist, is refused, and so is deleting ALFKI, because six orders still point at it. Each error names the constraint and the key involved. The Master’s Degree tab’s first lesson covers referential actions such as ON DELETE CASCADE.

CHECK constraints enforce rules within a row. This copy keeps Microsoft’s rules: quantities must be positive, a discount must be between 0 and 1, and stock can’t go below zero. The error shows the whole failing row. Declared lengths are enforced too: city is varchar(15), so a 20-character town is refused. Microsoft’s CK_Birthdate required BirthDate < getdate(). PostgreSQL accepts current_date in a CHECK, and the session shows it working, but the result is misleading. A check runs only when a row is written and is never re-checked as time passes, and PostgreSQL assumes checks always give the same answer, so a backup can fail to restore later. This copy checks birth_date < hire_date instead.

Adding a constraint to a table that already has data checks every existing row first. A rule that Côte de Blaye at 263.50 breaks is refused. NOT VALID adds the constraint without checking old rows. New writes must obey it from that moment, and VALIDATE CONSTRAINT checks the old rows later, when it suits you. This is how large production tables get new rules without a long lock.

CHECK constraint

A condition that every row must satisfy. It is tested on INSERT and UPDATE.

NOT VALID

Adds a constraint that applies to new writes immediately, while existing rows are checked later by VALIDATE CONSTRAINT.

varchar(n)

Text with a maximum length that PostgreSQL enforces. Plain text has no limit.

Taking an order is several writes that only make sense together: the order header, its lines, and a reduction in stock. A transaction makes them atomic. Between BEGIN and COMMIT nothing is final, and ROLLBACK throws all of it away. The last query proves it: still 830 orders, and Chai back at 39 in stock. While the transaction is open, psql’s prompt shows =*>.

RETURNING order_id \gset hands back the new key and stores it in a psql variable, so the following statements can use :order_id. The identity column assigned 11078, one more than Microsoft’s highest order. The order lines copy unit_price from products at this moment, the point-in-time copy from lesson 5, and the address is copied the same way.

Then an attempt to sell 100 Chai breaks the units_in_stock >= 0 check, and here PostgreSQL behaves differently from SQLite. An error aborts the whole transaction. The prompt changes to =!>, and every later statement is refused with “current transaction is aborted” until you roll back. A SAVEPOINT set before a risky statement gives you somewhere to return to: ROLLBACK TO SAVEPOINT undoes only the failed part, and the order and its 370.00 subtotal are still there. Applications use savepoints for the same reason, often without knowing it. Many drivers and frameworks, Django’s nested atomic() among them, create a savepoint for each nested block.

Transaction

A group of statements that succeed or fail as a unit (BEGIN ... COMMIT / ROLLBACK).

Aborted transaction

In PostgreSQL, any error inside a transaction block: nothing more runs until ROLLBACK (or ROLLBACK TO a savepoint).

SAVEPOINT

A named point inside a transaction that you can roll back to without abandoning the whole transaction.

\d orders lists the table’s indexes and foreign keys. Microsoft’s script indexes almost every foreign key, and EXPLAIN shows why. For ALFKI’s orders the planner expects 6 of 830 rows and uses orders_customer_id: a bitmap index scan collects the matching row addresses, then a bitmap heap scan reads each page once. Nothing indexes ship_country, so that query reads every row, a Seq Scan.

The composite primary key’s index serves lookups by its first column, order_id. Like a phone book sorted by surname and then first name, it can’t find rows by its second column alone, which is why product_id has an index of its own. An index is used only when the planner thinks it is cheaper. customers has an index on city, but the whole table fits in a few pages, so for London the planner reads them all instead. On 91 rows that is the right choice.

EXPLAIN ANALYZE runs the query and adds actual times and row counts. For Mexico’s orders the planner builds a hash table from the 5 Mexican customers and probes it with all 830 orders, a hash join. The estimates (46 rows) and the actual count (28) are close enough for a good plan. Microsoft’s original also had duplicate indexes, such as two identical ones on the customer column of orders. A duplicate makes every write slower and never helps a read, so this copy keeps one of each. The Master’s Degree tab covers the planner’s statistics in depth.

Seq Scan / Index Scan

In a plan: reading every row in table order, or looking rows up through an index.

Bitmap scan

Collect matching row addresses from an index first, then visit each table page once.

Leftmost prefix

A multi-column index helps queries that constrain its first column (first two, ...), not later columns alone.

categories.picture and employees.photo hold images as bytea, PostgreSQL’s binary type: raw bytes, stored and returned without interpretation. octet_length() counts bytes; every category picture is exactly 10,746. psql displays bytea in hex, starting with \x.

The first bytes aren’t an image header. Microsoft Access wrapped each picture in an OLE object, a container that recorded which program (Paint) should open it. The real Windows bitmap starts where its signature BM (hex 424d) appears, at byte 79, after a 78-byte wrapper. substring(picture FROM 79) peels the wrapper off.

To save it as a file on your own computer, the session turns the bytes into a temporary large object with lo_from_bytea(), and psql’s \lo_export copies that to a local file. \lo_unlink deletes the temporary object, and \! runs a shell command, here file, which recognises a 172 × 120, 16-colour bitmap. Storing images in the database keeps everything together and transactional, which suits small images like these. For large media, most designs store a file path and keep the bytes elsewhere. Northwind does both: it also has a photo_path column holding the old http://accweb/... address of each photo on Microsoft’s internal network.

bytea

Byte array: PostgreSQL’s type for binary data. '\x424d'::bytea is a literal in hex.

OLE object

An Access/Windows wrapper around embedded content, naming the program that created it.

Large object

A separate PostgreSQL store for big binary values, read and written in pieces; psql can import and export them.