gem-hunter

PostgreSQL is a free, open-source, ACID-compliant relational database. It is the database of choice for a large share of new applications because it is reliable, deeply featureful, standards-compliant, and friendly to extension. The official documentation is excellent — keep it open as you work through these lessons.

Postgres by Example is a hands-on introduction to PostgreSQL using annotated SQL examples. Each lesson is short enough to read in a few minutes and is paired with a runnable .sql file in source/. The lessons build on one another: by the end you will know how to model data, query it, index it, run transactions, write stored functions, and operate the database day-to-day. Start with the first example or browse the full list below.

Prerequisites. A working PostgreSQL installation (version 14 or newer is recommended; the lessons target current stable PostgreSQL, which at the time of writing is PostgreSQL 17). You should be able to connect with the psql command-line client. The examples assume a database called postgres unless noted; create a scratch database if you prefer (createdb pbe && psql pbe). Start the server with your system's service manager (brew services start postgresql, systemctl start postgresql, etc.) or with pg_ctl start for an isolated cluster.

How to use this book. Each lesson includes a short explanation, a SQL example you can run, and a sample of the output. Run the file with psql -f source/<lesson>.sql postgres and compare what you see to the expected output. Many lessons build on tables created in earlier lessons (fruits, orders_example, etc.), so if you skip around you may need to run the earlier scripts first. Try the Try it exercise at the bottom of each lesson — typing variations is the fastest way to internalize SQL.

Versioning. Unless stated otherwise, examples target the current stable release. Anything that requires a specific minimum version (e.g. gen_random_uuid() is built in from 13, MERGE from 15) is called out in the lesson. Use the newest PostgreSQL you can; the language and tooling improve every year.

• First Query
• psql Basics

• SELECT Basics
• WHERE
• ORDER BY
• SELECT from a Table
• LIMIT and OFFSET
• DISTINCT
• NULLs
• Expressions

• Numeric Types
• Text Types
• Boolean and Dates
• UUID and JSONB
• Arrays and ENUM

• CREATE TABLE
• Column Types and Constraints
• ALTER TABLE and DROP
• Primary Keys and Unique
• Foreign Keys and REFERENCES
• NOT NULL and DEFAULT
• Identity Columns and Sequences
• Schemas and search_path

• INSERT
• UPSERT (ON CONFLICT)
• UPDATE
• DELETE
• RETURNING

• INNER and LEFT JOIN
• RIGHT and FULL JOIN
• Self-Join
• UNION, INTERSECT, EXCEPT

• COUNT, SUM, AVG
• GROUP BY
• HAVING

• Scalar and IN Subqueries
• EXISTS and Derived Tables
• Common Table Expressions (WITH)
• Window Functions

• String and Numeric Functions
• Date Functions and COALESCE
• CASE

• CREATE INDEX
• When to Index
• EXPLAIN and Query Plans

• BEGIN, COMMIT, ROLLBACK
• Savepoints

• CREATE VIEW
• Materialized Views

• Functions and PL/pgSQL
• Triggers

• Full-Text Search

• VACUUM and ANALYZE
• pg_dump and pg_restore

• Roles and GRANT

• psql Meta-commands
• COPY

---

Found a typo, a clearer wording, or a missing topic? Open an issue or pull request — see CONTRIBUTING.md. The goal is short, accurate, runnable lessons that make PostgreSQL approachable without dumbing it down.

Licensed under CC BY 4.0.

by Dariush Abbasi | source