In short - this is the wrong list (pgsql-jdbc@xxxxxxxxxxxxxx is the appropriate one; or the official GitHub repo) and you need to provide some working self-contained examples showing exactly what you are doing.
Hi, I would like to know why this is happening and some advice if there is a way to solve this problem:I have a query like this:
select COALESCE(duedate, ? + 1) from invoices order by duedate desc limit 10;
If anything is wrong here it is a JDBC bug - not PostgreSQL core - and should be reported there. If you write the above using:
PREPARE testquery AS ... - i.e., no type specification - you get the same error - which happens because PostgreSQL guesses and assumes the left operand of the"+(?,integer)" operator is going to be integer (how exactly it comes to that conclusion I do not know).
[...]
However, when I try to execute the query, I get this error:
org.postgresql.util.PSQLException: ERROR: COALESCE types timestamp without time zone and integer cannot be matched
So JDBC must not telling the server about the expected type of parameter #1 when it sends the prepare command and statement text, otherwise the server wouldn't complain. If you are using "setDate" that seems unlikely - but I'm not familiar enough with the JDBC implementation to know for sure.
Why is it inferring that the type is integer, when I send it as Date??
When I force the type using a cast, like this:
select COALESCE(duedate, CAST(? AS DATE) + 1) from invoices order by duedate desc limit 10;I get this error:
org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1
This form can be prepared successfully directly in SQL even without an explicit specification of the data type in the PREPARE SQL command - so I'm led to believe, due to the lack of a self-contained test case - that you are possibly doing something wrong in the actual code - hence my second piece of advice at the top.
David J.
