Postgres by Example

Dariush Abbasi
English edition · PostgreSQL 17 target

Fifty-seven short lessons take you from the first query through data modeling, querying, indexes, transactions, stored logic, search, and day-to-day operations. Each lesson includes a SQL file; the backup lesson documents shell commands, and the psql reference includes catalog-query alternatives.

Start reading · Print the whole book · Source repository

Prerequisites

The lessons target PostgreSQL 17 and use the psql command-line client. Later-version features are identified explicitly. The official documentation is a useful companion.

Download the complete SQL bundle and extract it into a new directory. Run commands from the directory containing source/. Use a disposable database: these lessons create, change, and drop their demo tables. The examples show the database name postgres; substitute your own practice database, such as pbe:

createdb pbe
psql -X -v ON_ERROR_STOP=1 -f source/first-query.sql pbe

Read in chapter order. Before chapter 6, create the sample table using the files introduced in chapters 16 and 24:

psql -X -v ON_ERROR_STOP=1 -f source/create-table.sql -f source/insert.sql pbe

Chapter 16 recreates the table; chapter 24 fills it again. Later join lessons depend on chapter 29, and the recursive example also uses chapter 31. When skipping ahead, follow the lesson’s prerequisites. Optional exercises can change later results. Dates, UUIDs, catalog listings, query plans, and timings vary; ellipses in console transcripts mark omitted output.

Contents

Examples

1. Example 1.1 — First Query
2. Example 2.1 — psql Basics
3. Example 3.1 — SELECT Basics
4. Example 4.1 — WHERE
5. Example 5.1 — ORDER BY
6. Example 6.1 — SELECT from a Table
7. Example 7.1 — LIMIT and OFFSET
8. Example 8.1 — DISTINCT
9. Example 9.1 — NULLs
10. Example 10.1 — Expressions
11. Example 11.1 — Numeric Types
12. Example 12.1 — Text Types
13. Example 13.1 — Boolean and Dates
14. Example 14.1 — UUID and JSONB
15. Example 15.1 — Arrays and ENUM
16. Example 16.1 — CREATE TABLE
17. Example 17.1 — Column Types and Constraints
18. Example 18.1 — ALTER TABLE and DROP
19. Example 19.1 — Primary Keys and UNIQUE
20. Example 20.1 — Foreign Keys and REFERENCES
21. Example 21.1 — NOT NULL and DEFAULT
22. Example 22.1 — Identity Columns and Sequences
23. Example 23.1 — Schemas and search_path
24. Example 24.1 — INSERT
25. Example 25.1 — UPSERT (ON CONFLICT)
26. Example 26.1 — UPDATE
27. Example 27.1 — DELETE
28. Example 28.1 — RETURNING
29. Example 29.1 — INNER and LEFT JOIN
30. Example 30.1 — RIGHT and FULL JOIN
31. Example 31.1 — Self-Join
32. Example 32.1 — UNION, INTERSECT, EXCEPT
33. Example 33.1 — COUNT, SUM, AVG
34. Example 34.1 — GROUP BY
35. Example 35.1 — HAVING
36. Example 36.1 — Scalar and IN Subqueries
37. Example 37.1 — EXISTS and Derived Tables
38. Example 38.1 — Common Table Expressions (WITH)
39. Example 39.1 — Window Functions
40. Example 40.1 — String and Numeric Functions
41. Example 41.1 — Date Functions and COALESCE
42. Example 42.1 — CASE
43. Example 43.1 — CREATE INDEX
44. Example 44.1 — When to Index
45. Example 45.1 — EXPLAIN and Query Plans
46. Example 46.1 — BEGIN, COMMIT, ROLLBACK
47. Example 47.1 — Savepoints
48. Example 48.1 — CREATE VIEW
49. Example 49.1 — Materialized Views
50. Example 50.1 — Functions and PL/pgSQL
51. Example 51.1 — Triggers
52. Example 52.1 — Full-Text Search
53. Example 53.1 — VACUUM and ANALYZE
54. Example 54.1 — pg_dump and pg_restore
55. Example 55.1 — Roles and GRANT
56. Example 56.1 — psql Meta-commands
57. Example 57.1 — COPY

About this edition

This English web edition follows the boringcollege/postgres-by-example repository at commit ffde0d358722498e37982b57bdbd711871a61c43. The lesson prose and SQL examples are kept together, with reviewed technical corrections documented in the editorial notes. The repository is licensed under Creative Commons Attribution 4.0 International.

Publication and license · Editorial corrections

1 First Query

The fastest way to confirm your PostgreSQL setup is working is to run a query that needs no tables. PostgreSQL is happy to evaluate expressions on their own — every SELECT does not need a FROM. We will use that property to verify three things in one go: the server is running, your client can reach it, and you have a recent enough version to follow along.

What you’ll learn

• How to run a SQL file with psql -f
• A minimal SELECT with no table
• How to read the PostgreSQL version string
• The difference between client and server versions

Example

-- Simplest query: no tables, just a literal
SELECT 1;

-- Arithmetic and string expressions work without a FROM
SELECT 2 + 3 AS sum, 'hello, postgres' AS greeting;

-- Check your PostgreSQL server version
SELECT version();

-- A more readable version, when you only need the number
SHOW server_version;

SELECT 1 returns a single row with one column — the integer 1. The expression form (SELECT 2 + 3, 'hello, postgres') shows that any expression can be the body of a SELECT; PostgreSQL evaluates and returns it. version() is a built-in function that returns the full version string (operating system, compiler, etc.), while SHOW server_version returns just the version number. Both report the connected server’s version. psql --version reports the client version, which may differ.

Run the example

To run this file from the repository root:

$ psql -f source/first-query.sql postgres
 ?column?
----------
        1
(1 row)

 sum | greeting
-----+-----------------
   5 | hello, postgres
(1 row)

                                                    version
------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.x on x86_64-pc-linux-gnu, compiled by gcc ...
(1 row)

 server_version
----------------
 17.x
(1 row)

Your version string will vary. If you see output like above, everything is wired up.

Common pitfalls

• “could not connect to server” — the daemon isn’t running. Start it with brew services start postgresql (macOS), sudo systemctl start postgresql (Linux), or whatever your install uses.
• “role ‘you’ does not exist” — PostgreSQL doesn’t know about your OS user as a database user. Create one with createuser --superuser $(whoami) from a shell that has access to the cluster.
• “database ‘postgres’ does not exist” — some installs (especially homebrew on macOS) don’t create a default postgres database. Run createdb postgres once, or use createdb pbe and pass pbe as the database name to every command.

Downloads

SQL file: first-query.sql · All lesson files · Setup guide

2 psql Basics

psql is the official command-line client for PostgreSQL, and it is excellent. Most professionals reach for it before any GUI — it is fast, scriptable, and ships with every PostgreSQL install. You use it two ways: in batch mode (psql -f file.sql) to run a script, or interactively (psql) to type SQL and meta-commands one at a time. This lesson focuses on the basics; a later lesson covers more meta-commands.

What you’ll learn

• The two modes of psql: file and interactive
• How to identify your session (current user, database, schema)
• The difference between SQL statements and psql meta-commands
• Where to get help: \? for meta-commands, \h for SQL

Example

-- List user databases. Equivalent to \l in interactive psql.
SELECT datname
FROM pg_database
WHERE datistemplate = false
ORDER BY datname
LIMIT 5;

-- Identify the session: who am I, where am I, what's on my search_path?
SELECT current_user, current_database(), current_schema();

-- A few server settings worth knowing about
SHOW timezone;
SHOW server_encoding;

pg_database is one of many system catalogs — internal tables PostgreSQL exposes for inspection. The datistemplate = false filter hides template0 and template1, which are the templates new databases are cloned from. current_user, current_database(), and current_schema() are session functions; they answer questions you will ask yourself often when debugging connection or permission issues. SHOW displays runtime parameters — these can be changed at the server, database, role, or session level.

When you use psql interactively, you can also type meta-commands (also called backslash commands) that the client interprets locally:

\l         list databases
\c db      connect to database `db`
\dt        list visible tables in the current search_path
\d table   describe a table (columns, types, indexes)
\dn        list schemas
\df        list functions
\du        list roles (users/groups)
\timing    toggle showing query timing
\x         toggle expanded display (great for wide rows)
\?         help for meta-commands
\h SELECT  help for the SELECT command (or any SQL keyword)
\q         quit

Meta-commands are interpreted by the psql client, both interactively and in files read with psql -f. Commands such as \dt send catalog queries to the server; the backslash syntax itself is not server SQL.

Run the example

To run this file:

$ psql -f source/psql-basics.sql postgres
       datname
--------------------
 postgres
(1 row)

 current_user | current_database | current_schema
--------------+------------------+----------------
 your_user    | postgres         | public
(1 row)

 TimeZone
----------
 UTC
(1 row)

 server_encoding
-----------------
 UTF8
(1 row)

Common pitfalls

• Meta-commands require the psql client. psql -f executes \dt in a file; a driver or GUI that sends the file directly as SQL will not interpret it.
• Confusing current_user and session_user: when a SET ROLE is in effect, current_user reflects the effective role; session_user is the original login.
• Encoding mismatches: if your terminal is not UTF-8 and your database is, you may see garbled text — set client_encoding to match your terminal.

Downloads

SQL file: psql-basics.sql · All lesson files · Setup guide

3 SELECT Basics

SELECT is the heart of SQL — it is how you read data. The simplest form is SELECT expression, with no FROM, which evaluates the expression and returns one row. The more common form, SELECT columns FROM table, returns one row per matching row in the table. Either way, the result is a relation: rows and columns, with column names you can rename using AS.

What you’ll learn

• SELECT with literals and expressions (no table needed)
• Naming result columns with AS
• Selecting multiple columns in one query
• Reading from a system catalog table
• Column aliases vs. table aliases

Example

-- Literals and expressions: no FROM required
SELECT 1 AS one;
SELECT 2 + 3 AS sum, 10 / 4 AS quotient, 10 / 4.0 AS real_quotient;
SELECT 'hello' AS greeting, length('hello') AS chars;

-- Multiple columns in one row
SELECT 1 AS a, 2 AS b, 'x' AS c;

-- Reading from a table (here, a system catalog)
SELECT relname, relkind
FROM pg_class
WHERE relname = 'pg_database'
LIMIT 1;

Each SELECT produces a result set. AS column_name renames the output column — without it, PostgreSQL invents a name like ?column? for literals or uses the source column name. The arithmetic line is worth a second look: 10 / 4 returns the integer 2 because both operands are integers, while 10 / 4.0 returns 2.5 because one operand is numeric and integer division does not apply. This trap shows up often when computing averages and ratios.

pg_class is the system catalog that lists relations — tables, indexes, views, sequences, materialized views, and more. The relkind column distinguishes them (r = ordinary table, i = index, v = view, m = materialized view, S = sequence). Looking at catalogs is a habit worth developing; everything about your schema is visible there.

Run the example

To run:

$ psql -f source/select-basics.sql postgres
 one
-----
   1
(1 row)

 sum | quotient | real_quotient
-----+----------+---------------
   5 |        2 |            2.5
(1 row)

 greeting | chars
----------+-------
 hello    |     5
(1 row)

 a | b | c
---+---+---
 1 | 2 | x
(1 row)

   relname   | relkind
-------------+---------
 pg_database | r
(1 row)

Common pitfalls

• Forgetting AS on an expression: SELECT count(*) works, but SELECT count(*) FROM ... WHERE total > 0 GROUP BY id gets unreadable without AS order_count. Always alias derived columns.
• Integer division: 1 / 2 returns 0. Cast one side: 1::numeric / 2, or 1.0 / 2.
• Double-quotes vs. single-quotes: 'hello' is a string literal; "hello" is an identifier (a column or table name). Mixing them up is one of the most common SQL beginner errors.

Downloads

SQL file: select-basics.sql · All lesson files · Setup guide

4 WHERE

WHERE is how you filter rows. The clause sits between FROM and ORDER BY, and PostgreSQL keeps only rows for which the condition evaluates to true. Anything that evaluates to false or NULL is dropped — this three-valued logic is one of SQL’s quirks and the cause of many beginner bugs. Get comfortable with it early.

What you’ll learn

• Filtering with comparison operators (=, <>, <, >, <=, >=)
• Pattern matching with LIKE (and ILIKE for case-insensitive)
• Combining conditions with AND, OR, NOT
• Membership and ranges: IN, BETWEEN
• Why = NULL does not work and what to use instead

Example

-- Equality and ordering
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY tablename
LIMIT 3;

-- Pattern matching: % matches any sequence, _ matches one character
SELECT tablename
FROM pg_tables
WHERE tablename LIKE 'pg_a%'
ORDER BY tablename
LIMIT 5;

-- Case-insensitive: ILIKE (PostgreSQL extension)
SELECT tablename FROM pg_tables WHERE tablename ILIKE 'PG_Class' LIMIT 1;

-- Multiple conditions
SELECT tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
  AND tablename LIKE 'pg_%'
  AND tablename NOT LIKE 'pg_stat_%'
ORDER BY tablename
LIMIT 5;

-- IN: membership test
SELECT tablename
FROM pg_tables
WHERE tablename IN ('pg_class', 'pg_database', 'pg_namespace')
ORDER BY tablename;

-- BETWEEN: inclusive range
SELECT n FROM generate_series(1, 10) AS n WHERE n BETWEEN 3 AND 7;

LIKE is the SQL standard pattern operator: % matches any string of characters (including empty), _ matches exactly one character. ILIKE is a PostgreSQL extension for case-insensitive matching. Index use depends on the pattern, collation, and operator class. Alphabetic prefixes generally need a suitable expression or trigram index; a B-tree can support some patterns beginning with characters unaffected by case. For more powerful matching, PostgreSQL also supports ~ (regex) and ~* (case-insensitive regex), as well as full-text search (covered later).

AND binds tighter than OR, so A AND B OR C means (A AND B) OR C. When in doubt, add parentheses — your future self reading the query will thank you. IN (...) is shorthand for = ANY (ARRAY[...]) and is equivalent to a chain of ORs.

Run the example

To run:

$ psql -f source/where.sql postgres
 schemaname |  tablename
------------+--------------
 pg_catalog | pg_aggregate
 pg_catalog | pg_am
 pg_catalog | pg_amop
(3 rows)
 ...

Common pitfalls

• = NULL and <> NULL are never true (they are NULL). Use IS NULL and IS NOT NULL. This trips up engineers from every other language.
• NOT IN (subquery) becomes mysterious if the subquery contains a NULL — nonmatching values yield NULL while matching values yield false, so the filter keeps no rows. Prefer NOT EXISTS for nullable subqueries.
• LIKE 'abc' does not match 'ABC'. Use ILIKE or apply LOWER() to both sides — but LOWER(col) = 'abc' defeats a plain index on col (add an expression index on LOWER(col) if you need it often).
• Be careful with IN ('a', 'b', NULL) — the NULL in the list contributes nothing useful and silently makes NOT IN behave unexpectedly.

Downloads

SQL file: where.sql · All lesson files · Setup guide

5 ORDER BY

ORDER BY sorts the result set. Without it, the order of rows is undefined — PostgreSQL is free to return rows in whatever sequence the planner finds convenient, and that order can change between runs. If your application cares about order, say so explicitly.

What you’ll learn

• Sorting with ORDER BY column
• ASC (default) and DESC
• Multi-column sort (tie-breakers)
• Controlling where NULLs land (NULLS FIRST / NULLS LAST)
• Sorting by expressions and by position

Example

-- Default ascending
SELECT tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY tablename
LIMIT 4;

-- Descending
SELECT tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY tablename DESC
LIMIT 4;

-- Multi-column: sort by schema, then by name within schema
SELECT schemaname, tablename
FROM pg_tables
ORDER BY schemaname, tablename
LIMIT 5;

-- NULLs FIRST / LAST: default for ASC is NULLS LAST, for DESC is NULLS FIRST
SELECT * FROM (VALUES (1), (NULL), (2)) AS t(n) ORDER BY n NULLS FIRST;

-- Sort by an expression
SELECT tablename, length(tablename) AS len
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY length(tablename) DESC
LIMIT 3;

ORDER BY tablename sorts in ascending order — ASC is the default. DESC reverses. When two rows have the same first sort key, the second is used as a tie-breaker, and so on. You can sort by any expression, including ones that don’t appear in the select list, and by output position (ORDER BY 1, 2) — though using column names is much more readable.

NULL handling is worth knowing: ASC puts NULLs last; DESC puts NULLs first. Override with NULLS FIRST or NULLS LAST. For applications that always want to see NULLs at the end regardless of direction, write it out — relying on the default is a source of bugs when someone later flips ASC to DESC.

Run the example

To run:

$ psql -f source/order-by.sql postgres
 tablename
--------------
 pg_aggregate
 pg_am
 pg_amop
 pg_amproc
(4 rows)

   tablename
-----------------
 pg_user_mapping
 pg_type
 pg_ts_template
 pg_ts_parser
(4 rows)
 ...

Common pitfalls

• Sorting on text and getting surprising results: text sort respects the database’s collation (e.g. en_US.UTF-8), which may sort case-insensitively or treat accents specially. Use ORDER BY col COLLATE "C" for byte-order sorting.
• Forgetting ORDER BY with LIMIT: LIMIT 10 without ORDER BY returns some 10 rows, not necessarily the first or smallest. Pagination is meaningless without a deterministic order.
• Two rows with the same primary sort key may appear in any order — add the primary key as a final tie-breaker for fully deterministic results (ORDER BY created_at DESC, id DESC).
• Sorting can be expensive on large result sets. PostgreSQL can use an index to avoid sorting if the index order matches the requested order.

Downloads

SQL file: order-by.sql · All lesson files · Setup guide

6 SELECT from a Table

Now that fruits has rows, you can read them. SELECT * returns every column; listing column names returns just those, in the order you list them. The result is unordered unless you add ORDER BY — even if it looks ordered today, it may not be tomorrow.

What you’ll learn

• SELECT * FROM table for all columns
• Selecting specific columns and controlling their order in the result
• Combining SELECT with WHERE and ORDER BY
• Why SELECT * is fine for ad-hoc queries but risky in application code
• Result column names and how to override them

Example

-- All columns. The order matches CREATE TABLE.
SELECT * FROM fruits ORDER BY id;

-- Specific columns, in the order you list them
SELECT name, id FROM fruits ORDER BY id;

-- Renaming output columns
SELECT id AS fruit_id, name AS fruit_name FROM fruits ORDER BY id LIMIT 3;

-- Combining a filter and an order
SELECT id, name
FROM fruits
WHERE id BETWEEN 2 AND 4
ORDER BY name;

-- Counting rows
SELECT count(*) AS total FROM fruits;

Run this after create-table.sql and insert.sql. SELECT * returns every column in the physical column order. Listing columns gives you total control over the result shape, which matters when the data flows into application code that maps columns by position. AS renames a column in the output without changing anything in the table.

The space character is optional around * and around operators; the SQL parser is generous with whitespace. Multi-line queries are normal and encouraged for anything beyond two columns.

Run the example

To run:

$ psql -f source/select-from-table.sql postgres
 id  |   name
-----+-----------
   1 | apple
   2 | banana
   3 | cherry
   4 | date
   5 | elderberry
 101 | fruit_1
 102 | fruit_2
 103 | fruit_3
(8 rows)

    name    | id
------------+-----
 apple      |   1
 banana     |   2
 ...

Common pitfalls

• SELECT * FROM big_table; will fetch every column of every row across the network — slow and wasteful. List the columns you actually need.
• Output column names are not guaranteed unique. SELECT id, id FROM fruits has two columns both named id — application drivers may not handle that gracefully.
• In application code, prefer explicit columns over SELECT *. When the schema adds a column, the shape of SELECT * changes and may break code that assumes a particular column order or count.

Downloads

SQL file: select-from-table.sql · All lesson files · Setup guide

7 LIMIT and OFFSET

LIMIT n returns at most n rows. OFFSET m skips the first m rows of the result. Combined, they implement classic page-based pagination: page k of size p is LIMIT p OFFSET (k - 1) * p. Without an ORDER BY, “first” is meaningless — always pair LIMIT/OFFSET with an explicit order.

What you’ll learn

• Restricting result size with LIMIT
• Skipping rows with OFFSET
• Pagination patterns and their costs
• Why offset-based pagination scales poorly on large tables
• Keyset (cursor) pagination as the better alternative

Example

-- First two rows, ordered by id
SELECT * FROM fruits ORDER BY id LIMIT 2;

-- Skip one, then take two: rows 2 and 3
SELECT * FROM fruits ORDER BY id LIMIT 2 OFFSET 1;

-- Page 2 of 3 per page
SELECT * FROM fruits ORDER BY id LIMIT 3 OFFSET 3;

-- Keyset pagination: rows with id > last_seen_id
SELECT * FROM fruits WHERE id > 2 ORDER BY id LIMIT 2;

LIMIT 2 OFFSET 1 reads “two rows, after skipping one.” Internally, PostgreSQL still has to compute all rows up to OFFSET + LIMIT and discard the offset — for page 10,000 of 50 rows per page, that means processing 500,000 rows for a one-based page number. Offset pagination is fine for the first few pages but terrible for deep pagination.

The keyset pattern (also called cursor-based or seek pagination) uses the last seen value as the filter: WHERE id > $last_id ORDER BY id LIMIT 50. The query touches only the next 50 rows regardless of how far you have paginated. It requires an indexed column (often the primary key or created_at, id) but is the standard approach in production systems.

Run the example

To run (requires fruits from earlier lessons):

$ psql -f source/limit-offset.sql postgres
 id |  name
----+--------
  1 | apple
  2 | banana
(2 rows)

 id |  name
----+--------
  2 | banana
  3 | cherry
(2 rows)
 ...

Common pitfalls

• LIMIT 10 without ORDER BY returns some ten rows — and the choice can change between executions. Always sort.
• Two rows with the same sort key may swap places on different pages. Add a tie-breaker (ORDER BY created_at DESC, id DESC) for stable pagination.
• Deep offsets are slow. For page 1,000 of an “infinite scroll” feed, switch to keyset.
• OFFSET is also used in SELECT ... FOR UPDATE patterns and can interact poorly with row locks; if you need to process batches, keyset over a primary key is again the safer pattern.

Downloads

SQL file: limit-offset.sql · All lesson files · Setup guide

8 DISTINCT

DISTINCT removes duplicate rows from the result set. The deduplication considers the entire row of the SELECT — DISTINCT a, b is “distinct combinations of (a, b),” not “distinct a, then any b.” PostgreSQL also offers a more powerful sibling, DISTINCT ON (expr), which keeps one row per distinct value of expr — useful for “the most recent X per Y” queries.

What you’ll learn

• Eliminating duplicates with DISTINCT
• How DISTINCT applies to all columns in the SELECT list
• The PostgreSQL-specific DISTINCT ON (expr)
• How NULL is treated by DISTINCT
• When to use GROUP BY instead

Example

-- Unique values in a column
SELECT DISTINCT name FROM fruits ORDER BY name LIMIT 5;

-- Distinct combinations of multiple columns
SELECT DISTINCT id, name FROM fruits ORDER BY id LIMIT 3;

-- DISTINCT ON: keep one row per distinct expression value.
-- Here: one row per first letter of the name, picking the smallest id.
SELECT DISTINCT ON (substring(name, 1, 1)) substring(name, 1, 1) AS letter, id, name
FROM fruits
ORDER BY substring(name, 1, 1), id;

-- DISTINCT treats NULL as a single value
SELECT DISTINCT n FROM (VALUES (1), (NULL), (NULL), (2)) AS t(n) ORDER BY n;

In our fruits table every name is already unique, so DISTINCT name returns the same rows as SELECT name. With multiple columns, DISTINCT requires all listed values to be the same for two rows to count as duplicates.

DISTINCT ON (substring(name, 1, 1)) is PostgreSQL’s superpower for “first row per group” queries. The ON (...) expressions define the groups; the ORDER BY decides which row from each group is kept (the first one after sorting). The trick is that the ON expressions must appear at the start of the ORDER BY — otherwise PostgreSQL cannot guarantee which row you mean by “the first.”

For NULL handling, DISTINCT considers two NULLs equal — counterintuitive given that NULL = NULL is unknown, but it is what the standard requires.

Run the example

To run:

$ psql -f source/distinct.sql postgres
    name
------------
 apple
 banana
 cherry
 date
 elderberry
(5 rows)
 ...

Common pitfalls

• SELECT DISTINCT a, b FROM t and SELECT DISTINCT a, (b) FROM t mean the same thing: deduplicate (a, b) pairs. Parentheses around b do not make the operation apply only to a.
• DISTINCT and ORDER BY interact: any column in ORDER BY not in the DISTINCT list will cause an error (you cannot order by something that may have multiple values per distinct group).
• DISTINCT ON is non-standard SQL. Code that needs to be portable should use a window function (ROW_NUMBER() OVER (PARTITION BY ...)) instead — covered in a later lesson.
• SELECT DISTINCT on a large table forces a sort or hash; if you only need to know “are there duplicates?” use GROUP BY ... HAVING COUNT(*) > 1 or an aggregate.

Downloads

SQL file: distinct.sql · All lesson files · Setup guide

9 NULLs

NULL is SQL’s marker for unknown or missing. It is not zero, not the empty string, and not equal to anything — not even to another NULL. This three-valued logic (true, false, unknown) is the source of more SQL bugs than any other feature, but the rules are simple once you know them.

What you’ll learn

• What NULL represents
• Testing for NULL with IS NULL / IS NOT NULL
• How NULL propagates through expressions and comparisons
• How NULL behaves in AND/OR (three-valued logic)
• Replacing NULL with COALESCE and NULLIF

Example

-- NULL is missing/unknown
SELECT 1 AS a, NULL AS b;

-- = NULL is NULL (not true), so this returns 0 rows
SELECT * FROM (VALUES (1, NULL)) AS t(id, name) WHERE name = NULL;

-- IS NULL / IS NOT NULL are the correct tests
SELECT * FROM (VALUES (1, NULL)) AS t(id, name) WHERE name IS NULL;
SELECT * FROM (VALUES (1, 'x')) AS t(id, name) WHERE name IS NOT NULL;

-- Any arithmetic with NULL is NULL
SELECT 1 + NULL AS one_plus_null, 'a' || NULL AS concat_null;

-- COALESCE returns the first non-null argument
SELECT COALESCE(NULL, NULL, 'default') AS coalesced;

-- NULLIF returns NULL if the two args are equal, else the first
SELECT NULLIF(0, 0) AS hides_zero, NULLIF(5, 0) AS keeps_five;

-- Three-valued logic
SELECT (TRUE AND NULL) AS t_and_n, (TRUE OR NULL) AS t_or_n, (FALSE AND NULL) AS f_and_n;

= NULL does not work because the comparison evaluates to NULL, which is not TRUE, so the WHERE discards the row. The same applies to <>, <, >, IN, BETWEEN, and so on — almost any operator returns NULL when either operand is NULL. The exceptions are IS NULL, IS NOT NULL, IS DISTINCT FROM, and IS NOT DISTINCT FROM (the last two are NULL-aware versions of <> and =).

COALESCE(a, b, c) returns the first argument that is not NULL. It is the standard way to provide defaults: COALESCE(nickname, first_name, 'Anonymous'). NULLIF(a, b) returns NULL when a = b — handy when you want to turn sentinel values back into NULL (e.g. NULLIF(comment, '')).

The three-valued logic table is short and worth memorizing: TRUE OR NULL is TRUE (we know at least one side is true), but TRUE AND NULL is NULL (we don’t know the other side). FALSE AND NULL is FALSE (one false is enough). FALSE OR NULL is NULL.

Run the example

To run:

$ psql -f source/nulls.sql postgres
 a | b
---+---
 1 |
(1 row)
 ...

Common pitfalls

• Forgetting that aggregates skip NULL: SUM, AVG, MIN, MAX, COUNT(col) ignore NULL inputs. COUNT(*) counts every row including those with NULLs.
• NOT IN (subquery) becomes the empty result if the subquery returns a single NULL — because x <> NULL is NULL. Prefer NOT EXISTS.
• NULL <> NULL is unknown (NULL), not true. DISTINCT groups NULLs together; IS DISTINCT FROM provides a comparison that always returns true or false.
• Default ordering: ORDER BY col ASC puts NULLs last; DESC puts them first. Specify NULLS FIRST/NULLS LAST for predictable behavior.

Downloads

SQL file: nulls.sql · All lesson files · Setup guide

10 Expressions

An expression is anything PostgreSQL can evaluate to a single value: a literal, a column reference, an arithmetic operation, a function call, a CASE, even another SELECT that returns one value. Expressions can appear almost anywhere — in the select list, in WHERE, in ORDER BY, in GROUP BY, in index definitions. Mastering expressions is mastering SQL.

What you’ll learn

• Arithmetic, string, and boolean expressions
• Calling built-in functions in expressions
• Using expressions in SELECT, WHERE, and ORDER BY
• Casting between types with :: and CAST(... AS ...)
• Naming expression results with AS

Example

-- Arithmetic and concatenation
SELECT 10 + 2 AS sum, 10 * 2 AS product, 10 / 4 AS int_div, 10 / 4.0 AS real_div;
SELECT 'Hello ' || 'world' AS greeting;
SELECT 'fruit_' || 1 AS labeled;  -- numbers are coerced to text for ||

-- Function calls in expressions
SELECT upper(name), length(name), name || ' (#' || id || ')' AS label
FROM fruits ORDER BY id LIMIT 3;

-- Expression in WHERE
SELECT * FROM fruits WHERE id * 2 > 4 ORDER BY id LIMIT 3;

-- Expression in ORDER BY
SELECT id, name FROM fruits ORDER BY length(name) DESC LIMIT 3;

-- Casting
SELECT '42'::integer AS to_int, 42::text AS to_text, '2024-01-15'::date AS to_date;
SELECT CAST(3.7 AS integer) AS rounded_value;  -- SQL-standard cast syntax

The || operator concatenates strings. In PostgreSQL, if either operand is not text, it is coerced to text (so 'id_' || 5 works). +, -, *, /, % are the usual arithmetic operators; remember that integer division truncates toward zero.

PostgreSQL casts come in two flavors. value::type is the PostgreSQL-specific shortcut and is the common style. CAST(value AS type) is SQL-standard and reads better when the expression is complex. Both compile to the same thing.

Expressions in WHERE and ORDER BY are evaluated for every candidate row, which means a function call there can prevent index use. WHERE lower(email) = 'a@b.com' cannot use a plain index on email; it can use an expression index defined as CREATE INDEX ON users (lower(email)).

Run the example

To run:

$ psql -f source/expressions.sql postgres
 sum | product | int_div | real_div
-----+---------+---------+----------
  12 |      20 |       2 |      2.5
(1 row)

   greeting
-------------
 Hello world
(1 row)
 ...

Common pitfalls

• Integer division returning 0: 1 / 2 is 0. Cast: 1.0 / 2, 1::numeric / 2, or use numeric columns.
• Casting strings that don’t parse — e.g. 'abc'::integer raises an error. PostgreSQL is strict about types compared to MySQL.
• Expressions returning different types in different branches of CASE cause type-mismatch errors; cast to a common type.
• Operators have precedence; 1 + 2 * 3 is 7. When in doubt, parenthesize.

Downloads

SQL file: expressions.sql · All lesson files · Setup guide

11 Numeric Types

PostgreSQL offers three families of numeric types: integers, exact decimals, and floating-point. Each has its place. Integers are fast and compact for counters and IDs. numeric (also spelled decimal) gives you exact arithmetic — essential for money. Floats (real, double precision) are fast and small but can produce tiny rounding errors and should not be used for currency.

What you’ll learn

• Integer family: smallint, integer, bigint
• Exact decimals: numeric(p, s) and unconstrained numeric
• Floating-point: real, double precision
• How precision and scale work
• When each type is the right choice

Example

-- Integer family
SELECT 1::smallint AS s, 1::integer AS i, 1000000::bigint AS b;

-- Range limits (smallint -32768..32767, integer -2.1B..2.1B, bigint very large)
SELECT pg_typeof(1) AS literal_int_type;  -- 'integer' by default

-- Exact decimal: numeric(precision, scale)
-- precision = total digits, scale = digits after the decimal point
SELECT 3.14::numeric(4, 2) AS pi, 12345.67::numeric(7, 2) AS money;

-- Unconstrained numeric is exact within PostgreSQL's implementation limits
SELECT '0.1'::numeric + '0.2'::numeric AS exact;  -- 0.3, exactly

-- Floats are approximate but fast
SELECT 0.1::double precision + 0.2::double precision AS approximate;  -- 0.30000000000000004

-- Useful generators
SELECT generate_series(1, 5) AS n;

integer is your default integer type and is what 1 parses as. Switch to bigint when you might exceed two billion (rows in a large table, milliseconds since epoch, etc.). smallint is rarely worth it — the storage savings are tiny and values outside its range raise an error.

numeric(p, s) enforces up to p total digits with s of them after the decimal point. numeric(10, 2) stores values from -99,999,999.99 to 99,999,999.99. Unconstrained numeric (no p, s) stores exact decimal values within PostgreSQL’s implementation limits — slower than fixed integers, but exact. Use it for currency, lab measurements, anything where rounding errors are unacceptable.

real (4 bytes, ~6 significant digits) and double precision (8 bytes, ~15 significant digits) are IEEE 754 floats. They are fast and small, but 0.1 + 0.2 is not exactly 0.3. Use them for physical measurements, ML features, statistical aggregations where you can tolerate the imprecision.

Run the example

To run:

$ psql -f source/numeric-types.sql postgres
 s | i |    b
---+---+---------
 1 | 1 | 1000000
(1 row)
 ...
 exact
-------
 0.3
(1 row)

   approximate
------------------
 0.30000000000000004
(1 row)

Common pitfalls

• Storing money in double precision — eventually the cent-rounding catches you. Use numeric(p, 2) (or numeric without bounds) and let the database handle exact arithmetic.
• Choosing integer for an ID column on a large table — you can hit the 2.1-billion limit. Use bigint by default for primary keys you expect to grow.
• Casting strings with non-numeric characters: '$5'::numeric errors. Strip first, then cast.
• Dividing integers and being surprised: 7 / 2 is 3, not 3.5. Cast: 7::numeric / 2.

Downloads

SQL file: numeric-types.sql · All lesson files · Setup guide

12 Text Types

PostgreSQL has three character types: text (unbounded), varchar(n) (variable up to n characters), and char(n) (fixed n characters, space-padded). For almost all use cases, text is the right choice. PostgreSQL’s text and varchar have identical performance characteristics — unlike MySQL, there is no benefit to capping the length unless you want the database to enforce a maximum.

What you’ll learn

• The three text types and when each makes sense
• Why text is the default in PostgreSQL
• Length functions: length, char_length, octet_length
• The basics of collation — how text sorts and compares
• Encoding: PostgreSQL stores text in the database’s encoding (usually UTF-8)

Example

-- text: unbounded, recommended default
SELECT 'hello'::text AS t, length('hello'::text) AS chars;

-- varchar(n): same as text but enforces max length
SELECT 'hi'::varchar(10) AS v, length('hi'::varchar(10)) AS chars;

-- char(n): fixed length, padded with spaces
SELECT 'ab'::char(5) AS c, length('ab'::char(5)) AS chars, '|' || 'ab'::char(5) || '|' AS padded;

-- char_length vs octet_length on multibyte text
SELECT char_length('café') AS chars, octet_length('café') AS bytes;

-- A taste of collation: byte-order sort
SELECT * FROM (VALUES ('apple'), ('Banana'), ('cherry')) AS t(name)
ORDER BY name COLLATE "C";

text is the workhorse. It stores strings of any length and is what most application columns should use. Internally, PostgreSQL stores text and varchar the same way — using TOAST (the Oversized Attribute Storage Technique) for large values so the main table stays compact.

varchar(n) is text with a length check. Use it only when you have a real reason to cap length (e.g. integration with a system that requires it). varchar without a length is exactly text.

char(n) is the historical fixed-width type. It pads values with spaces to length n on storage and trims them in some operations — which leads to surprises. Avoid char(n) unless you have a very specific reason (e.g. interfacing with a system that requires fixed-length codes).

Run the example

To run:

$ psql -f source/text-types.sql postgres
   t   | chars
-------+-------
 hello |     5
(1 row)

 v  | chars
----+-------
 hi |     2
(1 row)

   c   | chars |  padded
-------+-------+----------
 ab    |     2 | |ab|
(1 row)
 ...

Common pitfalls

• Choosing varchar(255) from habit: it does nothing useful in PostgreSQL. Use text. If the application needs a length cap, enforce it in the application or with a CHECK constraint.
• char(n) padding silently changes your values. 'foo'::char(10) is stored as 'foo '.
• Multibyte characters: length('café') returns 4 (characters), but octet_length returns 5 (bytes in UTF-8). Match the function to the question you are asking.
• Sorting comparisons depend on the database’s LC_COLLATE. To get byte-order sorting (often faster, and predictable), use COLLATE "C".

Downloads

SQL file: text-types.sql · All lesson files · Setup guide

13 Boolean and Dates

PostgreSQL’s boolean type stores true, false, or NULL. For dates and times you have a small family of types: date for a calendar day, time for a time of day, timestamp for date-and-time without a time zone, and timestamptz (timestamp with time zone) for an instant displayed in the session time zone (the original zone/offset is not retained). For almost all application use, timestamptz is what you want.

What you’ll learn

• The boolean type and how it accepts many inputs (true, 't', 'yes', 1)
• The four core date/time types and when each makes sense
• Why timestamptz is preferred over timestamp
• current_date, current_timestamp, now(), localtime
• Date arithmetic with interval

Example

-- Boolean: true, false, NULL
SELECT true AS t, false AS f, (1 = 1) AS same;

-- Boolean accepts many input forms
SELECT 'yes'::boolean AS yes, 'n'::boolean AS n, '1'::boolean AS one;

-- Dates and times
SELECT current_date AS today,
       current_timestamp AS now_tz,
       localtimestamp AS now_local,
       current_time AS time_now;

-- Casting date/time literals
SELECT '2024-01-15'::date AS d,
       '2024-01-15 10:30:00'::timestamp AS ts,
       '2024-01-15 10:30:00+00'::timestamptz AS ts_tz;

-- Arithmetic with interval
SELECT current_date + 7 AS in_a_week,
       current_timestamp - interval '1 hour' AS an_hour_ago,
       date '2024-12-31' - date '2024-01-01' AS days_between;

-- Extracting parts
SELECT extract(year FROM current_date) AS year,
       extract(dow  FROM current_date) AS day_of_week;

The boolean type accepts a generous set of input strings: true, false, t, f, yes, no, y, n, 1, 0, on, off. The output is always shown as t or f in psql. Comparisons (=, <>, etc.) produce booleans; you can store the result of a comparison in a boolean column.

timestamp without time zone (just timestamp) stores a wall-clock value with no notion of where on Earth it was. If you insert 2024-01-15 10:00:00, that is exactly what you get back, regardless of who reads it. timestamp with time zone (timestamptz) stores the same instant in UTC internally and presents it in the session’s time zone. Two sessions in different time zones will see the same timestamptz as different wall-clock times, but the underlying instant is the same.

For nearly every “when did this event happen?” question — order placed, log written, user created — timestamptz is the right answer. Plain timestamp is for things like “the recipe says bake at 10am local time, in whatever local time the user is in” — rare.

Run the example

To run:

$ psql -f source/boolean-and-dates.sql postgres
 t | f | same
---+---+------
 t | f | t
(1 row)
 ...
   today
------------
 2024-xx-xx
(1 row)
 ...

Common pitfalls

• Storing event times as timestamp instead of timestamptz: a year later, you cannot tell whether they were UTC, server-local, or user-local. Always timestamptz.
• Comparing a date or timestamp without time zone to a timestamptz uses the session time zone to interpret the value without a zone. Be explicit when it matters.
• now() returns the time of the transaction start, not the wall-clock now. Inside a long transaction, repeated calls return the same value. Use clock_timestamp() for true wall-clock time.
• interval '1 month' plus a date gives a timestamp in the next month — sometimes with surprises (January 31 + 1 month = February 28/29).

Downloads

SQL file: boolean-and-dates.sql · All lesson files · Setup guide

14 UUID and JSONB

Two of PostgreSQL’s most popular non-trivial types: uuid for opaque, globally unique identifiers, and jsonb for binary-encoded JSON that can be indexed and queried. UUIDs are the standard alternative to sequential integer IDs when you need IDs that are unguessable, generatable on the client, or globally unique across systems. jsonb is what makes PostgreSQL competitive with document stores for semi-structured data.

What you’ll learn

• Generating UUIDs with gen_random_uuid()
• Storing and querying JSON with jsonb
• ->, ->>, #>, #>> operators and what they return
• The @> containment operator (and how it uses GIN indexes)
• The difference between json and jsonb

Example

-- UUID generation (built-in from PostgreSQL 13)
SELECT gen_random_uuid() AS id;

-- jsonb literal
SELECT '{"name": "Alice", "age": 30, "tags": ["admin", "beta"]}'::jsonb AS j;

-- -> returns jsonb (preserves type); ->> returns text
SELECT '{"name": "Alice"}'::jsonb -> 'name'  AS as_jsonb,
       '{"name": "Alice"}'::jsonb ->> 'name' AS as_text;

-- Array indexing (0-based)
SELECT '["a", "b", "c"]'::jsonb -> 1 AS second;

-- Path operators: #> returns jsonb, #>> returns text
SELECT '{"user": {"address": {"city": "Paris"}}}'::jsonb #>> '{user, address, city}' AS city;

-- Containment: @> checks if the right side is contained in the left
SELECT '{"name": "Alice", "age": 30}'::jsonb @> '{"name": "Alice"}'::jsonb AS contains_alice;

-- Querying jsonb columns in a real table
DROP TABLE IF EXISTS docs_example;
CREATE TABLE docs_example (id integer PRIMARY KEY, data jsonb);
INSERT INTO docs_example VALUES
  (1, '{"name": "Alice", "tags": ["admin"]}'),
  (2, '{"name": "Bob",   "tags": ["beta"]}');

SELECT id, data ->> 'name' AS name
FROM docs_example
WHERE data @> '{"tags": ["admin"]}';

gen_random_uuid() produces a random version-4 UUID. It is built in from PostgreSQL 13 onward — no pgcrypto extension needed. PostgreSQL 17 has no built-in UUIDv7 generator; use an application/library or extension if you need time-ordered UUIDs. PostgreSQL 18 adds uuidv7(). A bigint identity column is another option.

For JSON, prefer jsonb over json. json stores the text exactly as you give it (including whitespace and key order); jsonb parses, normalizes, and stores in a binary form. jsonb is what you index, what you query efficiently, and what almost every application uses. Reach for plain json only when you need to preserve the input bytes verbatim (rare).

The -> operator extracts and returns jsonb. ->> extracts and returns text (stripping JSON quoting). #> and #>> take a path (an array of keys/indexes). @> (“contains”) tests whether the left value contains the right — it is the foundation of JSON queries.

Run the example

To run:

$ psql -f source/uuid-jsonb.sql postgres
                  id
--------------------------------------
 xxxxxxxx-xxxx-4xxx-xxxx-xxxxxxxxxxxx
(1 row)
 ...
 contains_alice
----------------
 t
(1 row)
 ...

Common pitfalls

• Comparing data -> 'name' to a string: data -> 'name' = '"Alice"' works (note the JSON-quoted string), but data ->> 'name' = 'Alice' is what you usually want.
• Forgetting an index: filtering on a jsonb column without a GIN index does a full table scan. CREATE INDEX ON docs_example USING gin (data); enables @> and similar queries to use the index.
• Mutating jsonb in place: UPDATE t SET data = data || '{"k": "v"}'::jsonb adds or replaces a top-level key; jsonb_set lets you change nested paths.
• Schema drift inside jsonb: it is flexible, but you still need to know what is in there. Document the expected shape, and validate at the application layer or with a CHECK constraint.

Downloads

SQL file: uuid-jsonb.sql · All lesson files · Setup guide

15 Arrays and ENUM

Two more PostgreSQL-specific types worth knowing early. Arrays let any column hold an ordered list of values of the same type — sometimes the right tool when a one-to-many relationship is so tightly scoped that a separate table is overkill (tags on a post, role names on a user). ENUM types give a column a closed set of named values, enforced by the database, and stored compactly. Both are non-standard but well-supported, and used widely in real-world Postgres apps.

What you’ll learn

• Declaring and querying array columns
• Array literals, indexing (1-based), and useful functions
• The ANY / ALL operators on arrays
• Defining and using ENUM types
• When arrays/enums fit, and when a separate table is better

Example

-- Arrays: any type can be turned into an array by appending []
DROP TABLE IF EXISTS posts_example;
CREATE TABLE posts_example (
  id   integer PRIMARY KEY,
  title text,
  tags  text[]                  -- array of text
);

INSERT INTO posts_example VALUES
  (1, 'Hello', ARRAY['intro', 'meta']),
  (2, 'SQL tips', '{tips, postgres}'),   -- shorthand literal
  (3, 'Untagged', ARRAY[]::text[]);

-- Indexing (1-based!) and length
SELECT id, title, tags[1] AS first_tag, array_length(tags, 1) AS tag_count
FROM posts_example
ORDER BY id;

-- ANY: row matches if ANY element of the array equals 'tips'
SELECT id, title FROM posts_example WHERE 'tips' = ANY(tags);

-- Array containment: @> "left contains all elements of right"
SELECT id, title FROM posts_example WHERE tags @> ARRAY['postgres'];

-- ENUM: defined type with a fixed set of allowed values
DROP TABLE IF EXISTS tickets_example;
DROP TYPE  IF EXISTS ticket_status;
CREATE TYPE ticket_status AS ENUM ('open', 'in_progress', 'closed');

CREATE TABLE tickets_example (
  id     integer PRIMARY KEY,
  title  text,
  status ticket_status NOT NULL DEFAULT 'open'
);
INSERT INTO tickets_example (id, title, status) VALUES (1, 'Bug 1', 'open'), (2, 'Bug 2', 'closed');

SELECT * FROM tickets_example ORDER BY id;

Arrays in PostgreSQL are first-class: you can index them, use them in WHERE clauses, build them with ARRAY[], expand them to rows with unnest(), and query for containment with @>, <@, && (overlap). They are stored compactly inline (or in TOAST for very large ones).

The most important quirk: array indexes default to 1-based. tags[1] is the first element of the arrays here, though PostgreSQL also supports explicitly chosen lower bounds. The literal form '{a, b, c}' is a shorthand string-style literal; ARRAY['a', 'b', 'c'] is the constructor — both produce the same value.

ENUM types are excellent when you have a known small set of allowed values that should be enforced by the database ('open' | 'in_progress' | 'closed', 'small' | 'medium' | 'large'). They store as 4 bytes regardless of label length, and sort by declaration order rather than alphabetically — useful for status workflows where 'open' < 'in_progress' < 'closed' is the natural order.

Run the example

To run:

$ psql -f source/arrays-and-enum.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 3
 id |  title   | first_tag | tag_count
----+----------+-----------+-----------
  1 | Hello    | intro     |         2
  2 | SQL tips | tips      |         2
  3 | Untagged |           |
(3 rows)
 ...

Common pitfalls

• 1-based indexing trips up developers from every other language. tags[0] is NULL, not an error.
• Modifying an ENUM (adding a value) requires ALTER TYPE; removing a value is essentially impossible without recreating the type. For sets that change often, prefer a small lookup table joined by foreign key.
• Arrays look enticing for “I just need a list” — but if you ever need to query for items, join, sort, or constrain individually, a separate table with foreign keys is almost always cleaner. Use arrays when the elements are part of the row, not entities of their own.
• tag = 'x' does not work on an array column — use 'x' = ANY(tag) or tag @> ARRAY['x'].

Downloads

SQL file: arrays-and-enum.sql · All lesson files · Setup guide

16 CREATE TABLE

Tables are how PostgreSQL stores your data. CREATE TABLE defines a new table by listing its columns and their data types. Defaults, constraints, indexes, and foreign keys can all be added at create time, but a minimal table needs only a name and a column list. This lesson keeps things small; constraints get their own lessons.

What you’ll learn

• Defining a table with CREATE TABLE
• Picking a data type for each column (integer, text, etc.)
• Making the create script idempotent with DROP TABLE IF EXISTS
• Inspecting tables in the public schema
• The difference between TEMP TABLE, regular tables, and UNLOGGED TABLE

Example

-- Drop the table if it exists so the script is idempotent
DROP TABLE IF EXISTS fruits;

-- A minimal table: id and name
CREATE TABLE fruits (
  id   integer,
  name text
);

-- Confirm it exists in the current schema
SELECT schemaname, tablename
FROM pg_tables
WHERE tablename = 'fruits';

-- See the columns and their types
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'fruits'
ORDER BY ordinal_position;

fruits has two columns: id (a 32-bit signed integer) and name (variable-length text). The order of columns in CREATE TABLE is the physical order rows are stored in — for storage efficiency on wide tables, group fixed-width columns together. For small tables it doesn’t matter.

DROP TABLE IF EXISTS removes the table only if it exists; without IF EXISTS, the statement would error on a fresh database. Combined with CREATE TABLE, you get a script you can rerun safely while you iterate on the schema. In production, of course, do not blindly drop tables — use migrations.

There are several variants of CREATE TABLE worth knowing about:

• CREATE TEMP TABLE (or CREATE TEMPORARY TABLE) creates a table that exists only for the current session and is dropped automatically.
• CREATE UNLOGGED TABLE skips the write-ahead log — faster writes, but the contents are wiped if the server crashes. Useful for derived/staging data you can rebuild.
• CREATE TABLE ... (LIKE other_table INCLUDING ALL) clones the schema of another table.
• CREATE TABLE ... AS SELECT ... creates and populates a table from a query (called a CTAS).

Run the example

To run:

$ psql -f source/create-table.sql postgres
DROP TABLE
CREATE TABLE
 schemaname | tablename
------------+-----------
 public     | fruits
(1 row)

 column_name | data_type | is_nullable
-------------+-----------+-------------
 id          | integer   | YES
 name        | text      | YES
(2 rows)

Common pitfalls

• Picking varchar(255) because that’s what you learned in MySQL: in PostgreSQL, text and varchar(n) have the same performance. Use text unless you specifically want a length cap.
• Forgetting NOT NULL on columns that should never be null — you can add it later, but it is easier to set up correctly from the start.
• Naming a column user, order, select — these are reserved words. Either rename (user_id, placed_at) or quote ("user"), but the second is friction forever.

Downloads

SQL file: create-table.sql · All lesson files · Setup guide

17 Column Types and Constraints

A real table picks a type for every column and often adds constraints — rules the database enforces on every row. The most common constraints are NOT NULL (the column must have a value), DEFAULT (use this value if none is given), CHECK (a boolean expression that must be true), UNIQUE, PRIMARY KEY, and REFERENCES (foreign key). Each gets its own lesson; here we combine several into one realistic table.

What you’ll learn

• Combining multiple types and constraints in one CREATE TABLE
• Inline DEFAULT and NOT NULL
• CHECK constraints for domain rules
• Using timestamptz with current_timestamp as a default
• Inspecting constraints in the catalog

Example

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  id          integer PRIMARY KEY,
  name        text NOT NULL,
  price       numeric(10, 2) NOT NULL CHECK (price >= 0),
  in_stock    boolean NOT NULL DEFAULT true,
  category    text DEFAULT 'misc',
  created_at  timestamptz NOT NULL DEFAULT current_timestamp,
  CONSTRAINT name_not_blank CHECK (length(trim(name)) > 0)
);

INSERT INTO products (id, name, price)              VALUES (1, 'Widget',    19.99);
INSERT INTO products (id, name, price, in_stock)    VALUES (2, 'Gadget',    49.99, false);
INSERT INTO products (id, name, price, category)    VALUES (3, 'Sprocket',  4.95, 'parts');

SELECT * FROM products ORDER BY id;

-- Inspect the constraints on the table
SELECT conname, pg_get_constraintdef(oid) AS def
FROM pg_constraint
WHERE conrelid = 'products'::regclass
ORDER BY conname;

Each constraint is one promise the database makes about your data. NOT NULL says the value is always present. DEFAULT supplies a value when an INSERT omits the column. CHECK (expr) rejects any row where expr is false (NULLs pass). PRIMARY KEY implies NOT NULL and UNIQUE, and creates an index.

Constraints can be inline (after the column type, as above) or table-level (after all the columns). Table-level syntax is required for composite PRIMARY KEY, UNIQUE, and foreign-key constraints. A CHECK can reference several columns in either position, and both column and table constraints can be named with CONSTRAINT name for clearer errors.

timestamptz NOT NULL DEFAULT current_timestamp is one of the most common column patterns — the row’s creation time, stored in UTC, never null, set automatically.

Run the example

To run:

$ psql -f source/column-types-and-constraints.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
 id |   name   | price | in_stock | category |          created_at
----+----------+-------+----------+----------+-------------------------------
  1 | Widget   | 19.99 | t        | misc     | 2024-xx-xx xx:xx:xx.xxxxxx+00
  2 | Gadget   | 49.99 | f        | misc     | 2024-xx-xx xx:xx:xx.xxxxxx+00
  3 | Sprocket |  4.95 | t        | parts    | 2024-xx-xx xx:xx:xx.xxxxxx+00
(3 rows)
 ...

Common pitfalls

• Forgetting NOT NULL on columns that should never be null — adding it later requires backfilling and may take a long lock on a large table.
• CHECK accepts a row when the expression is NULL. To reject NULLs, combine NOT NULL and CHECK.
• Using now() as a DEFAULT: it works, but inside a long transaction every default is the transaction-start time. Usually that is fine; for true per-row wall-clock use clock_timestamp().
• Adding a DEFAULT and NOT NULL to a new column on a huge table used to rewrite the entire table; modern PostgreSQL (11+) avoids that rewrite when the default is a constant, but volatile defaults still rewrite.

Downloads

SQL file: column-types-and-constraints.sql · All lesson files · Setup guide

18 ALTER TABLE and DROP

Tables evolve. ALTER TABLE changes an existing table: add or drop a column, rename a column or the table itself, add or drop a constraint, change a column’s type, change its default. DROP TABLE removes the table and every row it contains. Schema changes in PostgreSQL are transactional — a failed ALTER TABLE rolls back cleanly, and you can wrap several ALTERs in a BEGIN ... COMMIT to apply them atomically.

What you’ll learn

• Adding, renaming, and dropping columns
• Adding and dropping constraints
• Changing a column’s type with USING
• Renaming a table
• The locking and rewrite implications of common alterations

Example

-- ADD COLUMN: new columns appear at the end
ALTER TABLE products ADD COLUMN description text;

-- ADD COLUMN with a default (cheap in PG 11+ for constant defaults)
ALTER TABLE products ADD COLUMN sku text NOT NULL DEFAULT '';

-- Rename a column
ALTER TABLE products RENAME COLUMN description TO short_desc;

-- Change a column type (use USING when a cast is needed)
ALTER TABLE products ALTER COLUMN sku TYPE varchar(40);

-- Add a constraint after the fact
ALTER TABLE products ADD CONSTRAINT sku_format CHECK (sku ~ '^[A-Z0-9-]*$');

-- Drop a column
ALTER TABLE products DROP COLUMN short_desc;

-- Rename the table itself
ALTER TABLE products RENAME TO products_old;
ALTER TABLE products_old RENAME TO products;

-- DROP TABLE removes the table and all data
-- (commented out so the rest of the lessons can still use products)
-- DROP TABLE products;

SELECT tablename FROM pg_tables WHERE tablename = 'products';

Each ALTER TABLE is a single transactional statement. You can also batch multiple operations: ALTER TABLE t ADD COLUMN a int, ADD COLUMN b text, DROP COLUMN c; — running them in one statement is faster because the table is touched once.

Two operations are worth understanding in terms of cost:

• Adding a column with no default (or a constant default) is fast — PostgreSQL just records the new column metadata.
• Many column type changes rewrite the table. ALTER COLUMN x TYPE bigint reads and rewrites every row. On a large table this can take hours and holds an exclusive lock the whole time. Some binary-compatible changes avoid a rewrite. For a rewrite-heavy change, plan a migration such as new column → backfill → switch; pg_repack does not perform arbitrary type conversions.

ALTER TABLE ... ALTER COLUMN col TYPE new_type USING expr lets you specify how to convert the existing value: ALTER COLUMN price TYPE numeric(12,2) USING price::numeric(12,2).

Run the example

To run:

$ psql -f source/alter-table-and-drop.sql postgres
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
 tablename
-----------
 products
(1 row)

Common pitfalls

• Adding NOT NULL to an existing column requires that the column already has no NULLs and acquires an ACCESS EXCLUSIVE lock long enough to verify. Backfill first, then SET NOT NULL. From PostgreSQL 12, you can ALTER COLUMN x SET NOT NULL faster if a matching CHECK constraint exists.
• Dropping a column does not reclaim disk space immediately — the column is marked dead. Run VACUUM FULL (heavyweight) or recreate the table to reclaim it.
• Renaming a column updates catalog-tracked dependencies such as views, but can break application SQL and function bodies stored as text. Review those uses before renaming.
• ALTER TYPE on an ENUM cannot remove values; adding values is fine.

Downloads

SQL file: alter-table-and-drop.sql · All lesson files · Setup guide

19 Primary Keys and UNIQUE

A primary key uniquely identifies a row. Every table should have one — it gives you a stable handle into the row from indexes, joins, foreign keys, and application code. A table can have at most one primary key (which may span multiple columns). UNIQUE is the more general “no duplicates here” constraint; you can have several UNIQUEs per table, and they may include nullable columns.

What you’ll learn

• The difference between PRIMARY KEY and UNIQUE
• Single-column vs composite (multi-column) primary keys
• Surrogate keys (a generated id) vs natural keys (e.g. an email)
• How NULL is treated by UNIQUE (each NULL is distinct, unless NULLS NOT DISTINCT)
• The index that a PK / UNIQUE automatically creates

Example

DROP TABLE IF EXISTS users_example;

-- Inline primary key + unique
CREATE TABLE users_example (
  id    integer PRIMARY KEY,
  email text UNIQUE NOT NULL,
  name  text NOT NULL
);

INSERT INTO users_example (id, email, name) VALUES
  (1, 'alice@example.com', 'Alice'),
  (2, 'bob@example.com',   'Bob');

SELECT * FROM users_example ORDER BY id;

-- Composite primary key (across two columns)
DROP TABLE IF EXISTS enrollments_example;
CREATE TABLE enrollments_example (
  student_id integer,
  course_id  integer,
  enrolled_at timestamptz NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (student_id, course_id)
);

INSERT INTO enrollments_example (student_id, course_id) VALUES (1, 100), (1, 200), (2, 100);
SELECT * FROM enrollments_example ORDER BY student_id, course_id;

-- See indexes that the constraints created
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename IN ('users_example', 'enrollments_example')
ORDER BY indexname;

A primary key combines NOT NULL and UNIQUE and creates a B-tree index. PostgreSQL uses the primary key index for joins, for WHERE id = ? lookups, and as the natural row identifier in foreign keys. Every well-designed table has one.

UNIQUE is the more flexible cousin. You can have several uniques per table — UNIQUE (email), UNIQUE (handle). A UNIQUE constraint allows NULL values; under the standard, NULL is distinct from every other NULL, so multiple rows with NULL in the same unique column are allowed. From PostgreSQL 15, you can override this with UNIQUE NULLS NOT DISTINCT if you want NULL to count as a duplicate.

Composite keys (PRIMARY KEY (a, b)) are common for junction tables representing many-to-many relationships (a student is enrolled in many courses; a course has many students; the pair is unique).

Run the example

To run:

$ psql -f source/primary-keys-and-unique.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 2
 id |       email       | name
----+-------------------+-------
  1 | alice@example.com | Alice
  2 | bob@example.com   | Bob
(2 rows)
 ...

Common pitfalls

• A UNIQUE column with NULLs allowed: two NULLs are not a duplicate by default. If your business rule says “the column is optional but if set, must be unique,” that is the default — but if your rule says “treat NULL as equal,” use UNIQUE NULLS NOT DISTINCT (PG 15+). A partial unique index with WHERE col IS NOT NULL still allows multiple NULLs and is not a substitute.
• Choosing a natural key like email as the primary key seems clean — until the user changes their email. Surrogate keys (id + UNIQUE (email)) are usually safer.
• Composite primary keys propagate into foreign keys; a child table that references a composite PK must duplicate both columns. Often a simpler design is to give the parent a surrogate id.
• PRIMARY KEY and UNIQUE each create an index automatically; do not create another B-tree index on the same column.

Downloads

SQL file: primary-keys-and-unique.sql · All lesson files · Setup guide

20 Foreign Keys and REFERENCES

A foreign key is a constraint that says “the value in this column must already exist as a primary key (or unique key) in some other table.” It is how relational databases enforce referential integrity — you cannot orphan a child row by pointing it at a non-existent parent, and (depending on the rules you choose) the database can cascade or restrict updates to the parent. Every relational model that involves multiple tables needs foreign keys.

What you’ll learn

• Declaring a foreign key with REFERENCES
• The ON DELETE and ON UPDATE actions: NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT
• Why an index on the FK column is almost always a good idea
• How DEFERRABLE constraints let you batch changes inside a transaction
• Inspecting foreign keys in the catalog

Example

-- Parent table
DROP TABLE IF EXISTS comments_example;
DROP TABLE IF EXISTS authors_example;

CREATE TABLE authors_example (
  id   integer PRIMARY KEY,
  name text NOT NULL
);

-- Child table referencing parent
CREATE TABLE comments_example (
  id        integer PRIMARY KEY,
  author_id integer NOT NULL REFERENCES authors_example(id) ON DELETE CASCADE,
  body      text NOT NULL,
  parent_id integer REFERENCES comments_example(id) ON DELETE SET NULL  -- self-reference!
);

-- Index the FK column so joins and cascading deletes don't seq-scan
CREATE INDEX ON comments_example (author_id);
CREATE INDEX ON comments_example (parent_id);

INSERT INTO authors_example VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO comments_example (id, author_id, body) VALUES
  (1, 1, 'First!'),
  (2, 2, 'Reply'),
  (3, 1, 'Nice');
UPDATE comments_example SET parent_id = 1 WHERE id = 2;

-- Refused: author 99 does not exist
-- INSERT INTO comments_example (id, author_id, body) VALUES (4, 99, 'orphan');

-- Cascade: deleting Alice removes her comments (and Bob's reply loses its parent_id)
DELETE FROM authors_example WHERE id = 1;
SELECT * FROM comments_example ORDER BY id;

-- Inspect foreign keys
SELECT conname, pg_get_constraintdef(oid) AS def
FROM pg_constraint
WHERE contype = 'f' AND conrelid = 'comments_example'::regclass;

REFERENCES other_table(column) declares the foreign key. The referenced column must be the primary key or a unique constraint of the other table. If you omit the column name, the referenced primary key is assumed: REFERENCES authors_example is equivalent to REFERENCES authors_example(id) here.

The ON DELETE and ON UPDATE actions decide what happens when the parent row’s key changes:

• NO ACTION (default): error if any child still references the parent. Checked at the end of the statement.
• RESTRICT: like NO ACTION but checked immediately (cannot be deferred).
• CASCADE: delete or update the child rows too. Useful when the child rows are owned by the parent.
• SET NULL: set the child column to NULL (the column must be nullable).
• SET DEFAULT: set the child column to its default.

Foreign keys are checked at every INSERT/UPDATE/DELETE — they are not free. They are also one of the most important correctness features in your schema; the cost almost always pays for itself in avoided data corruption.

Run the example

To run:

$ psql -f source/foreign-keys.sql postgres
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
INSERT 0 2
INSERT 0 3
UPDATE 1
DELETE 1
 id | author_id | body  | parent_id
----+-----------+-------+-----------
  2 |         2 | Reply |
(1 row)
 ...

Common pitfalls

• Forgetting an index on the FK column: when you delete a parent row, PostgreSQL must check (and possibly cascade to) child rows; without an index on the FK column this is a sequential scan. Always index the FK column.
• Choosing CASCADE too eagerly: a deletion in production can cascade across millions of rows. Audit your ON DELETE CASCADEs, especially on user-facing tables.
• Importing data with cross-table dependencies: load the parent first, then the child. Or use DEFERRABLE INITIALLY DEFERRED so the FK check happens at commit instead of per-row.
• Cycles between FKs require deferrable constraints. PostgreSQL supports them, but design around the need when possible.

Downloads

SQL file: foreign-keys.sql · All lesson files · Setup guide

21 NOT NULL and DEFAULT

NOT NULL and DEFAULT are the two most useful per-column rules. NOT NULL says the column always has a value — every INSERT and UPDATE must provide one (or rely on a default). DEFAULT expr provides the value when the column is omitted. Together, they make schemas easier to reason about: you never have to write IF column IS NULL in application code if the column is NOT NULL.

What you’ll learn

• Enforcing required values with NOT NULL
• Supplying defaults with DEFAULT expr
• Volatile vs constant defaults and what that means at scale
• Combining NOT NULL and DEFAULT for “always has a value”
• Removing or changing defaults later

Example

DROP TABLE IF EXISTS tasks_example;

CREATE TABLE tasks_example (
  id          integer PRIMARY KEY,
  title       text NOT NULL,
  done        boolean NOT NULL DEFAULT false,
  priority    integer NOT NULL DEFAULT 3 CHECK (priority BETWEEN 1 AND 5),
  due_at      timestamptz,                              -- nullable
  created_at  timestamptz NOT NULL DEFAULT current_timestamp
);

-- Defaults fill in done, priority, created_at
INSERT INTO tasks_example (id, title) VALUES (1, 'Learn SQL');

-- Explicit values override the defaults
INSERT INTO tasks_example (id, title, done, priority, due_at)
VALUES (2, 'Ship lesson', true, 1, current_timestamp + interval '1 day');

SELECT * FROM tasks_example ORDER BY id;

-- Remove a default later (or change it)
ALTER TABLE tasks_example ALTER COLUMN priority SET DEFAULT 1;
ALTER TABLE tasks_example ALTER COLUMN priority DROP DEFAULT;
ALTER TABLE tasks_example ALTER COLUMN priority SET DEFAULT 3;

-- Adding NOT NULL to a column requires no NULLs to exist
-- ALTER TABLE tasks_example ALTER COLUMN due_at SET NOT NULL;  -- would fail if any due_at is NULL

NOT NULL is one of the cheapest, highest-impact constraints you can add — it eliminates an entire class of bugs (forgotten null checks, broken aggregates, surprising joins). Mark columns NOT NULL whenever the application logic guarantees a value.

DEFAULT is evaluated at insert time, not at table creation time. DEFAULT current_timestamp runs current_timestamp once per inserted row, returning the transaction start time. DEFAULT gen_random_uuid() generates a fresh UUID for each row.

Defaults that are constants (DEFAULT 0, DEFAULT 'pending') are fast to add to existing tables — PostgreSQL 11+ stores the constant default in metadata without rewriting the table. Nonvolatile defaults, including the STABLE expression current_timestamp, can also use the fast path. Volatile defaults such as random() and clock_timestamp() require a value for each existing row and rewrite the table.

Run the example

To run:

$ psql -f source/not-null-and-default.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
 id |    title    | done | priority | due_at | created_at
----+-------------+------+----------+--------+-------------
  1 | Learn SQL   | f    |        3 |        | 2024-xx-xx
  2 | Ship lesson | t    |        1 | 2024-xx-xx | 2024-xx-xx
(2 rows)
 ...

Common pitfalls

• Treating '' (empty string) as “missing” — pick one and be consistent. Most teams use NULL for “no value” and reserve '' for “intentionally blank.” Mixing the two leads to half-broken queries forever.
• Defaults are not automatically reapplied on UPDATE. To “reset to default,” use UPDATE t SET col = DEFAULT WHERE ....
• Adding NOT NULL to an existing column that may have NULLs fails. Backfill the column first, then SET NOT NULL.
• INSERT INTO t DEFAULT VALUES is your friend when every column has a default — it lets the database fill in everything.

Downloads

SQL file: not-null-and-default.sql · All lesson files · Setup guide

22 Identity Columns and Sequences

You rarely want to hand-pick primary key values. PostgreSQL gives you two ways to generate them automatically: the modern, SQL-standard GENERATED ... AS IDENTITY syntax, and the older serial / bigserial shortcuts. Both are powered by a sequence — an independent object that hands out incrementing numbers. IDENTITY is the recommended choice for new code; serial is still common in older codebases.

What you’ll learn

• The GENERATED ALWAYS AS IDENTITY syntax
• The differences from the older serial shortcut
• What a sequence is and how to inspect it
• Why generated IDs can have gaps
• Resetting a sequence after a bulk import

Example

-- Modern style: GENERATED AS IDENTITY (recommended)
DROP TABLE IF EXISTS messages_example;
CREATE TABLE messages_example (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  body text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT current_timestamp
);

INSERT INTO messages_example (body) VALUES ('first'), ('second'), ('third');
SELECT id, body FROM messages_example ORDER BY id;

-- Legacy serial shortcut (rewrites to a sequence + DEFAULT under the hood)
DROP TABLE IF EXISTS legacy_example;
CREATE TABLE legacy_example (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

INSERT INTO legacy_example (name) VALUES ('a'), ('b');
SELECT id, name FROM legacy_example ORDER BY id;

-- Inspect sequences
SELECT sequencename, last_value, increment_by
FROM pg_sequences
WHERE sequencename IN ('messages_example_id_seq', 'legacy_example_id_seq');

-- Manually grab the next value (and consume it)
SELECT nextval('messages_example_id_seq') AS next_id;
SELECT currval('messages_example_id_seq') AS curr_id;

-- Reset a sequence (e.g. after a bulk import that supplied IDs)
SELECT setval('legacy_example_id_seq',
              (SELECT max(id) FROM legacy_example));