Thread: SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR

On 14/08/22 15:40, Piotr Gasidło wrote:
> Hello,
>
> I found strange PostgreSQL 9.3 behavior:
>
>> select now()::timestamp, 'now()'::timestamp;
>             now             |         timestamp
> ----------------------------+----------------------------
>  2014-08-22 08:34:00.883268 | 2014-08-22 08:34:00.883268
>
> Second column is now() in single apostrophes.
>
> Now, I tried similar function, clock_timestamp() and get:
>
>> select clock_timestamp()::timestamp, 'clock_timestamp()'::timestamp;
> ERROR:  invalid input syntax for type timestamp: "clock_timestamp()"
> LINE 1: select clock_timestamp()::timestamp, 'clock_timestamp()'::ti...
>                                                ^
>
> Why is NOW() so special? Where is it documented?

Here:
  http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

  "All the date/time data types also accept the special literal value 'now' to
   specify the current date and time"

and also here:

  http://www.postgresql.org/docs/9.3/static/datatype-datetime.html#AEN5861

Regards


Ian Barwick


--
 Ian Barwick                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Piotr Gasidło wrote
> Hello,
>
> I found strange PostgreSQL 9.3 behavior:
>
>> select now()::timestamp, 'now()'::timestamp;
>             now             |         timestamp
> ----------------------------+----------------------------
>  2014-08-22 08:34:00.883268 | 2014-08-22 08:34:00.883268
>
> Second column is now() in single apostrophes.
>
> Now, I tried similar function, clock_timestamp() and get:
>
>> select clock_timestamp()::timestamp, 'clock_timestamp()'::timestamp;
> ERROR:  invalid input syntax for type timestamp: "clock_timestamp()"
> LINE 1: select clock_timestamp()::timestamp, 'clock_timestamp()'::ti...
>                                                ^
>
> Why is NOW() so special? Where is it documented? And why not working with
> other timestamp returning internal functions?
>
>> select version();
>                                                                version
>
>
--------------------------------------------------------------------------------------------------------------------------------------
>  PostgreSQL 9.3.4 on amd64-portbld-freebsd10.0, compiled by FreeBSD clang
> version 3.3 (tags/RELEASE_33/final 183502) 20130610, 64-bit
> (1 wiersz)
>
>
> --
> Piotr Gasidło

SELECT ' now** '::timestamp --works

Pretty much any symbol before or after the word now is allowed and you still
get a valid result.  Putting a letter or number anywhere in the string
causes an input syntax error.

Tested on 9.0

As for documentation:

http://www.postgresql.org/docs/9.2/interactive/datetime-input-rules.html

2.b

'now' is a "special string" as referenced in this rule

The tokenizer must be constructed to throw away whitespace and any symbols
except those used in normal timestamps (~ [:/-])

<tests 'now-'>

Yep, ^ gives me an error.

That appendix section is missing considerable detail that I've inferred from
the observed behavior - though some of the gaps are filled in once you've
read the following:

http://www.postgresql.org/docs/9.2/interactive/datatype-datetime.html

The above also explains that the special SQL keywords cannot be used as
string literals though as is often the case it omits any discussion as to
why.  The fact that they are functions obviously does not preclude them from
also being keywords...

Most likely its this way for SQL standards compatibility reasons.

Do you have a use-case you'd like to share or is this curiosity after
accidentally finding out that 'now'::timestamp actually works?

David J.







--
View this message in context:
http://postgresql.1045698.n5.nabble.com/SELECT-NOW-OK-SELECT-CLOCK-TIMESTAMP-ERROR-tp5815823p5815826.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.