What this is
In my career, I have met a surprisingly large number of very talented engineers who are not comfortable with PostgreSQL and SQL fundamentals.
PgQuest is a concise, interactive introduction to the basics of PostgreSQL. The code and the examples do most of the explaining, so you can learn by reading queries and seeing what PostgreSQL does with them.
It is written for experienced developers who want a practical grounding in SQL and PostgreSQL, without starting with a long textbook or a large application.
Playground
Load any lesson database
Use the same browser SQL editor without following a lesson. Load any database used in the lessons, edit the query, and run it directly in PGlite.
Open
Curriculum
18 lessons · roughly 4 hours
Lesson 01
My first query
Create a small table, seed two rows, SELECT them back, and inspect the plan Postgres uses.
SELECT · EXPLAIN
10 min
0/3 exercises
Lesson 02
Introduction to JOIN
See why splitting data across tables matters, and use JOIN to combine it back together.
JOIN
10 min
0/1 exercises
Lesson 03
Schemas, tables, and types
Explore schemas, list tables, inspect columns, and understand database shape.
SCHEMA · TABLES · TYPES
15 min
0/2 exercises
Lesson 04
An introduction to indexes
Indexes and EXPLAIN plans, comparing a sequential scan with an index scan on the same query.
INDEX · EXPLAIN
15 min
0/2 exercises
Lesson 05
DraftInsert, update, delete
Change stored rows with INSERT, UPDATE, DELETE, and RETURNING.
INSERT · UPDATE · DELETE
15 min
0/2 exercises
Lesson 06
DraftConstraints
Use NOT NULL, UNIQUE, CHECK, primary keys, and foreign keys to protect data.
CONSTRAINTS · KEYS
15 min
0/2 exercises
Lesson 07
DraftAdvanced indexes
Use selectivity, composite indexes, and partial indexes to shape better plans.
SELECTIVITY · COMPOSITE · PARTIAL
20 min
0/2 exercises
Lesson 08
DraftAggregation intro
Summarize rows with COUNT, SUM, AVG, MIN, MAX, and GROUP BY.
COUNT · GROUP BY
15 min
0/2 exercises
Lesson 09
DraftNULL
Understand missing values, IS NULL, three-valued logic, and COALESCE.
NULL · COALESCE
15 min
0/2 exercises
Lesson 10
Types of JOINs
Compare INNER, LEFT, RIGHT, FULL OUTER, and CROSS JOIN on the same two tables.
JOIN · LEFT · RIGHT · FULL · CROSS
20 min
0/2 exercises
Lesson 11
DraftRelationships
Model one-to-many and many-to-many data with foreign keys and join tables.
FOREIGN KEY · RELATIONSHIPS
20 min
0/2 exercises
Lesson 12
DraftSorting and pagination
Sort rows deterministically with ORDER BY, LIMIT, OFFSET, and indexes.
ORDER BY · LIMIT
15 min
0/2 exercises
Lesson 13
DraftText search basics
Search text with LIKE, ILIKE, wildcards, and case-insensitive patterns.
LIKE · ILIKE
15 min
0/2 exercises
Lesson 14
DraftJSON in PostgreSQL
Store jsonb, extract fields, query nested values, and use containment.
JSONB
20 min
0/2 exercises
Lesson 15
DraftTransactions
Group changes with BEGIN, COMMIT, and ROLLBACK so work is atomic.
BEGIN · COMMIT · ROLLBACK
15 min
0/1 exercises
Lesson 16
DraftAdvanced aggregation
Use HAVING, FILTER, date_trunc, and time-based grouping.
HAVING · FILTER · DATE_TRUNC
20 min
0/2 exercises
Lesson 17
DraftVectors
Use pgvector-style similarity search and see how vectors fit into PostgreSQL.
VECTOR · SIMILARITY
20 min
0/2 exercises
Lesson 18
Implementing a high-concurrency system with Postgres
Model a seat reservation system and use transactions with advisory locks to close a real race condition.
ACID · TRANSACTIONS · ADVISORY LOCKS
25 min
0/2 exercises