Chapter 13 — Key Takeaways

The big idea

DML changes data, so habits matter as much as syntax. Reading is safe; writing can corrupt or destroy. The professional reflex: preview the WHERE with a SELECT, wrap in a transaction, verify, then commit.

INSERT

  • Name your columns: INSERT INTO t (a, b) VALUES (...) — robust and readable.
  • Multi-row INSERT and INSERT ... SELECT are efficient; omitted columns take their defaults.
  • RETURNING hands back modified rows (e.g., the generated id) — no second query.

UPDATE / DELETE

  • UPDATE t SET ... WHERE ...; DELETE FROM t WHERE .... UPDATE ... FROM updates using another table.
  • ⚠️ No WHERE = every row. The canonical disaster. (Case Study 1.)
  • They respect foreign keys (a restricting FK blocks an unsafe delete).

TRUNCATE vs DELETE

  • TRUNCATE empties a whole table fast (no WHERE), can RESTART IDENTITY, fires no row triggers. Use only when you want the whole table gone (staging/temp).
  • DELETE ... WHERE for selective removal.

UPSERT

  • INSERT ... ON CONFLICT (cols) DO UPDATE SET x = EXCLUDED.x — atomic insert-or-update.
  • DO NOTHING = insert-if-absent. EXCLUDED = the row you tried to insert.
  • Fixes check-then-insert race conditions and makes loads idempotent (safe to re-run). (Case Study 2.)

Transactions = your undo button (Ch. 26 preview)

BEGIN;  <change>  SELECT ...verify...;  COMMIT;   -- or ROLLBACK to undo
  • Wrap risky changes; verify before COMMIT.
  • Consider \set AUTOCOMMIT off in production psql.

The safe sequence (memorize)

  1. Run the WHERE as a SELECT (check the row count). 2. BEGIN. 3. Run the change. 4. Verify with a SELECT. 5. COMMIT (or ROLLBACK).

Defense in depth (Case Study 1)

Transactions + least-privilege access + reviewed migrations (not ad-hoc edits) + tested backups. Don't rely on "I'll be careful."

Performance (Ch. 23 & 31 preview)

Multi-row INSERT/COPY ≫ many single inserts. Indexes speed the WHERE but slow every write (they must be maintained). Batch huge UPDATE/DELETE on busy tables.

You can now…

  • ☐ INSERT (single/multi/SELECT) and capture output with RETURNING.
  • ☐ UPDATE/DELETE safely with a verified WHERE, inside a transaction.
  • ☐ Choose DELETE vs TRUNCATE.
  • ☐ Write atomic, idempotent upserts with ON CONFLICT.
  • ☐ Use BEGIN/COMMIT/ROLLBACK as an undo button.

Looking ahead

Chapter 14 — Data Definition. CREATE/ALTER/DROP, PostgreSQL data types and constraints in depth, sequences, and schemas — building the well-constrained tables you've been modifying.

One sentence to carry forward: Before any UPDATE or DELETE, run the WHERE as a SELECT and wrap the change in a transaction — that one habit prevents the most expensive mistakes in databases.