Search code examples
postgresqltypescastingtimestamp

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

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?

Solution

  • 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.