Product Documentation

Appearance

All platforms

ROLLBACK TO SAVEPOINT ​

Roll back the commands in the current transaction that were executed after the named savepoint.

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name

The savepoint remains valid and can be rolled back to again later, if needed. The ROLLBACK TO SAVEPOINT command destroys all savepoints that were defined after the named savepoint.

For example, the following transaction is rolled back to savepoint insert1 when a subsequent INSERT command within the transaction returns an error. Therefore, the row inserted into the season table is committed when the transaction ends. Without the savepoint that insert would have been rolled back as well.

premdb=# begin;
BEGIN
premdb=# insert into season(seasonid) values(100);
INSERT 0 1
premdb=# savepoint insert1;
SAVEPOINT
premdb=# insert into newseason(seasonid) values(100);
ERROR:  relation "newseason" does not exist
LINE 1: insert into newseason(seasonid) values(100);
                   ^
premdb=# rollback to savepoint insert1;
ROLLBACK
premdb=# commit;
COMMIT
premdb=# select * from season where seasonid=100;
 seasonid | season_name | numteams | winners 
----------+-------------+----------+---------
     100 | [NULL]      |   [NULL] | [NULL]
(1 row)