SAVEPOINT ​

Create a savepoint within the current transaction.

SAVEPOINT name

Within a transaction, commands that are executed after a savepoint may be rolled back. The transaction is restored to its state before the savepoint was established.

You can define a maximum of 1023 savepoints per transaction.

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)