A Safe Workflow for Data Changes

Transactions as an undo button

Run manual data changes inside an explicit transaction: BEGIN, run the change, verify with SELECTs or RETURNING, then COMMIT if correct or ROLLBACK if not. Back up or snapshot important tables first, batch very large updates to limit lock time, and make scripts idempotent so they can be rerun. In production, prefer reviewed migration scripts over ad-hoc statements.

Previewing an update and rolling it back, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). The preview shows the January refund would also be archived, so the change is rolled back and the original counts remain.

BEGIN;
UPDATE orders SET status = 'archived' WHERE ordered_on < '2026-02-01';
SELECT status, count(*) FROM orders GROUP BY status ORDER BY status;
ROLLBACK;  -- the preview looked wrong (refunds would be archived too), so undo

SELECT status, count(*) FROM orders GROUP BY status ORDER BY status;

Output:

  status   | count 
-----------+-------
 archived  |     3
 cancelled |     1
 paid      |     3
(3 rows)

  status   | count 
-----------+-------
 cancelled |     1
 paid      |     5
 refunded  |     1
(3 rows)

Beware autocommit

Most clients commit each statement immediately unless you start a transaction explicitly.

त्वरित जाँच: What does ROLLBACK do after an UPDATE inside BEGIN?

• Commits the changes
• Undoes all changes made since BEGIN
• Deletes the table
• Repeats the update