SAVEPOINT
This page documents a preview version.
v2.23 Preview
Preview includes features under active development and is for development and testing only.
For production, use the latest stable version (v2024.1).
For production, use the latest stable version (v2024.1).
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
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)