Thread: setting PreparedStatement parameter using "at time zone interval ?"

I'm using the latest 8.3 development driver against an 8.3 database. I
thought back in 8.1 I used to be able to use a PreparedStatement that
included something like

... date(l.created AT TIME ZONE INTERVAL ?)

but now I am always getting an exception like

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

I found if I changed the query to use this syntax:

... date(timezone(?::interval, l.created))

it worked fine. Should the former syntax be supported by the JDBC driver?

-- m@

"Matt Magoffin" <postgresql.org@msqr.us> writes:
> I'm using the latest 8.3 development driver against an 8.3 database. I
> thought back in 8.1 I used to be able to use a PreparedStatement that
> included something like
> ... date(l.created AT TIME ZONE INTERVAL ?)

That might've worked back before the driver did real prepared
statements.  It's invalid syntax though, because typename before the
constant works only for simple literal string constants.  You got away
with it when the driver interpolated parameter values into the query as
string literals, but you can't do it anymore.

?::interval (or the full-blown CAST syntax) is the right way.

See
http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
particularly 4.1.2.5.

            regards, tom lane

> That might've worked back before the driver did real prepared
> statements.  It's invalid syntax though, because typename before the
> constant works only for simple literal string constants.  You got away
> with it when the driver interpolated parameter values into the query as
> string literals, but you can't do it anymore.
>
> ?::interval (or the full-blown CAST syntax) is the right way.
>
> See
> http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
> particularly 4.1.2.5.

Thank you for the clarification.

-- m@