Editorial Corrections
This web edition corrects technical errors in the pinned source, and marks the adaptation in each page’s attribution. The original repository and its license remain available from Publication and License.
Chapter order is unchanged. The three comma-containing titles are recovered from their chapter headings, and OINK provides the sequential chapter navigation.
Displayed SQL and downloadable SQL use the same corrected example. Chapters 54 and 56 instead pair a shell example or command reference with a SQL companion.
Console output is illustrative: dates, UUIDs, users, catalog listings, statistics, and query plans depend on the environment. Ellipses mark omitted output.
1. First Query
The client/server version explanation now distinguishes the version reported by the connected server from the local psql binary.
PostgreSQL documentation: reference 1.
2. psql Basics
The psql section now explains that -f processes backslash commands and that these commands are client syntax which may issue catalog queries.
PostgreSQL documentation: reference 1.
3. SELECT Basics
The SELECT wording no longer promises an evaluation order that SQL does not guarantee.
PostgreSQL documentation: reference 1.
4. WHERE
The runnable series query is fixed, and the ILIKE and NULL predicate guidance now reflects PostgreSQL’s actual planner and three-valued-logic behavior.
PostgreSQL documentation: reference 1, reference 2, reference 3.
7. LIMIT and OFFSET
The OFFSET cost example now describes the number of rows processed without an off-by-one claim.
PostgreSQL documentation: reference 1.
8. DISTINCT
The DISTINCT example now correctly explains that parentheses around a selected expression do not change the distinct key.
PostgreSQL documentation: reference 1.
9. NULLs
The NULL comparison explanation now separates unknown comparisons, DISTINCT grouping, and IS DISTINCT FROM.
PostgreSQL documentation: reference 1.
10. Expressions
The cast example uses an unambiguous output name.
PostgreSQL documentation: reference 1.
11. Numeric Types
Numeric ranges, overflow behavior, and unconstrained numeric limits are stated more precisely.
PostgreSQL documentation: reference 1.
12. Text Types
The character padding example now distinguishes character length from stored byte length and labels the collation example accurately.
PostgreSQL documentation: reference 1.
13. Boolean and Dates
The date/time section now describes timestamptz display semantics and current_timestamp versus clock_timestamp correctly.
PostgreSQL documentation: reference 1.
14. UUID and JSONB
UUIDv7 availability is version-scoped: PostgreSQL 17 has no built-in generator, while PostgreSQL 18 provides uuidv7().
PostgreSQL documentation: reference 1.
15. Arrays and ENUM
The array indexing text acknowledges PostgreSQL’s default lower bound without overstating the rule.
PostgreSQL documentation: reference 1.
17. Column Types and Constraints
Constraint placement is described accurately for composite constraints and multi-column CHECK expressions.
PostgreSQL documentation: reference 1.
18. ALTER TABLE and DROP
ALTER TABLE guidance now distinguishes changes that require a table rewrite from binary-compatible changes and describes rename dependencies more carefully.
PostgreSQL documentation: reference 1.
19. Primary Keys and UNIQUE
The NULLS NOT DISTINCT recommendation no longer presents a partial unique index as an equivalent solution.
PostgreSQL documentation: reference 1, reference 2.
21. NOT NULL and DEFAULT
The table-default discussion distinguishes nonvolatile current_timestamp from volatile defaults such as random().
PostgreSQL documentation: reference 1, reference 2.
22. Identity Columns and Sequences
Sequence objects are shared across sessions; currval() state is the session-local part.
PostgreSQL documentation: reference 1.
23. Schemas and search_path
SECURITY DEFINER and search_path guidance now uses a trusted path with pg_temp last, and schema drops are described as transactionally reversible before commit.
PostgreSQL documentation: reference 1, reference 2.
25. UPSERT (ON CONFLICT)
The ON CONFLICT examples now use the actual constraint name and qualify the partial-index caveat.
PostgreSQL documentation: reference 1.
26. UPDATE
The update lesson now uses IS DISTINCT FROM for a NULL-safe change test.
PostgreSQL documentation: reference 1.
27. DELETE
DELETE and TRUNCATE behavior now distinguishes row-level triggers, foreign-key checks, locking, WAL, and rollback semantics.
PostgreSQL documentation: reference 1, reference 2, reference 3.
28. RETURNING
RETURNING syntax is scoped to PostgreSQL 17, with the PostgreSQL 18 old/new extension identified separately.
PostgreSQL documentation: reference 1, reference 2.
29. INNER and LEFT JOIN
The join warning no longer says PostgreSQL warns about valid cross joins or treats a missing INNER JOIN condition as valid syntax.
PostgreSQL documentation: reference 1.
32. UNION, INTERSECT, EXCEPT
The ALL variants of UNION, INTERSECT, and EXCEPT are described with their different duplicate rules.
PostgreSQL documentation: reference 1.
33. COUNT, SUM, AVG
Aggregate NULL behavior is no longer generalized to aggregates such as array_agg and jsonb_agg that retain NULL inputs.
PostgreSQL documentation: reference 1.
35. HAVING
The WHERE/HAVING performance guidance now leaves room for PostgreSQL to push suitable predicates down.
PostgreSQL documentation: reference 1.
36. Scalar and IN Subqueries
A scalar subquery is correctly described as returning at most one row, with zero rows producing NULL.
PostgreSQL documentation: reference 1, reference 2.
37. EXISTS and Derived Tables
Derived-table alias guidance reflects PostgreSQL 16’s optional alias behavior while retaining the portability recommendation.
PostgreSQL documentation: reference 1.
38. Common Table Expressions (WITH)
CTE folding, recursive UNION choices, nontermination, and data-modifying CTE ordering are described without unsupported guarantees.
PostgreSQL documentation: reference 1.
39. Window Functions
Window-frame defaults and the need for an explicit lag/lead order are stated precisely.
PostgreSQL documentation: reference 1, reference 2.
40. String and Numeric Functions
The examples distinguish random integer generation from a direct floating-point cast and correct the rounding description.
PostgreSQL documentation: reference 1.
41. Date Functions and COALESCE
Date bucketing and extract(epoch) are aligned with PostgreSQL 17’s return types and date functions.
PostgreSQL documentation: reference 1.
42. CASE
FILTER is compared with CASE without claiming identical empty-result behavior.
PostgreSQL documentation: reference 1.
43. CREATE INDEX
Multicolumn index guidance reflects modern planner options, lock modes, and HOT-update behavior.
PostgreSQL documentation: reference 1, reference 2, reference 3.
44. When to Index
The index-selection guidance removes a universal selectivity percentage and an overbroad redundancy rule.
PostgreSQL documentation: reference 1, reference 2, reference 3.
45. EXPLAIN and Query Plans
The EXPLAIN aggregate counts actual child rows, and index-only scans now mention visibility-map heap checks.
PostgreSQL documentation: reference 1, reference 2.
46. BEGIN, COMMIT, ROLLBACK
Transaction durability and dry-run limitations now mention normal durability settings, sequence increments, locks, and external effects; the two-session exercise recreates its demo table first.
PostgreSQL documentation: reference 1, reference 2, reference 3.
48. CREATE VIEW
CREATE OR REPLACE VIEW restrictions are separated from ALTER VIEW column renames.
PostgreSQL documentation: reference 1.
49. Materialized Views
REFRESH MATERIALIZED VIEW CONCURRENTLY is described as a full-query refresh requiring a populated matview and a suitable unique index.
PostgreSQL documentation: reference 1, reference 2.
50. Functions and PL/pgSQL
Function volatility, SECURITY DEFINER search paths, and PL/pgSQL statement syntax are corrected.
PostgreSQL documentation: reference 1, reference 2.
51. Triggers
Trigger return values now distinguish INSERT/UPDATE from DELETE, and the exercise accounts for the script’s cleanup.
PostgreSQL documentation: reference 1.
52. Full-Text Search
The full-text-search exercise tells readers to run it before the script drops its demonstration table.
PostgreSQL documentation: reference 1, reference 2.
53. VACUUM and ANALYZE
VACUUM’s visibility-map role, tail-page truncation, and autovacuum guidance are more exact.
PostgreSQL documentation: reference 1.
54. pg_dump and pg_restore
pg_dump snapshot, globals, version compatibility, and restore behavior are corrected and qualified.
PostgreSQL documentation: reference 1, reference 2.
55. Roles and GRANT
The role and RLS example now forces row security for the table owner and documents the limits of a freely changeable tenant setting.
PostgreSQL documentation: reference 1, reference 2, reference 3, reference 4.
56. psql Meta-commands
The psql command reference now accurately covers files executed with -f, SQL alternatives, and CSV output mode.
PostgreSQL documentation: reference 1.
57. COPY
COPY FROM trigger behavior, encoding selection, bulk-load performance, and the executable multiline \copy exercise are corrected.
PostgreSQL documentation: reference 1, reference 2.