Savepoints — Complete Guide
Savepoints — Complete Guide: free step-by-step lesson with examples, common mistakes, and interview tips — part of PostgreSQL Tutorial on Toolliyo Academy.
On this page
PostgreSQL Tutorial · Lesson 36 of 100
Savepoints
SQL → Advanced
SQL · 1 — Queries · ~6 min · PostgreSQL — Transactions & MVCC
What is this?
SAVEPOINT name creates a nested checkpoint inside a transaction. ROLLBACK TO SAVEPOINT undoes work after that point without aborting the whole transaction.
Why should you care?
PostgresVerse bulk import tries each row — failure on row 500 rolls back to savepoint, not entire 499 successful rows.
See it live — copy this example
Run in pgAdmin or psql.
BEGIN;
INSERT INTO staging_orders (order_ref) VALUES ('ORD-100');
SAVEPOINT row_2;
INSERT INTO staging_orders (order_ref) VALUES ('ORD-100'); -- duplicate
ROLLBACK TO SAVEPOINT row_2;
INSERT INTO staging_orders (order_ref) VALUES ('ORD-101');
COMMIT;What happened?
- First insert succeeds.
- Duplicate violates UNIQUE — instead of full ROLLBACK, ROLLBACK TO SAVEPOINT row_2 clears only the failed insert.
- Third insert commits with first.
Practice next
- CREATE UNIQUE on order_ref in staging_orders.
- Run script step by step in psql.
- Try RELEASE SAVEPOINT row_2 after success path.
- Chain three savepoints and rollback to middle one.
- Combine with exception block in PL/pgSQL later.
Remember
Savepoints partial undo within one transaction. Useful for batch error handling. RELEASE SAVEPOINT frees nested point when done.
PostgresVerse CSV ingest
Nightly job processes 10k partner orders; bad rows skipped via savepoint per row.
Outcome: Valid rows commit; ops gets error report for failures only.
Interview prep for this lesson
Practice these questions aloud after reading—each links to a full structured answer.
Sign in to ask a question or upvote helpful answers.
No questions yet — be the first to ask!
