31

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?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Vikas Prasad
  • 3,163
  • 5
  • 28
  • 39

1 Answers1

48

The documentation says about now():

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

And about transaction_timestamp():

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

So within one SQL statement, now() will always return the same value.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Can you explain the other questions mentioned in the end of the post? Like sequence of function invocation etc.? – Vikas Prasad Apr 20 '18 at 07:12
  • 5
    SQL is "declarative". The SQL states what you want. The query planner is free to execute functions in any order or any number of times as long as it returns the right result. – Andomar Apr 20 '18 at 07:14