Re: Fwd: Query parameter types not recognized

In short - this is the wrong list (pgsql-jdbc(at)postgresql(dot)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 <rober710(at)gmail(dot)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.