EDB PostgreSQL Associate: Data Manipulation and Querying

Changing data

INSERT ... ON CONFLICT (key) DO UPDATE SET ... is the upsert; DO NOTHING silently skips conflicts. RETURNING returns generated values in the same statement. UPDATE ... FROM other and DELETE ... USING other join another relation - and without a WHERE you rewrite or delete every row, a classic trap. TRUNCATE is fast, takes an ACCESS EXCLUSIVE lock, and is transactional in PostgreSQL.

Query features

• CTEs (WITH) acted as optimisation fences before v12; from v12, side-effect-free non-recursive CTEs are inlined unless marked MATERIALIZED.
• Window functions (row_number(), rank(), lag(), sum() OVER (...)) run after grouping and cannot appear in WHERE - wrap them in a subquery.
• DISTINCT ON (col) keeps the first row per group and requires a matching leading ORDER BY.
• GROUPING SETS, ROLLUP and CUBE produce subtotals containing NULLs; GROUPING() tells real NULLs from generated ones.
• LATERAL lets a subquery reference columns to its left - the clean way to write "top N per group".
• EXISTS is safer than IN when the subquery can produce NULLs.

Traps

WHERE filters rows before aggregation and HAVING after, so an aggregate cannot appear in WHERE. count(*) counts rows while count(col) skips NULLs. LIMIT without ORDER BY returns an arbitrary, non-repeatable set.

Rule of thumb

For "latest row per group", reach for DISTINCT ON or a LATERAL join rather than a correlated max() subquery.