COMMIT v17

Suggest edits

The COMMIT command makes all database updates from the current transaction permanent and ends the current transaction.

COMMIT [ WORK ];

You can use the COMMIT command in anonymous blocks, stored procedures, or functions. In an SPL program, it can appear in the executable section and the exception section.

In this example, the third INSERT command in the anonymous block results in an error. The effect of the first two INSERT commands is retained as shown by the first SELECT command. Even after issuing a ROLLBACK command, the two rows remain in the table, as shown by the second SELECT command verifying that they were indeed committed.

Note

You can set the edb_stmt_level_tx configuration parameter shown in the example for the entire database using the ALTER DATABASE command. Alternatively, you can set it for the entire database server by changing it in the postgresql.conf file.

\set AUTOCOMMIT off
SET edb_stmt_level_tx TO on;

BEGIN
    INSERT INTO dept VALUES (50, 'FINANCE', 'DALLAS');
    INSERT INTO dept VALUES (60, 'MARKETING', 'CHICAGO');
    COMMIT;
    INSERT INTO dept VALUES (70, 'HUMAN RESOURCES', 'CHICAGO');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;

SQLERRM: value too long for type character varying(14)
SQLCODE: 22001

SELECT * FROM dept;
Output
deptno  |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | FINANCE    | DALLAS
     60 | MARKETING  | CHICAGO
(6 rows)
ROLLBACK;

SELECT * FROM dept;
Output
deptno  |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | FINANCE    | DALLAS
     60 | MARKETING  | CHICAGO
(6 rows)
← Prev

About transactions

↑ Up

Working with transactions

Next →

ROLLBACK