SAVEPOINT marks a point within the current transaction that you can roll back to later.
Synopsis
SAVEPOINT savepoint_nameParameters
savepoint_name: The name to assign to the savepoint. If a savepoint with the same name already exists in the transaction, the old savepoint becomes inaccessible until the newer one is released.
Usage notes
Savepoints can only be created inside a transaction block (after
BEGIN).A single transaction can contain multiple savepoints.
Use ROLLBACK to undo all commands executed after a savepoint. Use
RELEASE SAVEPOINTto destroy a savepoint and keep its changes.
Examples
Roll back to a savepoint
The following example creates a savepoint after the first insert, rolls back to it (discarding the second insert), then completes a third insert.
BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (3);
COMMIT;The transaction inserts values 1 and 3, but not 2.
Release a savepoint
The following example creates a savepoint and then releases it, committing all changes.
BEGIN;
INSERT INTO table1 VALUES (3);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (4);
RELEASE SAVEPOINT my_savepoint;
COMMIT;The transaction inserts both 3 and 4.