PostgreSQL

Past the basics into what PostgreSQL interviews actually probe — NULL logic, LATERAL, recursive CTEs, window frames, JSONB, UPSERT, indexes, EXPLAIN, MVCC and locking.

Start the course Practise this with real questions

In this course

Types and casting, where the arithmetic quietly goes wrongInteger division, numeric versus float, the :: cast, and the casts that truncate or fail instead of doing what you meant.8 min
NULL and three-valued logicWhy NULL = NULL is not true, how NOT IN returns nothing, and what aggregates, sorting and UNIQUE do with missing values.9 min
Constraints and normalizationLet the schema refuse bad data: keys, CHECK, UNIQUE, foreign-key actions, and the normal forms that stop one fact being stored twice.10 min
Joins beyond the basics: LATERAL, DISTINCT ON, semi- and anti-joinsTop-N per group with LATERAL, latest row per group with DISTINCT ON, and why EXISTS beats a join when you only need to know a match exists.10 min
Aggregates in depth: FILTER, ordered aggregates and ROLLUPConditional counts with FILTER, string_agg with its own ORDER BY, medians, and subtotals with ROLLUP that you can tell apart from real NULLs.10 min
Dates, intervals and generate_seriesDate arithmetic that surprises people, half-open ranges instead of BETWEEN, and filling the gaps in a time report with generate_series.10 min
CTEs: naming steps, materialization, and writing in WITHWhat WITH is for, when PostgreSQL inlines a CTE and when it materializes it, and data-modifying CTEs that move rows in one statement.9 min
Recursive CTEs for trees and graphsHow WITH RECURSIVE actually iterates, walking a hierarchy with depth and path, and stopping on cycles with UNION or the CYCLE clause.11 min
Window functions: ranking, offsets and framesrow_number, rank and dense_rank, lag and lead, and the default frame that makes running totals and last_value give surprising answers.12 min
JSONB and arraysReading JSONB with -> and ->>, containment with @>, expanding arrays into rows, and when a document column is the wrong design.11 min
UPSERT with ON CONFLICT, and RETURNINGInsert-or-update in one race-free statement, what EXCLUDED means, the errors upserts actually raise, and getting changed rows back with RETURNING.10 min
Functions, procedures and triggersSQL and PL/pgSQL functions, what IMMUTABLE, STABLE and VOLATILE promise, procedures that can commit, and triggers that fire only on a real change.12 min
Indexes: B-tree, multicolumn, partial, expression and GINWhich index a query can use and why: leftmost prefixes, sorted reads without a Sort, partial and expression indexes, and GIN for JSONB and arrays.12 min
Reading EXPLAIN and tuning a slow queryWhat cost, rows and loops mean, how to spot a bad estimate, extended statistics, and rewriting predicates so an index can be used.12 min
MVCC, snapshots and isolation levelsHow PostgreSQL keeps old row versions so readers never block writers, what a snapshot is, and what each isolation level really guarantees.12 min
Locking and deadlocksTable and row lock modes, SELECT FOR UPDATE, SKIP LOCKED queues, how PostgreSQL detects a deadlock, and the lock ordering that prevents one.12 min