Can the transaction continue after a statement error?
We often execute a series of SQL statements in a transaction block. If an error occurs during the execution of one of these SQL statements, can other subsequent SQL statements continue?
If an error occurs during the execution of an SQL statement in a transaction, you can continue to execute other SQL statements.
Redrock Postgres supports statement-level atomicity, the unsuccessful statement does not cause the loss of any work that preceded it in the current transaction.
In the following example, we need to create a table whose name is randomly generated. Before creating the table using the table name, try to query the table to check whether the table name has been used.
BEGIN; SELECT * FROM temp_ktmwq_1258 LIMIT 1; ERROR: relation "temp_ktmwq_1258" does not exist CREATE TABLE temp_ktmwq_1258 (id integer, name text); INSERT INTO temp_ktmwq_1258 (id, name) VALUES (1, 'dummy'); COMMIT;
In the above example, we can still execute other SQL statements in the transaction even if an error occurs when we query a nonexistent table.