Why does 'now()'::timestamp return the current timestamp in PostgreSQL?

Question

A simple example:

select 'now()'::timestamp;

gives

2016-03-12 07:52:46.1

Note the single quote around now() function call.

I think the now() function call is escaped by the single quote and the database should output just now(). But I tested this on Postgres 9.0 and 8.4 and the results are both 2016-03-12 07:52:46.1.

The same thing happens when I explicitly insert 'now()' (note the single quote) into a field which is of timestamp type.

But if no implicit conversion to timestamp applies, it will just output 'now()', for example:

select 'now()';

gives:

now()

And I noted such 'unescaping' only happens to now, as I tested call to other functions are correctly escaped by single quote.

I failed to find a reasonable explanation to this, can anyone please give some advice?

Answer 1

It's a special function of the conversion from string to timestamp or date/time type; the parentheses are superfluous, as it's not really a function call. I have a pretty strong suspicion that that particular value was added in order to serve as the special default value for timestamp columns.

Answer 2

The value 'now()'::timestamp is exactly the same as 'now'::timestamp and subtly different from the function call now().

Details in the current manual. Note in particular that these strings ...

... are simply notational shorthands that will be converted to ordinary date/time values when read. (In particular, now and related strings are converted to a specific time value as soon as they are read.)

Typically, you do not want to use the string as column default, which would result in a constant representing the moment in time when the command was executed ("early binding"). Use now()::timestamp or the standard SQL function LOCALTIMESTAMP to get the current local time (dynamically - "late binding").

In most cases timestamptz would be a better choice to begin with. So now() or CURRENT_TIMESTAMP. Details:

• Ignoring timezones altogether in Rails and PostgreSQL

You need to understand the difference between string literals or string constants and typed values.
Start in the manual here.