SAVEPOINT

A transaction command for defining a new savepoint

SAVEPOINT is a transaction command for defining a new savepoint.

SAVEPOINT was added in PostgreSQL 8.0.

Usage

A savepoint is a user-defined marker within a transaction created with the SAVEPOINT command, which can be rolled back to without invalidating the entire transaction.

The savepoint must be named; the name can be arbitrary, but must follow the same rules as for other database objects such as table and column identifiers; see the PostgreSQL documentation section "Identifiers and Key Words" for further details.

Change history

Examples

Basic usage of SAVEPOINT:

postgres=# BEGIN;
BEGIN

postgres=*# SELECT 1;
 ?column?
----------
        1
(1 row)

Time: 0.379 ms
postgres=*# SAVEPOINT s1;
SAVEPOINT

postgres=*# SELECT foo;
ERROR:  column "foo" does not exist
LINE 1: SELECT foo;
               ^

postgres=!# ROLLBACK TO SAVEPOINT s1;
ROLLBACK

postgres=*# SELECT 2;
 ?column?
----------
        2
(1 row)

postgres=*# COMMIT;
COMMIT

Note that savepoints can only be used within transaction blocks:

postgres=# SAVEPOINT s1;
ERROR:  SAVEPOINT can only be used in transaction blocks

A savepoint cannot of course be established after a transaction error:

postgres=# BEGIN;
BEGIN

postgres=*# SELECT foo;
ERROR:  column "foo" does not exist
LINE 1: SELECT foo;
               ^
postgres=!# SAVEPOINT foo;
ERROR:  current transaction is aborted, commands ignored until end of transaction block

Valid savepoint names:

postgres=*# SAVEPOINT foo;
SAVEPOINT

postgres=*# SAVEPOINT _foo;
SAVEPOINT

postgres=*# SAVEPOINT foo_bar_1;
SAVEPOINT

postgres=*# SAVEPOINT über1;
SAVEPOINT

postgres=*# SAVEPOINT ใƒ†ใ‚นใƒˆ; SAVEPOINT
postgres=*# SAVEPOINT "123"; SAVEPOINT postgres=*# SAVEPOINT "[foo]"; SAVEPOINT

Invalid savepoint names:

postgres=*# SAVEPOINT 1;
ERROR:  syntax error at or near "1"
LINE 1: SAVEPOINT 1;
                  ^

postgres=*# SAVEPOINT [foo];
ERROR:  syntax error at or near "["
LINE 1: SAVEPOINT [foo];
                  ^

Categories

SQL command, Transaction command, Transactions

See also

RELEASE SAVEPOINT, ROLLBACK TO SAVEPOINT