ROLLBACK TO
Synopsis
Use the ROLLBACK TO SAVEPOINT statement to revert the state of the transaction to a previously established savepoint. This can be particularly useful to handle and unwind errors like key/index constraint violations.
Syntax
savepoint_rollback ::= ROLLBACK [ WORK | TRANSACTION ] TO
[ SAVEPOINT ] name
savepoint_rollback
Semantics
begin
ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] name
NAME
The name of the savepoint to which you wish to roll back.
Examples
Create a sample table and add one row to start.
CREATE TABLE sample(k int PRIMARY KEY, v int);
INSERT INTO sample(k, v) VALUES (1, 2);
Begin a transaction and insert some rows.
BEGIN TRANSACTION;
INSERT INTO sample(k, v) VALUES (3, 4);
Now, create a savepoint before inserting a duplicate row for k=1:
SAVEPOINT test;
INSERT INTO sample(k, v) VALUES (1, 3);
You should get the following error:
ERROR: duplicate key value violates unique constraint "k_pkey"
Any other operations should error, since the transaction is now in a bad state:
SELECT * FROM sample;
ERROR: current transaction is aborted, commands ignored until end of transaction block
However, you can roll back to our earlier savepoint and continue with the transaction without losing our earlier insert:
ROLLBACK TO test;
INSERT INTO sample(k, v) VALUES (5, 6);
COMMIT;
If you check the rows in the table, you will see the row you inserted before the primary key violation, as well as the one you inserted after roll back:
SELECT * FROM sample;
k | v
----+----
1 | 2
3 | 4
5 | 6
(3 rows)