Hi Tom,
Thanks for your quick response. I'll give that a try.
Len
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, April 24, 2018 4:22 PM
To: Carlsen, Len <len.carlsen@ubc.ca>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15171: JDBC TIMESTAMP WITH TIME ZONE PSQLException When Using Substitution Parameter
=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> Executing this query works fine: 'SELECT TIMESTAMP WITH TIME ZONE
> '2018-04-17 12:00:00.0-07'"
> But executing query "SELECT TIMESTAMP WITH TIME ZONE ?" with a
> substitution parameter results in a PSQLException syntax error.
Yes. A parameter symbol is not a syntactic equivalent to a string literal; it's more nearly like a variable. You
can'tsay "typename variable" either. You need to write it like a cast, ie "variable::typename", or "CAST(variable AS
typename)"if you want to be pedantically standards-compliant. So what you want is "SELECT ?::TIMESTAMP WITH TIME
ZONE".
(Allowing the non-orthogonal, non-extensible syntax "typename string-literal" was not one of the SQL committee's better
decisionsIMV. We're stuck with supporting it, but I'd suggest avoiding it in favor of cast-like notation, which works
ina much wider variety of
cases.)
regards, tom lane