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; 
BEGIN
test=# 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.

It is important to point out that PostgreSQL will error out, unlike MySQL, which is far less strict. After an error has occurred, no more instructions will be accepted, even if those instructions are semantically and syntactically correct. It is still possible to issue COMMIT. However, PostgreSQL will roll back the transaction because it is the only correct thing to be done at that point.