Re: PreparedStatement.setDate() behavior with OVERLAPS

Christopher BROWN <brown(at)reflexe(dot)fr> writes:
> When I use the following query as a PreparedStatement with the 9.4.1201
> JDBC driver, using a 9.4.4 database, the SQL is rejected:

> SELECT id, ctime, mtime, is_archived, ref_store, ref_supplier,
> period_begins, period_ends, received_by, received_on, received_qty,
> disposed_qty FROM store_delivery WHERE (period_begins, period_ends +
> interval '1 day') OVERLAPS (?, ? + interval '1 day') AND ref_store = ?
> ORDER BY period_begins, ctime

> Specifically, with "invalid input syntax for type interval". I'm setting
> the first two parameters to java.sql.Date values, using "setDate" method of
> PreparedStatement (the third parameter is an integer, ex 4251).

FWIW, the same would happen if you just did this in psql:

regression=# select '2015-09-06' + interval '1 day';
ERROR: invalid input syntax for type interval: "2015-09-06"

The server uses various heuristics to determine the type of an unmarked
literal or parameter symbol, and the first one that applies in this
context is "assume it's the same type as the other input to the binary
operator". So the only way to make this work is to explicitly tell the
server that the parameter is of type date or timestamp. You could do that
within the SQL string with "::date", which as you mentioned fixes the
problem. However, I'd have expected that if you set the parameter with
setDate or equivalent, the JDBC driver would pass along the information
that the value is of type date. I'm not sure what the restrictions are on
making that happen, but that's the area to sniff around in. Maybe you're
actually using setString, for example? Or using protocol version 2, which
doesn't have a provision for passing parameter type data?

regards, tom lane