AdvancedAdvanced PostgreSQL

Transactional DDL with SAVEPOINT

The query

SQL

BEGIN;

  SAVEPOINT before_update;

  UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 1;

  -- Check results
  SELECT COUNT(*), AVG(salary) FROM employees WHERE dept_id = 1;

  -- Oops, too much! Roll back just this update
  ROLLBACK TO SAVEPOINT before_update;

  -- Try a smaller raise
  UPDATE employees SET salary = salary * 1.05 WHERE dept_id = 1;

COMMIT;

Tested against PostgreSQL 16

Note

“SAVEPOINTs allow partial rollbacks within a transaction. ROLLBACK TO SAVEPOINT doesn't end the transaction.

Tables referenced

employees10 cols