Mastering PostgreSQL 12 - Third Edition

Handling errors inside a transaction

It is not always the case that transactions are correct from beginning to end. Things might just go wrong for whatever reason. However, in PostgreSQL, only error-free transactions can be committed. The following listing shows a failing transaction, which errors out due to a division-by-zero error:

test=# BEGIN; 
BEGINtest=# SELECT 1; 
 ?column? ----------      1(1 row)test=# SELECT 1 / 0; psql: ERROR: division by zero  test=# SELECT 1; 
psql: ERROR: current transaction is aborted, commands ignored until end of transaction block test=# COMMIT;  
ROLLBACK 

Note that division by zero did not work out.

In any proper database, an instruction similar to this will instantly error out and make the statement fail.

It is important ...