gem-hunter
Language: PLpgSQL
Yet another postgresql book!
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.
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