RE: BUG #15171: JDBC TIMESTAMP WITH TIME ZONE PSQLException WhenUsing Substitution Parameter

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