PostgreSQL Queries

Overview

• Purpose: Hub for PostgreSQL query notes — interview prep and day-to-day work
• Approach: Every note uses one shared e-commerce dataset from E-commerce Practice Queries, so examples can be pasted into a scratch database and run as-is
• Verified Against: PostgreSQL 17 — features from newer/older versions are labelled (e.g. “PG 15+”)
• Theory Lives In: Backend/Databases Concepts — ACID, Database Indexing, Concurrency Control, Database Normalisation, Database Replication, Database Partition
• Mind Map: Backend/Database/MindMap.canvas

Quick Setup

docker run --rm -d --name pg-practice -e POSTGRES_PASSWORD=x -p 55432:5432 postgres:17
docker exec -it pg-practice psql -U postgres
# paste the "Sample Schema" and "Seed Data" blocks from E-commerce Practice Queries

Recommended Study Order

1. Fundamentals

1. DDL Data Types and Constraints — CREATE/ALTER/DROP, identity vs serial, text vs varchar, numeric, timestamptz, uuid, jsonb, arrays, PK/FK/UNIQUE/CHECK
2. SELECT Fundamentals — logical processing order, WHERE, ORDER BY, OFFSET vs keyset pagination, DISTINCT ON, NULL / three-valued logic, CASE
3. Aggregation and GROUP BY — GROUP BY, HAVING, FILTER, GROUPING SETS / ROLLUP / CUBE, string_agg / array_agg, fan-out

2. Combining Data

4. Joins — inner/outer/cross/self joins, ON vs WHERE, semi/anti joins, NOT IN pitfall, LATERAL
5. Subqueries and CTEs — correlated subqueries, EXISTS/IN/ANY/ALL, WITH, recursive CTEs, MATERIALIZED
6. Window Functions — ROW_NUMBER/RANK/DENSE_RANK, LAG/LEAD, running totals, ROWS vs RANGE, top-N, gaps & islands

3. Writing Data and Concurrency

7. Inserts Updates Upserts and MERGE — RETURNING, ON CONFLICT, UPDATE … FROM, DELETE … USING, MERGE, COPY
8. Transactions and Locking — BEGIN/SAVEPOINT, isolation levels, FOR UPDATE, SKIP LOCKED job queue, advisory locks

4. Performance and Operations

9. Indexes and Query Performance — B-tree/partial/expression/INCLUDE/GIN, CONCURRENTLY, EXPLAIN ANALYZE, anti-patterns
10. Admin and Inspection Queries — pg_stat_activity, killing queries, locks, pg_stat_statements, sizes, vacuum health

5. Postgres Features

11. JSONB and Arrays — ->, ->>, @>, ?, JSONPath, SQL/JSON (PG 17), GIN indexing, array operators

12. Views Functions and Triggers — views, materialized views + REFRESH CONCURRENTLY, SQL/PL/pgSQL functions, procedures, triggers

13. UUID Versions — v1–v8, why v4 hurts B-tree PKs (measured), v7 for keys, uuidv7() (PG 18), v7 function for PG 17

6. Practice

14. E-commerce Practice Queries — schema, seed data, 20 graded questions (easy → hard) with solutions

Note Map

#	Note	Group	Key Interview Topics
1	DDL Data Types and Constraints	Fundamentals	serial vs identity, FK ON DELETE, NULL in CHECK/UNIQUE
2	SELECT Fundamentals	Fundamentals	Processing order, keyset pagination, NULL logic
3	Aggregation and GROUP BY	Fundamentals	WHERE vs HAVING, FILTER, ROLLUP
4	Joins	Combining	LEFT JOIN filters, NOT IN vs NOT EXISTS, LATERAL
5	Subqueries and CTEs	Combining	Correlated subqueries, recursive org chart
6	Window Functions	Combining	Top-N per group, running totals, LAG
7	Inserts Updates Upserts and MERGE	Writing	UPSERT, MERGE, bulk load
8	Transactions and Locking	Writing	Isolation levels, SKIP LOCKED, lost updates
9	Indexes and Query Performance	Performance	Why isn’t my index used, EXPLAIN
10	Admin and Inspection Queries	Performance	Find & kill blockers, slow query log
11	JSONB and Arrays	Features	json vs jsonb, GIN
12	Views Functions and Triggers	Features	Matview refresh, trigger use cases
13	UUID Versions	Features	UUIDv4 vs v7 as PK, bigint vs UUID
14	E-commerce Practice Queries	Practice	All of the above

Postgres vs Standard SQL Cheat Sheet

Feature	Postgres	Standard / Other DBs
Auto IDs	GENERATED … AS IDENTITY (or legacy serial)	Identity (std), AUTO_INCREMENT (MySQL)
Return Written Rows	RETURNING on INSERT/UPDATE/DELETE/MERGE	OUTPUT (SQL Server), none in MySQL
Upsert	ON CONFLICT DO UPDATE	MERGE (std), ON DUPLICATE KEY (MySQL)
First Row per Group	DISTINCT ON	ROW_NUMBER()
Case-insensitive LIKE	ILIKE	lower(x) LIKE …
Conditional Aggregate	FILTER (WHERE …) (std, MySQL lacks it)	sum(CASE …)
Casting	x::int	CAST(x AS int)
NULL-safe Equality	IS [NOT] DISTINCT FROM	<=> (MySQL)
Transactional DDL	Yes	No in MySQL/Oracle
Arrays / JSONB	First-class types with indexes	Limited

Summary

• Start: Load the practice schema, then work through the notes in order 1 → 13
• Interview Focus: NULL logic, joins vs anti-joins, window functions, CTEs, UPSERT, isolation & locking, index anti-patterns
• Real Work Focus: Keyset pagination, RETURNING, SKIP LOCKED, CREATE INDEX CONCURRENTLY, EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements