In short - this is the wrong list (pgsql-j...@postgresql.org 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.
On Fri, Feb 10, 2017 at 8:17 AM, Roberto Balarezo <rober...@gmail.com> wrote: > 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.
