Learn PostgreSQL by doing. Each lesson is something you run yourself in psql — from spinning up a server and writing SQL to indexes, transactions, functions, and backups.
Before you start
To follow along you need a running PostgreSQL server. The simplest way is to install PostgreSQL — the installer also includes psql, the command-line client you type the examples into. The first lesson walks through it.
Install & Connect with psql
Install PostgreSQL, connect to it with the psql command-line client, create your first database, and learn the everyday psql commands.
Creating Tables & Data Types
Define tables with CREATE TABLE, pick the right PostgreSQL data type for each column, change tables with ALTER TABLE, group them into schemas, and clean up with DROP and TRUNCATE.
Inserting, Updating & Deleting Rows
Add rows with INSERT, get generated values back with RETURNING, change rows with UPDATE, remove them with DELETE, and handle duplicates cleanly with an ON CONFLICT upsert.
Querying Data with SELECT
Read data with SELECT — filter rows with WHERE, match patterns with LIKE/ILIKE, sort and paginate with ORDER BY and LIMIT, remove duplicates with DISTINCT, and handle NULLs with COALESCE.
Joining Tables
Combine rows from related tables — INNER JOIN to keep matches, LEFT/RIGHT/FULL joins to keep unmatched rows too, plus self joins and cross joins, all driven by a foreign key relationship.
Aggregation & Grouping
Summarize data with COUNT, SUM, AVG, MIN and MAX, collapse rows into groups with GROUP BY, filter those groups with HAVING, and aggregate selectively with FILTER.
Constraints & Keys
Protect your data with rules the database enforces — PRIMARY KEY, FOREIGN KEY with ON DELETE actions, UNIQUE, NOT NULL, DEFAULT, CHECK, and generated columns.
Normalization
Design tables that avoid duplicated, inconsistent data — see the anomalies a flat table creates, reach first, second, and third normal form by splitting into related tables linked by foreign keys, and know when to denormalize on purpose.
Subqueries, CTEs & Set Operations
Nest queries inside queries with subqueries and EXISTS, make complex SQL readable with WITH (CTEs), walk hierarchies with recursive CTEs, and combine result sets with UNION, INTERSECT and EXCEPT.
Window Functions
Run calculations across related rows without collapsing them — number and rank rows with ROW_NUMBER and RANK, look at neighbours with LAG and LEAD, and build running totals with SUM OVER.
Transactions
Group several statements into one all-or-nothing unit with BEGIN, COMMIT and ROLLBACK, undo part of the work with SAVEPOINT, and control how concurrent transactions see each other with isolation levels.
Indexes & Their Use Cases
Speed up queries with indexes — the everyday B-tree, unique and multicolumn indexes, partial and expression indexes, plus GIN for JSON and full-text search and BRIN for huge time-ordered tables.
Reading Query Plans with EXPLAIN
See how PostgreSQL runs a query with EXPLAIN, measure it for real with EXPLAIN ANALYZE, tell a slow sequential scan from a fast index scan, keep stats fresh with ANALYZE and VACUUM, and find slow queries with pg_stat_statements.
Functions, Procedures & Triggers
Move logic into the database — write reusable SQL and PL/pgSQL functions, run multi-step procedures with CALL, and fire automatic actions on data changes with triggers.
Bulk Loading & Partitioning
Load and export data fast with COPY and \copy, then split a very large table into manageable pieces with declarative range partitioning.
Users, Roles & Security
Control who can do what — create roles and login users, grant and revoke privileges, set defaults for future tables, allow connections in pg_hba.conf, and restrict rows per user with row-level security.
Backups & Restore
Protect your data — back up a database with pg_dump, restore it with pg_restore or psql, capture roles and all databases with pg_dumpall, and understand when to reach for a physical base backup.