Postgres: is NOW() used in more places of the query guaranteed to be always the same?

Question

i was writing a query where you have tickets in a table. The column first_use_on represents the TIMESTAMP of the first time it has been used. first_use_on is by the default NULL and then updated once used the first time only. My query works fine but now i got the need to know outside of the query if the runned query fired first_use_on, so i thought about of adding first_use_on = NOW() AS is_first_usage in the RETURNING. Can i be 100% sure that the NOW() compared in the returning is always the very same of the one used in the UPDATE part? Could there be some cases where they differ?

UPDATE
    l_codes
SET first_use_on = (
    CASE WHEN first_use_on IS NULL THEN 
        NOW()
    ELSE 
        first_use_on 
    END )
WHERE
    l_code = 'C9TCH' AND id_mostra = 1
RETURNING 
    first_use_on,
    first_use_on = NOW() AS is_first_usage,
    NOW() > DATE_TRUNC('day', first_use_on + INTERVAL '1 DAY') AS expired,
    DATE_TRUNC('day', first_use_on + INTERVAL '1 DAY') AS expiration_on;

score 4 · Accepted Answer · answered Jun 22 '20 at 13:48

Yes. As specified in the documentation for now():

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.

The transaction start is constant for the entire statement.

score 3 · Answer 2 · answered Jun 22 '20 at 13:48

Postgres: is NOW() used in more places of the query guaranteed to be always the same?

Yes. It returns the timestamp at the beginning of the current transaction, as explained in the documentation:

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.

Postgres: is NOW() used in more places of the query guaranteed to be always the same?

2 Answers2