34

I have created my tables with a column (type: timestamp with timezone) and set its default value to now() (current_timestamp()).

I run a series of inserts in separate statements in a single function and I noticed all the timestamps are equal down to the (ms), is the function value somehow cached and shared for the entire function call or transaction?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Ryan Fisch
  • 2,614
  • 5
  • 36
  • 57

2 Answers2

55

That is expected and documented behaviour:

From the manual:

Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the "current" time, so that multiple modifications within the same transaction bear the same time stamp.

If you want something that changes each time you run a statement, you need to use statement_timestamp() or even clock_timestamp() (again see the description in the manual)

15

now() and current_timestamp (the latter without parentheses - odd SQL standard) are STABLE functions returning the point in time when the transaction started as timestamptz.

Consider one of the other options PostgreSQL offers, in particular statement_timestamp(). The manual:

statement_timestamp() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client)

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Seems to me now the `current_timestamp` returns the current timestamp, and not when the transaction started. I tried it in pg admin with simple `select current_timestamp` statement. `select now()` still returns the timestamp when the transaction started. – Eqzt111 Jan 29 '23 at 10:18
  • @Eqzt111: Most clients (incl. pgAdmin and psql) run with "autocommit" on by default, i.e. every (block of) executed command(s) is wrapped into a transaction and committed automatically. Executing `SELECT now()` will report (almost) the same time as `SELECT statement_timestamp()` or `SELECT clock_timestamp()` this way. See: https://dba.stackexchange.com/a/63549/3684 – Erwin Brandstetter Jan 29 '23 at 12:04
  • Autocommit is set to false in my pg admin settings, that's why `select to_char(now(), 'YYYY.MM.DD HH24:MI:SS')` returns the same value until I use `commit`, but `select to_char(current_timestamp, 'YYYY.MM.DD HH24:MI:SS')` always returns the current time on the server. Give it a try. – Eqzt111 Jan 29 '23 at 16:42
  • 1
    @Eqzt111: I gave it a try, and there is no difference whatsoever, as expected. Would be a *very* odd bug if there was any difference. If you can reproduce your observation, please report a bug, including your environment (pg version, client, ...) – Erwin Brandstetter Jan 30 '23 at 13:39