The PostgreSQL transaction model

The PostgreSQL transaction model differs quite a lot from other systems. In many cases, these differences are a root source of trouble. In this section, some basic examples have been included to make you aware of the way PostgreSQL works.

The first thing to take into account is that a transaction block has to be started with BEGIN statement and finished with a simple COMMIT statement All statements inside the transaction block have to be correct:

test=# BEGIN;
BEGIN
test=# SELECT now();
              now              
-------------------------------
 2014-09-22 13:27:24.952578+02
(1 row)

test=# SELECT now();
              now              
-------------------------------
 2014-09-22 13:27:24.952578+02
(1 row)

test=# COMMIT;
COMMIT

Note that now() will return the transaction time. Therefore it is always the same, regardless of how often it is used. Many people like to use DEFAULT now() in their column definitions. Always keep in mind that DEFAULT now() sets...