sqldatabasepostgresqlrelational-databasepersistence

Will multiple calls to `now()` in a single PostgreSQL query always give the same result?

I want to insert one row in a table; e.g.:

INSERT INTO some_table VALUES (now(), now());

I want the date value in both the columns to be equal. Is the above query safe for this requirement? Or should I use other alternatives like sub-query/CTE:

INSERT INTO some_table (select t.now, t.now from (select now()) as t);

In general, how do these functions get invoked in SQL internally? How is the sequence (left to right/right to left) of functions to be invoked decided? Is a given function just called once and the return value cached for a single query? Is it vendor-specific?

Solution

  • According to the PostgreSQL documentation, now() will always return the same value within one SQL statement.

    now()

    now() is a traditional PostgreSQL equivalent to transaction_timestamp()

    transaction_timestamp()

    transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns.

    CURRENT_TIMESTAMP

    These SQL-standard functions all return values based on the start time of the current transaction: