Re: BUG #13812: 'now' acting differently than now() in function

On Thu, Dec 10, 2015 at 12:52 PM, <darin(at)darinhoward(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 13812
> Logged by: Darin Howard
> Email address: darin(at)darinhoward(dot)com
> PostgreSQL version: 9.4.5
> Operating system: Linux
> Description:
> ​[...]​
>
> I would not expect the date2 on row 2 to be the same date2 as row 1.
>
> Replacing
>
> UPDATE test_date_bug SET date2 = 'now' WHERE id = id_param;
> With
>
> UPDATE test_date_bug SET date2 = now() WHERE id = id_param;
> Sets new date as I would expect:
>

*​Quick answer: *'now' is a string literal that is resolved once and
embedded (i.e., parsed) into the query for the life of the session due to
caching; now() is a function that gets executed each time the query is
executed.​

*Additional considerations:*​

​I may be missing a nuance here, this could
maybe use better documentation surrounding implications, but the basic
answer is that 'now' is a string literal that ends up getting resolved at
parse time during the first invocation of the function and then its value
is cached and re-used during subsequent executions. The "now()" volatile
function is instead left alone by the parser and instead is invoked during
each execution of the UPDATE.

I am going from memory here since I cannot locate the documentation that
points out this fact...

The same behavior is seen if you trying to use "now" and "now()" as part of
the column default (SQL functions that can be written without parens but
that can only be functions do not exhibit this behavior). Using "now" you
end up getting the time the table was created instead of the time the row
was inserted;

CREATE TABLE now_test (id serial, tm timestamptz DEFAULT 'now');
INSERT INTO now_test DEFAULT VALUES; --repeat manually
SELECT * FROM now_test; --all same values for tm

David J.