PostgreSQL from the ground up

Learn PostgreSQL by writing it: tables and queries, the types that fit real data, NULL and defaults, RETURNING and upserts, keys and constraints, joins, groups and totals, dates, subqueries and CTEs, window functions, indexes and query plans — ending with a shop report that uses all of it.

What You'll Learn

• Create tables, insert rows, and read them back with SELECT
• Filter, sort and cut results with WHERE, IN, ILIKE, ORDER BY and LIMIT
• Choose types that fit — numeric for money, timestamptz for instants, jsonb for the rest
• Handle NULL deliberately, with COALESCE and NULLIF
• Write changes that report back, and upsert with ON CONFLICT
• Let the database keep the rules: primary keys, CHECK, UNIQUE and foreign keys
• Join tables — inner, left, and the anti-join that finds what is missing
• Summarise with aggregates, GROUP BY, HAVING and FILTER
• Use CASE, format dates and numbers, and report every period with generate_series
• Name steps in CTEs and reach into queries with subqueries
• Add context without collapsing rows using window functions
• Read an EXPLAIN plan and index a query for the shape it has
• Assemble a shop report with joins, aggregates, CTEs and windows

Prerequisites

• No database experience needed — this course starts at the first table
• The SQL course covers the standard syntax; this one goes deeper into what PostgreSQL adds

Course Content

1. 01
   A real database, and your first table

   What PostgreSQL is, why the engine running in this page is the real thing, and the three statements every database starts with: CREATE TABLE, INSERT, SELECT.

   Beginner · 15 min read

   Not started
2. 02
   Asking narrow questions

   WHERE, the comparison operators, AND and OR, IN and BETWEEN, and the two ways Postgres sorts and cuts a result down to the rows you asked for.

   Beginner · 16 min read

   Not started
3. 03
   Types that fit the data

   Numeric for money and why it is not the same as a float, text and varchar, boolean, dates with and without a time zone, intervals, jsonb, and the casts that move between them.

   Beginner · 17 min read

   Not started
4. 04
   NULL, and the values that are not there

   The third truth value, why nothing equals NULL, and the two functions that turn a missing value into a usable one: COALESCE and NULLIF.

   Beginner · 16 min read

   Not started
5. 05
   Writing, and hearing back

   INSERT, UPDATE and DELETE, the RETURNING clause that hands you the rows you just changed, and the upsert that inserts or updates depending on what is already there.

   Intermediate · 17 min read

   Not started
6. 06
   Rules the database keeps

   Primary keys and generated ids, unique columns, CHECK rules, and foreign keys that refuse to leave an order pointing at a customer who is not there.

   Intermediate · 18 min read

   Not started
7. 07
   Joins: asking across tables

   Rows matched by key: INNER JOIN keeps the matches, LEFT JOIN keeps everything on one side, and the anti-join pattern that finds what is missing.

   Intermediate · 18 min read

   Not started
8. 08
   Totals, and the groups they belong to

   COUNT, SUM, AVG, MIN and MAX, the GROUP BY that turns one answer into one per category, HAVING for the groups themselves, and FILTER for the ones you would rather leave out.

   Intermediate · 17 min read

   Not started
9. 09
   Expressions, CASE, and dates that behave

   CASE as SQL's if, the text and date functions worth knowing, and the calendar trick that gives quiet months a row of zeros instead of a hole in the report.

   Advanced · 18 min read

   Not started
10. 10
    Queries inside queries, named for reading

    Scalar subqueries, IN and EXISTS, and the WITH clause that lets a long query read like a short story with one step per sentence.

    Advanced · 18 min read

    Not started
11. 11
    Window functions: the same rows, with context

    Ranks, running totals and comparisons with the previous row — computed across a set of rows without collapsing them, which is the one thing GROUP BY cannot do.

    Advanced · 19 min read

    Not started
12. 12
    Indexes, and asking the planner

    What an index is, when PostgreSQL uses one and when it rightly refuses, and how to read EXPLAIN — the feature that turns a slow query into a described problem.

    Advanced · 18 min read

    Not started
13. 13
    Putting it together: a shop report

    A schema with real constraints, a month of orders, and three report queries that use joins, aggregates, CTEs and window functions together.

    Advanced · 22 min read

    Not started