Skip to content

Postgres by Example

A hands-on introduction to PostgreSQL through short, runnable lessons.

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.

Two special cases

Chapter 46 deliberately raises a duplicate-key error: run it with ON_ERROR_STOP=off so the subsequent rollback and cleanup execute. For chapter 55, use a non-superuser database owner without BYPASSRLS; superusers bypass row security even with FORCE ROW LEVEL SECURITY.

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

Adapted from Postgres by Example, © Dariush Abbasi, under CC BY 4.0. See attribution.