Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

06

Chapter 6

INSERT, UPDATE, DELETE

Changing data safely. INSERT, UPDATE, DELETE, UPSERT, RETURNING, and the patterns that keep history intact.

In this chapter

15

queries, easy → advanced

  1. 001INSERT: single row into employeesRead
  2. 002INSERT: multiple rows at onceRead
  3. 003INSERT FROM SELECT: copy data between tablesRead
  4. 004UPDATE: give 10% raise to engineering departmentRead
  5. 005UPDATE with JOIN (UPDATE FROM syntax)Read
  6. 006DELETE with JOIN conditionRead
  7. 007Conditional INSERT with ON CONFLICT DO NOTHINGRead
  8. 008MERGE statement (PostgreSQL 15+)Read
  9. 009Batch UPDATE with CTE for complex logicRead
  10. 010INSERT with RETURNING to get generated IDRead
  11. 011UPDATE: change order status to shippedRead
  12. 012Soft delete: mark as deleted instead of removingRead
  13. 013Bulk INSERT with CTE and SELECTRead
  14. 014Transactional multi-table INSERTRead
  15. 015Conditional aggregate UPDATE (normalize data)Read