Skip to the main content
Pgnaut

account

Sign in or create an account

A sign-in link goes to your email. No password to remember.

ENES

Fleet training command

Learn PostgreSQL properly.
In your browser. For free.

A real PostgreSQL 18 server compiled to WebAssembly runs inside this tab. Your queries never leave it, no account is needed, and every lesson gets its own throwaway database, so you can break things on purpose. The path does not stop at joins. It goes through window frames, recursive CTEs, JSONB, upserts, query plans and PL/pgSQL.

begin training →0 / 105 objectives cleared

Available now

 

  1. 01

    Signal Acquisition

    Pull rows out of a table and bend them to your will.

    0/8

    SELECT · WHERE · ORDER BY · NULL · LIKE · DISTINCT

    What you will learn

    • · Read any table with SELECT, WHERE, ORDER BY and LIMIT
    • · Reason correctly about NULL and three-valued logic
    • · Filter on ranges and set membership
    • · Match text with LIKE and ILIKE, and know when a pattern stops being cheap
  2. 02

    Instruments & Units

    Text, numbers, dates and the casts between them.

    0/4

    text functions · dates · intervals · CAST · CASE

    What you will learn

    • · Manipulate text with concatenation and the standard string functions
    • · Truncate, extract from and do arithmetic on dates and intervals
    • · Cast deliberately, and branch with CASE
  3. 03

    Fleet Statistics

    Collapse many rows into one number. Correctly.

    0/5

    GROUP BY · HAVING · FILTER · ROLLUP · GROUPING SETS

    What you will learn

    • · Group rows and aggregate them with COUNT, SUM, AVG, MIN, MAX
    • · Filter groups with HAVING and understand why it is not WHERE
    • · Pivot data with aggregate FILTER clauses
    • · Produce subtotals and grand totals with ROLLUP, CUBE and GROUPING SETS
  4. 04

    Docking Procedures

    Combine tables without losing rows you meant to keep.

    0/5

    INNER JOIN · LEFT JOIN · self-join · anti-join · NOT IN

    What you will learn

    • · Use INNER, LEFT, RIGHT, FULL and CROSS joins deliberately
    • · Write self-joins and anti-joins
    • · Avoid the NOT IN / NULL trap and the aggregate-after-LEFT-JOIN trap
  5. 05

    Nested Transmissions

    Queries inside queries, and the set algebra between them.

    0/6

    EXISTS · correlated · UNION · EXCEPT · DISTINCT ON · LATERAL

    What you will learn

    • · Write scalar, correlated and EXISTS subqueries
    • · Combine result sets with UNION, INTERSECT and EXCEPT
    • · Use DISTINCT ON and LATERAL for per-group problems
  6. 06

    Route Plotting

    Name your intermediate results. Then let them call themselves.

    0/5

    WITH · recursive CTE · generate_series

    What you will learn

    • · Structure complex queries with WITH
    • · Walk hierarchies and graphs with recursive CTEs
    • · Generate series and control recursion depth safely
  7. 07

    Sensor Windows

    Aggregate without collapsing: the chapter the free courses skip.

    0/8

    OVER · PARTITION BY · LAG · LEAD · ROWS · RANGE · GROUPS

    What you will learn

    • · Compute aggregates alongside detail rows with OVER
    • · Partition a window and rank within it
    • · Reach across rows with LAG and LEAD
    • · Control the frame, and know what the default frame really does
    • · Name a window once with WINDOW, reuse it, and build one window on another
    • · Choose between ROWS, RANGE and GROUPS frame modes
  8. 08

    Hull Design

    Schemas that make bad data impossible.

    0/9

    CREATE TABLE · CHECK · foreign keys · GENERATED · UNIQUE · IDENTITY

    What you will learn

    • · Create tables with appropriate types and keys
    • · Enforce rules with NOT NULL, CHECK and foreign keys
    • · Choose referential actions deliberately
    • · Derive columns with GENERATED ALWAYS AS
    • · Guarantee uniqueness with UNIQUE, across one column or several, and know how it treats nulls
    • · Hand out key values with GENERATED AS IDENTITY, and repair a sequence that has fallen behind
    • · Name a rule once with CREATE DOMAIN and reuse it across every column that needs it
    • · Model a closed, ordered set of labels with CREATE TYPE ... AS ENUM, extend it with ALTER TYPE ... ADD VALUE, and recognise the set that belongs in a lookup table instead
    • · Bundle several fields into one value with CREATE TYPE ... AS (...), read them back with (col).field, and know when two plain columns are the better answer
  9. 09

    Pattern Recognition

    The analytical shapes that recur in every real job.

    0/6

    running totals · top N per group · gaps and islands · dedupe · period over period

    What you will learn

    • · Compute running totals that reset, and express them as a share
    • · Choose a moving window by time rather than by row count
    • · Take the top N rows of every group in one pass
    • · Collapse consecutive runs into islands, and find the gaps between them
    • · Reduce a table to one row per key, and name the rows you would delete
    • · Compare a period against the one before it without losing the empty periods
  10. 10

    Cargo Manifests

    Semi-structured data, natively.

    0/7

    JSONB · ->> · @> · jsonpath · arrays · GIN

    What you will learn

    • · Extract JSONB values as jsonb or as text, and know which you have
    • · Reach into nested objects with path operators
    • · Ask containment and existence questions with @> and ?
    • · Filter inside the document with jsonpath
    • · Work with array columns without pretending they are strings
    • · Index JSONB and arrays with GIN, so a containment query can use the index
    • · Model a span as a half-open range that tiles, and detect a clash with &&
    • · Make non-overlap a database guarantee with an exclusion constraint
  11. 11

    Writing to the Log

    Changing data without losing any.

    0/6

    INSERT · UPDATE · DELETE · RETURNING · ON CONFLICT · MERGE

    What you will learn

    • · Insert, update and delete rows deliberately
    • · See what a statement did with RETURNING, in one round trip
    • · Resolve collisions with ON CONFLICT and excluded
    • · Drive a whole set of changes from one MERGE
  12. 12

    Engine Efficiency

    Ask the planner what it is about to do, and understand the answer.

    0/6

    EXPLAIN · EXPLAIN ANALYZE · scans · indexes · pg_stats

    What you will learn

    • · Read an EXPLAIN plan as a tree of nodes
    • · Tell an estimate from a measurement with EXPLAIN ANALYZE
    • · Recognise sequential, index and bitmap heap scans, and know when each is right
    • · Build btree, partial and expression indexes, and check the planner actually uses them
    • · Refresh statistics with ANALYZE and read the planner's raw inputs from pg_stats
  13. 13

    Airlock Protocol

    Transactions, and what isolation actually buys you.

    0/6

    BEGIN · COMMIT · SAVEPOINT · isolation levels · locks · deadlocks

    What you will learn

    • · Group statements into one all-or-nothing unit with BEGIN, COMMIT and ROLLBACK
    • · Undo part of a transaction with savepoints, without losing the rest
    • · Name the four anomalies and say which isolation level rules out which
    • · Choose an isolation level deliberately, and write the retry loop SERIALIZABLE requires
    • · Read what a row lock is doing, and know why deadlocks happen and how to avoid them
  14. 14

    Ship Automation

    Logic that lives in the database.

    0/8

    functions · PL/pgSQL · triggers · EXCEPTION · SQLSTATE

    What you will learn

    • · Write SQL functions that take arguments and return scalars or whole tables
    • · Mark a function IMMUTABLE, STABLE or VOLATILE, and know what you promised
    • · Use PL/pgSQL variables, IF/ELSIF and loops to express what SQL alone cannot
    • · Build a row trigger and an audit table, and read NEW and OLD inside it
    • · Catch errors with EXCEPTION WHEN and branch on SQLSTATE rather than on message text
    • · Raise your own SQLSTATE, and write the retry loop a serialization failure demands
  15. 15

    Postgres Proper

    The features that are the reason to pick Postgres.

    0/7

    full-text search · partitions · row-level security · GRANT

    What you will learn

    • · Search prose with tsvector, tsquery and the @@ operator, and rank the hits
    • · Back a full-text search with a GIN index on the right expression
    • · Split a table into range partitions and watch the planner skip the ones it does not need
    • · Filter rows per role with row-level security, and prove it under a non-superuser
    • · Grant and revoke table and column privileges, and inspect them with has_table_privilege
    • · Find out what a given Postgres build actually ships
  16. 16

    Living Schema

    Give a query a name, and change a table nobody will let you take offline.

    0/5

    VIEW · CHECK OPTION · materialized views · ALTER TABLE · migrations

    What you will learn

    • · Name a query with CREATE VIEW, and tell when that beats repeating a CTE
    • · Close the hole a writable view leaves open with WITH CHECK OPTION
    • · Trade freshness for speed with a materialized view, and refresh it deliberately
    • · Add a column to a live table and know whether the table was rewritten
    • · Run the add-nullable, backfill, SET NOT NULL migration and name the lock each step takes
  17. 17

    Life Support

    The systems that keep running when nobody is watching.

    0/4

    MVCC · xmin · bloat · VACUUM · WAL

    What you will learn

    • · Read xmin, xmax and ctid, and say which version of a row you are looking at
    • · Explain why an UPDATE writes a new row version and leaves the old one behind
    • · Measure bloat with pg_relation_size, and tell dead space from live rows
    • · Say what VACUUM reclaims, what it never gives back, and what VACUUM FULL costs
    • · Describe how the write-ahead log makes a COMMIT durable, and watch the log position move