Re: Bug with PreparedStatements using EXTRACT function

> -----Ursprüngliche Nachricht-----
> Von: Maciek Sakrejda [mailto:msakrejda(at)truviso(dot)com]
> Gesendet: Mittwoch, 28. März 2012 22:54
> An: Matthias Böhm
> Cc: pgsql-jdbc(at)postgresql(dot)org
> Betreff: Re: [JDBC] Bug with PreparedStatements using EXTRACT function
>
> That's because the 'date' is not a cast but some sort of type
> specification inherent to the expression. I don't understand the
> details of the grammar, but you can see this if you try to run it via
> PREPARE (the closest language-level thing you can use to JDBC's
> parameterized statements):
>
> cqdb=# prepare foo(date) as select extract (year from date '2000-01-
> 01');
> PREPARE
> cqdb=# prepare bar(date) as select extract (year from date $1);
> ERROR: syntax error at or near "$1"
> LINE 1: prepare bar(date) as select extract (year from date $1);
> ^
> cqdb=# prepare baz(date) as select extract (year from $1);
> PREPARE
> cqdb=#

OK, I found a solution:

Whereas the last prepare statement works on the sql console it doesn't work
with jdbc: I've tried to construct the PreparedStatement as follows:

PreparedStatement stmt2 = conn.prepareStatement(
"SELECT EXTRACT (YEAR FROM ?)");

And now the exception I get is (translated roughly from German):

Function pg_catalog.date_part(unknown, unknown) is ambiguous.
Hint: Couldn't choose best candidate function. You may have to add an
explicit cast.

Adding this explicit cast makes the thing work:

PreparedStatement stmt2 = conn.prepareStatement(
"SELECT EXTRACT (YEAR FROM CAST (? AS DATE))");

It is not obvious though at once that this cast is needed.

Thanks for taking a look at this issue and kind regards,
Matthias Böhm