Postgres by Example — English web edition
https://book.pg.center/examples/

By Dariush Abbasi. Original repository:
https://github.com/boringcollege/postgres-by-example
Source revision: ffde0d358722498e37982b57bdbd711871a61c43
License: Creative Commons Attribution 4.0 International.
The upstream LICENSE file is preserved verbatim, including its mistaken
"Python by Example" heading. See the web edition's Publication and License page.

This archive contains corrected web-edition SQL. It differs from the upstream
files where technical errors were corrected and instructional comments were
aligned with the displayed lessons. See /examples/errata/ for the corrections.
The lessons target PostgreSQL 17; PostgreSQL 18-specific features are identified.

SETUP

Unzip into a new directory. Use a disposable practice database: the examples
create, update, and drop their demonstration objects. Run from the directory
containing source/. Replace "postgres" in the commands with your database name.
Use a non-superuser database owner without BYPASSRLS so chapter 55 can demonstrate
row-level security. This role needs CREATE on the practice schema.

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

Before chapter 6, prepare the fruits table:

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

Then read in numbered chapter order. Chapter 16 recreates fruits and chapter 24
populates it again. Chapter 18 needs chapter 17's products; chapter 30 and later
join examples use chapter 29's tables; chapter 38 also uses chapter 31's employee
tree. When jumping into a lesson, follow its prerequisites and rerun the relevant
setup in a fresh scratch database. Do not execute the files in alphabetical order.

Chapter 38 archives an order. Chapter 48 inserts an order through a view. Later
outputs reflect those changes. Optional exercises can also change later results.
Dates, UUIDs, catalog listings, query plans, and timings vary by environment.

Chapter 46 deliberately raises a duplicate-key error. For that lesson only, use:

    psql -X -v ON_ERROR_STOP=off -f source/begin-commit-rollback.sql pbe

The expected error is followed by ROLLBACK and cleanup. Other unexpected errors
should be investigated. Some exercises require recreating objects dropped by
the demonstration's cleanup; the lesson identifies these cases.

Chapter 54's SQL file documents shell commands. Running it does not perform a
backup or restore. Run the shell commands from the lesson against scratch
databases. Chapter 56's SQL file shows catalog queries; its page also lists psql
commands, whose descriptive labels are not executable syntax.
