Thread: timestamp literal out of line

Version: 8.3.9

I was surprised when I came across the following while changing some
code to send parameters out of line (to avoid interpolation).

postgres=# prepare test1 as select timestamp '2009-01-01';
PREPARE
postgres=# prepare test2 as select timestamp $1;
ERROR:  syntax error at or near "$1"

The workaround is simple, use a cast instead, but is there a particular
reason why you can't use a parameter there?

This does work, oddly enough.

postgres=# prepare test2 as select timestamp '2009-01-01' at time zone $1;
PREPARE

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Martijn van Oosterhout <kleptog@svana.org> writes:
> postgres=# prepare test1 as select timestamp '2009-01-01';
> PREPARE
> postgres=# prepare test2 as select timestamp $1;
> ERROR:  syntax error at or near "$1"

> The workaround is simple, use a cast instead, but is there a particular
> reason why you can't use a parameter there?

"timestamp foo" is legitimate syntax only when foo is a string literal.
It is not a general cast syntax.  We wouldn't even support it at all,
because it's so syntactically messy and inextensible, except that the
SQL standard requires it for literals of certain datatypes.

            regards, tom lane

Tom Lane wrote on 07.03.2010 16:34:
> We wouldn't even support it at all because it's so syntactically messy and inextensible

I like it :)

It's the only cross-DBMS way to write down a date or timestamp literal.

Thomas