Re: Bug with PreparedStatements using EXTRACT function

>Whereas the last prepare statement works on the sql console...

Well, only if you explicitly provide the parameter type information,
as I did. Compare the last statement to

cqdb=# prepare quux as select extract(year from $1);
ERROR: function pg_catalog.date_part(unknown, unknown) is not unique
LINE 1: prepare quux as select extract(year from $1);
^
HINT: Could not choose a best candidate function. You might need to
add explicit type casts.

Note that the parameter declaration on the prepared statement is
missing this time and you get a similar error.

>...it doesn't work with jdbc:

Are you using setObject() to provide the parameter value? If so, I
think the JDBC driver does not give the server enough information
about the target parameter type. It asks the server to figure it out,
and the server can't, because it's ambiguous if all it's getting is a
parameter string typed as "unknown". If you use one of the
type-specific setters, it should work. The driver does attempt to do
some type-guessing based on the Java "source" parameter type, but
there is something of a disconnect between Java and Postgres types, so
this does *not* occur for java.util.Date, and for java.sql.Timestamp,
it occurs in such a way as to break for your use case (see
https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc2/AbstractJdbc2Statement.java#L3272
).

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com