How to Use Savepoint in PostgreSQL: Syntax and Examples

In PostgreSQL, use SAVEPOINT to mark a point within a transaction that you can roll back to without aborting the entire transaction. Use ROLLBACK TO SAVEPOINT to undo changes after the savepoint, and RELEASE SAVEPOINT to remove it when no longer needed.

Syntax

The SAVEPOINT command creates a named point in a transaction. You can roll back to this point without ending the whole transaction. The main commands are:

• SAVEPOINT savepoint_name; - creates a savepoint.
• ROLLBACK TO SAVEPOINT savepoint_name; - undoes changes after the savepoint.
• RELEASE SAVEPOINT savepoint_name; - removes the savepoint, making it unavailable for rollback.

sql

BEGIN;
SAVEPOINT sp1;
-- some SQL commands
ROLLBACK TO SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;
COMMIT;

Example

This example shows how to use a savepoint to undo part of a transaction without canceling the whole transaction.

sql

BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 1000);
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Oops, mistake detected, rollback to savepoint
ROLLBACK TO SAVEPOINT sp1;
-- Correct update
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;

Output

INSERT 0 1
SAVEPOINT
UPDATE 1
ROLLBACK
UPDATE 1
COMMIT

Common Pitfalls

Common mistakes when using savepoints include:

• Trying to rollback to a savepoint that does not exist or was released, which causes an error.
• Not releasing savepoints, which can clutter transaction state.
• Using savepoints outside of a transaction block, which is invalid.

Always ensure SAVEPOINT commands are inside BEGIN and COMMIT blocks.

sql

BEGIN;
ROLLBACK TO SAVEPOINT sp1; -- Error: no such savepoint
COMMIT;

Output

ERROR:  no such savepoint

Quick Reference

Command	Description
SAVEPOINT savepoint_name;	Create a savepoint inside a transaction.
ROLLBACK TO SAVEPOINT savepoint_name;	Undo changes after the savepoint.
RELEASE SAVEPOINT savepoint_name;	Remove the savepoint.
BEGIN;	Start a transaction block.
COMMIT;	End and save the transaction.

Key Takeaways

Use SAVEPOINT to mark a point in a transaction for partial rollback.

ROLLBACK TO SAVEPOINT undoes changes after the savepoint without aborting the whole transaction.

Always use savepoints inside a transaction block started with BEGIN.

Release savepoints when done to keep transaction state clean.

Trying to rollback to a non-existent savepoint causes an error.