0

I have several tables in a database, some containing timestamps like so:

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Now I want to populate these tables using several pg/plsql functions, which contain INSERT statements that do not specify the created_at stamp. I call these functions with a single command line:

psql -U bob -h localhost tests -c "select * from test.main()"

I would assume that, even though the sql takes a very short time to execute, I would end up with slight variations in timestamp values. Surprisingly, it is not the case, and timestamps are identical down to the microsecond.

Does anyone know the reason for this behavior (and if there is a way to avoid that)?

Thanks

user3166886
  • 51
  • 5
  • The way to avoid is to read the docs [Current Time](https://www.postgresql.org/docs/12/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT). 'now() is a traditional PostgreSQL equivalent to transaction_timestamp().' You want "clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command." – Adrian Klaver Feb 04 '21 at 22:05
  • Thank you, I did not read the documentation carefully enough indeed. – user3166886 Feb 04 '21 at 22:12

0 Answers0