Christopher BROWN <brown@reflexe.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
Tom,
I suspect it is getting lumped into the time/timestamp and we send it over as unknown... If so we can fix setDate.
Christopher, can you try this on HEAD. If you can build it ?