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.

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)