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

Hi Tom,

Thanks for your quick response. I'll give that a try.

Len

-----Original Message-----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Tuesday, April 24, 2018 4:22 PM
To: Carlsen, Len <len(dot)carlsen(at)ubc(dot)ca>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #15171: JDBC TIMESTAMP WITH TIME ZONE PSQLException When Using Substitution Parameter

=?utf-8?q?PG_Bug_reporting_form?= <noreply(at)postgresql(dot)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't say "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 decisions IMV. We're stuck with supporting it, but I'd suggest avoiding it in favor of cast-like notation, which works in a much wider variety of
cases.)

regards, tom lane