Different behaviours in getting current timestamp in Oracle and PostgreSQL within a transaction

Behaviour in Oracle:

• Implicitly start a transaction when first DML statement starts
• DDL is not part of the transaction
• functions like sysdate(), systimestamp() return different values to reflect the time when these functions executed.

SQL> create table t1 (ts timestamp);

Table T1 created.

SQL> insert into t1 values (systimestamp);

1 row inserted.

SQL> insert into t1 values (systimestamp);

1 row inserted.

SQL> select * from t1;

                               TS 

_________________________________ 

08/10/21 01:56:15.663021000 AM    

08/10/21 01:56:17.543937000 AM    

SQL> rollback;

Rollback complete.

SQL> select * from t1;

no rows selected

Behaviour in PostgreSQL:

• Explicitly start a transaction (in psql CLI)
• DDL can be part of the transaction
• functions like sysdate(), systimestamp() return same value to reflect the time when transaction starts.
• To get similar behaviour as Oracle, use clock_timestamp()

mytest=> begin;

BEGIN

mytest=*> create table t1 (ts timestamp);

CREATE TABLE

mytest=*> insert into t1 values (current_timestamp);

INSERT 0 1

mytest=*> insert into t1 values (current_timestamp);

INSERT 0 1

mytest=*> select * from t1;

             ts             

----------------------------

 2021-10-08 01:49:26.576841

 2021-10-08 01:49:26.576841

(2 rows)

mytest=*> rollback;

ROLLBACK