Re: TIMESTAMP <string-literal>

J Lumby <johnlumby(at)hotmail(dot)com> writes:
> I am curious about the syntactic nature of the expression :
> TIMESTAMP '2020-07-13'
> in this expression, is the token TIMESTAMP a function, typecast,  or other?
> I don't see a description of it as a function and there are no
> parentheses in this example.

This construct is a typed constant, as described at
"4.1.2.7. Constants Of Other Types" on this page:

https://www.postgresql.org/docs/current/sql-syntax-lexical.html

It's a wart, frankly, and we wouldn't have it if the SQL standard
didn't insist. Of the three syntaxes described by 4.1.2.7:

type 'string'
'string'::type
CAST ( 'string' AS type )

the second and third actually work for casting any expression,
but the first one *only* works for a literal-string constant.
People are constantly burnt by that, for example by expecting
that they can write

TIMESTAMP $1

You can plug a parameter symbol into the other two syntaxes,
but not that one. On the whole I prefer using 'string'::type,
which is slightly more typing effort, but it generalizes correctly.

> I also tried
> FLOAT8 '7.3'
> and that is accepted as well.

IIRC, the SQL standard only defines this syntax for certain types
such as TIMESTAMP and INTERVAL; but Postgres allows it for any
type name.

regards, tom lane