# Postgres by Example

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

---

LLMS index: [llms.txt](/en/llms.txt)

---

**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](/en/01-first-query/#example) · [Print the whole book](/en/_print/) · [Source repository](https://github.com/boringcollege/postgres-by-example)

## Prerequisites {#prerequisites}

The lessons target **[PostgreSQL](https://www.postgresql.org/) 17** and use the `psql` command-line client.
Later-version features are identified explicitly. The
[official documentation](https://www.postgresql.org/docs/) is a useful companion.

Download the [complete SQL bundle](/examples/examples.zip) 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`:

```sh
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:

```sh
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.

> [!NOTE] 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 {#contents}

- [1 First Query](/en/01-first-query/)
- [2 psql Basics](/en/02-psql-basics/)
- [3 SELECT Basics](/en/03-select-basics/)
- [4 WHERE](/en/04-where/)
- [5 ORDER BY](/en/05-order-by/)
- [6 SELECT from a Table](/en/06-select-from-table/)
- [7 LIMIT and OFFSET](/en/07-limit-offset/)
- [8 DISTINCT](/en/08-distinct/)
- [9 NULLs](/en/09-nulls/)
- [10 Expressions](/en/10-expressions/)
- [11 Numeric Types](/en/11-numeric-types/)
- [12 Text Types](/en/12-text-types/)
- [13 Boolean and Dates](/en/13-boolean-and-dates/)
- [14 UUID and JSONB](/en/14-uuid-jsonb/)
- [15 Arrays and ENUM](/en/15-arrays-and-enum/)
- [16 CREATE TABLE](/en/16-create-table/)
- [17 Column Types and Constraints](/en/17-column-types-and-constraints/)
- [18 ALTER TABLE and DROP](/en/18-alter-table-and-drop/)
- [19 Primary Keys and UNIQUE](/en/19-primary-keys-and-unique/)
- [20 Foreign Keys and REFERENCES](/en/20-foreign-keys/)
- [21 NOT NULL and DEFAULT](/en/21-not-null-and-default/)
- [22 Identity Columns and Sequences](/en/22-identity-and-sequences/)
- [23 Schemas and search_path](/en/23-schemas/)
- [24 INSERT](/en/24-insert/)
- [25 UPSERT (ON CONFLICT)](/en/25-upsert/)
- [26 UPDATE](/en/26-update/)
- [27 DELETE](/en/27-delete/)
- [28 RETURNING](/en/28-returning/)
- [29 INNER and LEFT JOIN](/en/29-inner-and-left-join/)
- [30 RIGHT and FULL JOIN](/en/30-right-and-full-join/)
- [31 Self-Join](/en/31-self-join/)
- [32 UNION, INTERSECT, EXCEPT](/en/32-union-intersect-except/)
- [33 COUNT, SUM, AVG](/en/33-count-sum-avg/)
- [34 GROUP BY](/en/34-group-by/)
- [35 HAVING](/en/35-having/)
- [36 Scalar and IN Subqueries](/en/36-scalar-and-in-subqueries/)
- [37 EXISTS and Derived Tables](/en/37-exists-and-derived-tables/)
- [38 Common Table Expressions (WITH)](/en/38-cte/)
- [39 Window Functions](/en/39-window-functions/)
- [40 String and Numeric Functions](/en/40-string-and-numeric-functions/)
- [41 Date Functions and COALESCE](/en/41-date-functions-and-coalesce/)
- [42 CASE](/en/42-case/)
- [43 CREATE INDEX](/en/43-create-index/)
- [44 When to Index](/en/44-when-to-index/)
- [45 EXPLAIN and Query Plans](/en/45-explain/)
- [46 BEGIN, COMMIT, ROLLBACK](/en/46-begin-commit-rollback/)
- [47 Savepoints](/en/47-savepoints/)
- [48 CREATE VIEW](/en/48-create-view/)
- [49 Materialized Views](/en/49-materialized-views/)
- [50 Functions and PL/pgSQL](/en/50-functions-and-plpgsql/)
- [51 Triggers](/en/51-triggers/)
- [52 Full-Text Search](/en/52-full-text-search/)
- [53 VACUUM and ANALYZE](/en/53-vacuum-and-analyze/)
- [54 pg_dump and pg_restore](/en/54-pg-dump-and-restore/)
- [55 Roles and GRANT](/en/55-roles-and-grant/)
- [56 psql Meta-commands](/en/56-psql-meta-commands/)
- [57 COPY](/en/57-copy/)
- [Editorial Corrections](/en/errata/)
- [Publication and License](/en/publication/)

## Examples {#examples}

- [Example 1.1](/en/01-first-query/#eg-1.1) — First Query
- [Example 2.1](/en/02-psql-basics/#eg-2.1) — psql Basics
- [Example 3.1](/en/03-select-basics/#eg-3.1) — SELECT Basics
- [Example 4.1](/en/04-where/#eg-4.1) — WHERE
- [Example 5.1](/en/05-order-by/#eg-5.1) — ORDER BY
- [Example 6.1](/en/06-select-from-table/#eg-6.1) — SELECT from a Table
- [Example 7.1](/en/07-limit-offset/#eg-7.1) — LIMIT and OFFSET
- [Example 8.1](/en/08-distinct/#eg-8.1) — DISTINCT
- [Example 9.1](/en/09-nulls/#eg-9.1) — NULLs
- [Example 10.1](/en/10-expressions/#eg-10.1) — Expressions
- [Example 11.1](/en/11-numeric-types/#eg-11.1) — Numeric Types
- [Example 12.1](/en/12-text-types/#eg-12.1) — Text Types
- [Example 13.1](/en/13-boolean-and-dates/#eg-13.1) — Boolean and Dates
- [Example 14.1](/en/14-uuid-jsonb/#eg-14.1) — UUID and JSONB
- [Example 15.1](/en/15-arrays-and-enum/#eg-15.1) — Arrays and ENUM
- [Example 16.1](/en/16-create-table/#eg-16.1) — CREATE TABLE
- [Example 17.1](/en/17-column-types-and-constraints/#eg-17.1) — Column Types and Constraints
- [Example 18.1](/en/18-alter-table-and-drop/#eg-18.1) — ALTER TABLE and DROP
- [Example 19.1](/en/19-primary-keys-and-unique/#eg-19.1) — Primary Keys and UNIQUE
- [Example 20.1](/en/20-foreign-keys/#eg-20.1) — Foreign Keys and REFERENCES
- [Example 21.1](/en/21-not-null-and-default/#eg-21.1) — NOT NULL and DEFAULT
- [Example 22.1](/en/22-identity-and-sequences/#eg-22.1) — Identity Columns and Sequences
- [Example 23.1](/en/23-schemas/#eg-23.1) — Schemas and search\_path
- [Example 24.1](/en/24-insert/#eg-24.1) — INSERT
- [Example 25.1](/en/25-upsert/#eg-25.1) — UPSERT \(ON CONFLICT\)
- [Example 26.1](/en/26-update/#eg-26.1) — UPDATE
- [Example 27.1](/en/27-delete/#eg-27.1) — DELETE
- [Example 28.1](/en/28-returning/#eg-28.1) — RETURNING
- [Example 29.1](/en/29-inner-and-left-join/#eg-29.1) — INNER and LEFT JOIN
- [Example 30.1](/en/30-right-and-full-join/#eg-30.1) — RIGHT and FULL JOIN
- [Example 31.1](/en/31-self-join/#eg-31.1) — Self\-Join
- [Example 32.1](/en/32-union-intersect-except/#eg-32.1) — UNION\, INTERSECT\, EXCEPT
- [Example 33.1](/en/33-count-sum-avg/#eg-33.1) — COUNT\, SUM\, AVG
- [Example 34.1](/en/34-group-by/#eg-34.1) — GROUP BY
- [Example 35.1](/en/35-having/#eg-35.1) — HAVING
- [Example 36.1](/en/36-scalar-and-in-subqueries/#eg-36.1) — Scalar and IN Subqueries
- [Example 37.1](/en/37-exists-and-derived-tables/#eg-37.1) — EXISTS and Derived Tables
- [Example 38.1](/en/38-cte/#eg-38.1) — Common Table Expressions \(WITH\)
- [Example 39.1](/en/39-window-functions/#eg-39.1) — Window Functions
- [Example 40.1](/en/40-string-and-numeric-functions/#eg-40.1) — String and Numeric Functions
- [Example 41.1](/en/41-date-functions-and-coalesce/#eg-41.1) — Date Functions and COALESCE
- [Example 42.1](/en/42-case/#eg-42.1) — CASE
- [Example 43.1](/en/43-create-index/#eg-43.1) — CREATE INDEX
- [Example 44.1](/en/44-when-to-index/#eg-44.1) — When to Index
- [Example 45.1](/en/45-explain/#eg-45.1) — EXPLAIN and Query Plans
- [Example 46.1](/en/46-begin-commit-rollback/#eg-46.1) — BEGIN\, COMMIT\, ROLLBACK
- [Example 47.1](/en/47-savepoints/#eg-47.1) — Savepoints
- [Example 48.1](/en/48-create-view/#eg-48.1) — CREATE VIEW
- [Example 49.1](/en/49-materialized-views/#eg-49.1) — Materialized Views
- [Example 50.1](/en/50-functions-and-plpgsql/#eg-50.1) — Functions and PL\/pgSQL
- [Example 51.1](/en/51-triggers/#eg-51.1) — Triggers
- [Example 52.1](/en/52-full-text-search/#eg-52.1) — Full\-Text Search
- [Example 53.1](/en/53-vacuum-and-analyze/#eg-53.1) — VACUUM and ANALYZE
- [Example 54.1](/en/54-pg-dump-and-restore/#eg-54.1) — pg\_dump and pg\_restore
- [Example 55.1](/en/55-roles-and-grant/#eg-55.1) — Roles and GRANT
- [Example 56.1](/en/56-psql-meta-commands/#eg-56.1) — psql Meta\-commands
- [Example 57.1](/en/57-copy/#eg-57.1) — COPY

## About this edition {#edition}

This English web edition follows the [boringcollege/postgres-by-example](https://github.com/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](/en/errata/). The repository is licensed under
[Creative Commons Attribution 4.0 International](https://creativecommons.org/licenses/by/4.0/).

[Publication and license](/en/publication/) · [Editorial corrections](/en/errata/)

---

Section pages:

- [First Query](/en/01-first-query/): Run your first query and check the PostgreSQL server and psql client versions.
- [psql Basics](/en/02-psql-basics/): Run SQL files, explore a database interactively, and get help from psql.
- [SELECT Basics](/en/03-select-basics/): Select values and columns, name query results, and inspect system catalogs.
- [WHERE](/en/04-where/): Filter rows with comparisons, patterns, ranges, and SQL's three-valued logic.
- [ORDER BY](/en/05-order-by/): Sort query results, break ties, and control where NULL values appear.
- [SELECT from a Table](/en/06-select-from-table/): Read the sample fruits table with explicit columns, aliases, and filters.
- [LIMIT and OFFSET](/en/07-limit-offset/): Limit result size and compare offset pagination with keyset pagination.
- [DISTINCT](/en/08-distinct/): Remove duplicate rows and choose one row per group with DISTINCT ON.
- [NULLs](/en/09-nulls/): Understand NULL, unknown comparisons, and NULL-aware expressions.
- [Expressions](/en/10-expressions/): Combine arithmetic, strings, function calls, and type casts in SQL expressions.
- [Numeric Types](/en/11-numeric-types/): Choose between integers, exact decimals, and floating-point numbers.
- [Text Types](/en/12-text-types/): Store and compare text, measure character lengths, and understand padding and collation.
- [Boolean and Dates](/en/13-boolean-and-dates/): Work with booleans, dates, timestamps, time zones, and intervals.
- [UUID and JSONB](/en/14-uuid-jsonb/): Generate UUIDs and query structured JSON documents with jsonb operators.
- [Arrays and ENUM](/en/15-arrays-and-enum/): Store arrays, query their elements, and define ordered sets of ENUM values.
- [CREATE TABLE](/en/16-create-table/): Create a table, inspect its columns, and understand temporary and unlogged tables.
- [Column Types and Constraints](/en/17-column-types-and-constraints/): Combine data types, defaults, and constraints in a practical table definition.
- [ALTER TABLE and DROP](/en/18-alter-table-and-drop/): Change tables and constraints while understanding rewrite and locking costs.
- [Primary Keys and UNIQUE](/en/19-primary-keys-and-unique/): Choose primary keys and unique constraints, including how they treat NULL.
- [Foreign Keys and REFERENCES](/en/20-foreign-keys/): Connect tables with foreign keys and choose the appropriate update and delete actions.
- [NOT NULL and DEFAULT](/en/21-not-null-and-default/): Require values with NOT NULL and supply useful defaults for new rows.
- [Identity Columns and Sequences](/en/22-identity-and-sequences/): Generate identifiers with identity columns and understand shared sequence behavior.
- [Schemas and search_path](/en/23-schemas/): Organize database objects into schemas and control name resolution with search_path.
- [INSERT](/en/24-insert/): Insert single rows, batches, and query results while naming target columns explicitly.
- [UPSERT (ON CONFLICT)](/en/25-upsert/): Insert or update atomically with ON CONFLICT, EXCLUDED, and RETURNING.
- [UPDATE](/en/26-update/): Update selected rows, join a data source, and return the values you changed.
- [DELETE](/en/27-delete/): Delete selected rows and understand how DELETE differs from TRUNCATE.
- [RETURNING](/en/28-returning/): Return generated identifiers and modified rows directly from data-changing statements.
- [INNER and LEFT JOIN](/en/29-inner-and-left-join/): Join matching rows and preserve unmatched rows with INNER JOIN and LEFT JOIN.
- [RIGHT and FULL JOIN](/en/30-right-and-full-join/): Use outer joins to preserve either side and find unmatched records.
- [Self-Join](/en/31-self-join/): Query relationships within one table and follow an employee hierarchy.
- [UNION, INTERSECT, EXCEPT](/en/32-union-intersect-except/): Combine query results with UNION, INTERSECT, and EXCEPT, with or without duplicates.
- [COUNT, SUM, AVG](/en/33-count-sum-avg/): Count, sum, average, and collect values while accounting for NULL and empty input.
- [GROUP BY](/en/34-group-by/): Group rows for aggregation and build subtotals with ROLLUP and GROUPING SETS.
- [HAVING](/en/35-having/): Filter aggregate groups with HAVING and distinguish group predicates from row filters.
- [Scalar and IN Subqueries](/en/36-scalar-and-in-subqueries/): Use scalar and membership subqueries and handle empty results and NULL correctly.
- [EXISTS and Derived Tables](/en/37-exists-and-derived-tables/): Test for related rows with EXISTS and name intermediate results with derived tables.
- [Common Table Expressions (WITH)](/en/38-cte/): Structure queries with WITH, move rows atomically, and traverse trees recursively.
- [Window Functions](/en/39-window-functions/): Rank rows and compute running totals, neighboring values, and window frames.
- [String and Numeric Functions](/en/40-string-and-numeric-functions/): Transform strings and use common numeric, rounding, and random-number functions.
- [Date Functions and COALESCE](/en/41-date-functions-and-coalesce/): Group dates into time buckets, extract fields, and supply defaults with COALESCE.
- [CASE](/en/42-case/): Express conditional values with CASE and compare conditional aggregation with FILTER.
- [CREATE INDEX](/en/43-create-index/): Create plain, multicolumn, partial, and expression indexes with appropriate locking.
- [When to Index](/en/44-when-to-index/): Choose indexes from real queries and inspect whether PostgreSQL uses them.
- [EXPLAIN and Query Plans](/en/45-explain/): Read execution plans and compare estimated costs with actual rows, timings, and buffers.
- [BEGIN, COMMIT, ROLLBACK](/en/46-begin-commit-rollback/): Group changes into transactions, commit or roll back, and observe an expected failure.
- [Savepoints](/en/47-savepoints/): Roll back part of a transaction and recover from errors with savepoints.
- [CREATE VIEW](/en/48-create-view/): Expose reusable queries through views and understand when views accept updates.
- [Materialized Views](/en/49-materialized-views/): Store query results in materialized views and refresh them while managing reader access.
- [Functions and PL/pgSQL](/en/50-functions-and-plpgsql/): Define SQL and PL/pgSQL functions with appropriate volatility and security settings.
- [Triggers](/en/51-triggers/): Maintain timestamps and audit changes with row-level and statement-level triggers.
- [Full-Text Search](/en/52-full-text-search/): Search and rank text with tsvector, tsquery, language configurations, and GIN indexes.
- [VACUUM and ANALYZE](/en/53-vacuum-and-analyze/): Maintain row visibility and planner statistics with VACUUM, ANALYZE, and autovacuum.
- [pg_dump and pg_restore](/en/54-pg-dump-and-restore/): Back up and restore scratch databases using plain and custom-format logical dumps.
- [Roles and GRANT](/en/55-roles-and-grant/): Grant object privileges and demonstrate row-level security with an ordinary database role.
- [psql Meta-commands](/en/56-psql-meta-commands/): Explore objects, format results, run scripts, and customize sessions with psql commands.
- [COPY](/en/57-copy/): Import and export CSV data using server-side COPY and client-side psql copy commands.
- [Editorial Corrections](/en/errata/): Corrections to the pinned English source edition, with PostgreSQL references.
- [Publication and License](/en/publication/): Authorship, source revision, license, and editorial changes for Postgres by Example.