SAVEPOINT
Synopsis
Use the SAVEPOINT statement to define a new savepoint within the current transaction. A savepoint is a special "checkpoint" inside a transaction that allows all commands that are executed after it was established to be rolled back, restoring the transaction state to what it was at the time of the savepoint.
Syntax
savepoint_create ::= SAVEPOINT name
savepoint_create
Semantics
begin
SAVEPOINT name
NAME
The name of your savepoint.
Examples
Create a sample table.
CREATE TABLE sample(k int PRIMARY KEY, v int);
Begin a transaction and insert some rows.
BEGIN TRANSACTION;
INSERT INTO sample(k, v) VALUES (1, 2);
SAVEPOINT test;
INSERT INTO sample(k, v) VALUES (3, 4);
Now, check the rows in this table:
SELECT * FROM sample;
k | v
----+----
1 | 2
3 | 4
(2 rows)
And then, rollback to savepoint test and check the rows again. Note that the second row does not appear:
ROLLBACK TO test;
SELECT * FROM sample;
k | v
----+----
1 | 2
(1 row)
We can even add a new row at this point. If we then commit the transaction, only the first and third row inserted will persist:
INSERT INTO sample(k, v) VALUES (5, 6);
COMMIT;
SELECT * FROM SAMPLE;
k | v
----+----
1 | 2
5 | 6
(2 rows)