Free through lesson 3

Database Fundamentals with PostgreSQL

It follows the same running order as the MySQL course, but every lesson brings in something only PostgreSQL has. DISTINCT ON, = ANY(array), the FILTER clause, keyset pagination, JSONB, SKIP LOCKED, MERGE, and Buffers in EXPLAIN. Thirty lessons ending at a design where whatever a constraint can protect is not re-protected in the application.

Start from lesson 1 Unlock every course

Curriculum

The 30 lessons are grouped into six chapters. Working through them in order is the best way, but you are welcome to read only the part you are stuck on right now. Note: SQL cannot run inside the browser. Install PostgreSQL 18 (16 and 17 behave almost identically), load the sample data from lesson 1, and try it there.

Chapter 1 — Getting PostgreSQL running (lessons 1–3)

Connecting with psql, creating a schema and tables, and taking back the row you just inserted with RETURNING. It starts from the fact that what MySQL calls a database is a schema here.

Connecting to PostgreSQL

PostgreSQL has no USE. What MySQL calls a database is closer to a schema here.

Free

Creating schemas and tables

Since PostgreSQL 15 you can no longer create tables in public by default.

Free

Putting data in and getting it back

You never need currval or max(id) to learn a generated id. RETURNING gives it to you.

Free

Chapter 2 — Getting SELECT under control (lessons 4–8)

DISTINCT ON, which writes "one row per category" in a single statement, = ANY(array) so you never build IN placeholders again, how one NULL inside NOT IN empties the result, and how to stop using OFFSET.

The basics of SELECT

"One row per category" is a single statement with DISTINCT ON — no window function needed.

Filtering with WHERE

Stop building IN placeholders. Pass one array to = ANY(array) instead.

LIKE and pattern matching

Indexed email, yet LIKE 'user0123%' still does a Seq Scan. The collation is why.

Working with NULL

WHERE course_id NOT IN (1, 2, NULL) returns nothing — and raises no error.

Ordering and pagination

LIMIT 10 OFFSET 20000 reads 20,010 rows and throws the first 20,000 away.

Chapter 3 — Aggregating and joining (lessons 9–15)

From the FILTER clause that retires counting with CASE WHEN, through GROUP BY and HAVING, duplication in joins, LATERAL, EXISTS, and how WITH stopped being an optimisation fence.

Aggregate functions

You can retire count(CASE WHEN ... THEN 1 END). The FILTER clause exists.

GROUP BY and HAVING

Group by the primary key and the table's other columns need no GROUP BY entry.

Joining tables

40 courses joined to lessons gives 616 rows. Not a bug — that is what a join does.

Outer joins with LEFT JOIN

The same condition gives 2,121 rows in ON and 1,568 in WHERE. This is why totals disagree.

Multiple joins, self joins and LATERAL

A sum inflated 57×. count(DISTINCT ...) fixes the count but never the sum.

Subqueries

If EXPLAIN says loops=2121, that node ran 2,121 times.

UNION, CTEs and views

"CTEs are always materialised in PostgreSQL" ended at 11. Since 12 they inline.

Chapter 4 — Functions and changing data (lessons 16–19)

Window frames, the trap where timestamptz meets BETWEEN, UPSERT with ON CONFLICT and bulk loading with COPY, and the fact that even TRUNCATE can be rolled back.

Window functions

last_value does not give the group's last value — without a frame it returns the current row.

Dates and times

"August's count" written with BETWEEN was 12 rows short. Use a half-open range.

INSERT and UPSERT

SELECT-then-branch upserts break when another request slips in between.

UPDATE and DELETE

PostgreSQL can roll back a TRUNCATE — DDL participates in transactions.

Chapter 5 — Designing so it does not break (lessons 20–25)

Isolation levels and the aborted transaction, a job queue that needs no more than SKIP LOCKED, numeric and uuidv7, what does not apply inside jsonb, constraints and normalisation.

Transactions and isolation levels

One error aborts the whole transaction. That is the safe design, not a nuisance.

Locks and deadlocks

FOR UPDATE SKIP LOCKED is often the whole job queue. Try it before adding one.

Choosing data types

A total off by one yen is usually a type problem: use numeric, never float.

JSONB and arrays

A jsonb column accepts "9800", a misspelled key, and {} alike — no error.

Keys and constraints

"No overlapping bookings" is one EXCLUDE constraint — no exists() check needed.

Normalization

Normalizing is not about saving disk. It is about keeping the data trustworthy.

Chapter 6 — Speed and operations (lessons 26–30)

Index types and when they apply, why EXPLAIN is read through Buffers rather than time, VACUUM and statistics, roles, privileges and backups, and finally driving it from an application with psycopg.

Indexes

It says Index Only Scan and it is still slow — the composite column order is wrong.

Reading query plans with EXPLAIN

Execution Time fluctuates. Judge improvements by BUFFERS instead.

VACUUM and statistics

UPDATE does not rewrite a row. It writes a new one and marks the old one dead.

Roles, privileges and backups

GRANT ON ALL TABLES covers only today's tables — the next migration breaks the app.

Driving PostgreSQL from an application

Whatever a constraint can protect, the application should not re-protect.

Once you have finished all 30 lessons, see the same material through MySQL in Database Fundamentals with MySQL. To drive it from an application, continue with FastAPI or Django. Membership unlocks every course.