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.
savepoint_create ::= SAVEPOINT name
The name of your savepoint.
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)