PostgreSQL: 1,000 Examples — Companion

The practice environment for PostgreSQL: 1,000 Examples by Bi Learner.

---

Start practising in one command

You need Docker Desktop, which is free for personal use, and nothing else.

docker run -d --name pg1000-practice \
  -p 8888:8888 -p 5432:5432 \
  bilearner/postgres1000-practice:1.1

Then open http://localhost:8888/lab.

PostgreSQL 16.15 is already running with the book's datasets loaded and all 53 chapter folders waiting. There is nothing to generate, nothing to load, and nothing to configure. First start is a few seconds.

The same image is on GitHub's registry too, if you prefer it or if Docker Hub's anonymous pull limit gets in your way on a shared network:

docker run -d --name pg1000-practice \
  -p 8888:8888 -p 5432:5432 \
  ghcr.io/bitoollearner/postgres1000-practice:1.1

To connect your own client instead — pgAdmin, DBeaver, DataGrip, PyCharm:

Setting	Value
Host	localhost
Port	5432
Database	pg1000
Username	book
Password	book

---

What this repository is

The book is 1000 worked PostgreSQL examples across 53 chapters. Every one was executed against PostgreSQL 16.15 and its output captured — the printed row counts, sums and execution plans are real, not illustrative.

This repository is the environment those examples run in, so you can work through them on your own machine and get the same answers the book prints.

What you get here

• The practice image — PostgreSQL 16.15, configured exactly as the book was verified against, with the datasets preloaded and JupyterLab on top.
• 53 chapter notebooks — every example as a problem to solve, with an empty cell under it waiting for your SQL.
• The datasets — the seeded generators, so the figures are reproducible rather than approximate.
• The exact server configuration — conf/postgresql.conf, copied from the book's own build. Plans depend on these settings; a different work_mem or default_statistics_target produces a different plan.
• A chapter index — all 1000 example titles, so you can find the one you need.

What is in the book, not here

The solutions, explanations, common mistakes, recommendations and pattern insights. Each example in the book carries all of them in a fixed seven-section format, plus a captured execution plan for the advanced tier.

That is the deal: the environment and the problems are free and open, and the answers are what you buy. The notebooks here give you the question and a blank cell — work it out, then check yourself against the book.

Get the book on Amazon Kindle

---

The datasets

Three schemas, each from a seeded generator, so your numbers match the printed ones exactly.

Schema	Scale	In the image?	Used by
ecommerce	5,000 orders, 14,998 lines	preloaded	most of the book
hr	1,200 employees	preloaded	Chapter 49
ecommerce_lg	2,000,000 orders, 6M lines	load on demand	Part VIII

ecommerce_lg is not baked in because it would add roughly 850 MB to the image for the one part that needs it. Load it when you reach Part VIII:

docker exec -it pg1000-practice load-large-dataset

Index selection, partition pruning and BRIN cannot be demonstrated on five thousand rows — at that size the planner correctly prefers a sequential scan and every lesson inverts. See docs/DATASETS.md, including the defects the data contains on purpose.

---

Contents

conf/            the server configuration the book was verified against
datasets/        seeded generators and DDL for all three schemas
docs/            setup, datasets, troubleshooting, and the chapter index
notebooks/       53 chapter notebooks -- problems, not answers
practice-image/  the Dockerfile and entrypoint for the image above
scripts/         environment verification and dataset loading

---

Chapters

Part I — Getting Started

• 1. PostgreSQL in Context · 5 examples
• 2. Your Environment: Docker, psql, PyCharm · 8 examples
• 3. The Architecture You Actually Need · 7 examples

Part II — Data Definition and Types

• 4. Databases, Schemas and Tables · 14 examples
• 5. The Type System: Numbers, Text, Booleans · 20 examples
• 6. Dates, Times and Time Zones as Types · 12 examples
• 7. Constraints and Data Integrity · 22 examples
• 8. Writing Data: INSERT, UPDATE, DELETE, UPSERT · 24 examples

Part III — Querying

• 9. SELECT Fundamentals · 22 examples
• 10. Filtering and Conditional Logic · 26 examples
• 11. String Functions · 28 examples
• 12. Numeric and Mathematical Functions · 16 examples
• 13. Date and Time Functions · 36 examples
• 14. Aggregation and GROUP BY · 38 examples
• 15. Advanced Grouping: ROLLUP, CUBE, GROUPING SETS · 12 examples
• 16. Joins · 45 examples

Part IV — Composition and Analytics

• 17. Subqueries · 18 examples
• 18. Common Table Expressions · 20 examples
• 19. Recursive CTEs · 12 examples
• 20. Window Functions · 60 examples
• 21. Analytical Patterns · 28 examples

Part V — Data Modeling

• 22. Relational Modeling · 20 examples
• 23. Normalization and Denormalization · 16 examples
• 24. Dimensional Modeling · 26 examples
• 25. Slowly Changing Dimensions · 20 examples
• 26. Designing Production Schemas · 15 examples

Part VI — PostgreSQL's Distinctive Types

• 27. Arrays · 25 examples
• 28. JSON and JSONB · 19 examples
• 29. UUID, ENUM, Ranges and Custom Types · 15 examples

Part VII — Objects, Transactions and Programming

• 30. Views and Materialized Views · 20 examples
• 31. Transactions and Savepoints · 13 examples
• 32. MVCC, Isolation and Locking · 14 examples
• 33. Functions and Procedures · 22 examples
• 34. Triggers and Change Tracking · 13 examples

Part VIII — Performance

• 35. Index Fundamentals · 17 examples
• 36. Specialized Indexes · 16 examples
• 37. Reading Execution Plans · 17 examples
• 38. Query Optimization · 16 examples
• 39. Partitioning, VACUUM and Maintenance · 20 examples

Part IX — Data Engineering

• 40. Loading Data · 17 examples
• 41. Incremental Processing and CDC · 18 examples
• 42. Dimension and Fact Pipelines · 16 examples
• 43. Data Quality and Reconciliation · 14 examples
• 44. Orchestration with Python · 14 examples

Part X — Production Operations

• 45. Roles, Security and RLS · 13 examples
• 46. Backup, Recovery and PITR · 11 examples
• 47. Monitoring and System Catalogs · 12 examples
• 48. Beyond the Core · 4 examples

Part XI — Capstone Projects

• 49. HR and Workforce Analytics · 10 examples
• 50. Capstone I - E-Commerce Warehouse · 22 examples
• 51. Capstone II - Enterprise ELT · 20 examples
• 52. Capstone III - Banking and Fraud · 16 examples
• 53. Capstone IV - Event Analytics · 16 examples

---

Difficulty mix

Level	Examples	What it means
Beginner	142	establishes a mechanism; usually one reasonable way to write it
Intermediate	729	applying it correctly, which is where most real difficulty lives
Advanced	129	carries a captured EXPLAIN plan and a performance discussion

---

Running without Docker

You can use any PostgreSQL 13 or later, but the book assumes the container's configuration and some output will differ. See docs/SETUP.md. The settings that matter are in conf/postgresql.conf, and the cluster locale must be C — text sort order depends on it, and every example that orders mixed-case text will differ otherwise.

---

Errata and questions

Found a mistake in the book, or an example that does not reproduce? Open an issue. Errata reports are especially welcome and are credited in the next revision.

---

Licence

The code, notebooks, configuration and dataset generators in this repository are MIT licensed — see LICENSE.

The book's text, example solutions and explanations are © Bi Learner and are not covered by that licence.