Re: Fwd: Query parameter types not recognized

On 02/10/2017 07:17 AM, Roberto Balarezo 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; |

What is the 1 in ? + 1 supposed to represent?

>
> where ? is a query parameter. I’m using JDBC to connect to the database,
> and sending parameters like this:
>
> |query.setDate(1, defaultDueDate); |
>
> Where defaultDueDate is a java.sql.Date object. 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 what is the actual value of defaultDueDate?

Looks like it is an integer from the ERROR message.

Might want to look in the Postgres logs to see if they show anything
that might help.

>
> Why is it inferring that the type is integer, when I send it as Date??

I don't use Java, but I did find the below, don't know if it helps?:

https://jdbc.postgresql.org/documentation/94/escapes-datetime.html

>
> 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 |
>
> If I’m telling PostgreSQL that the parameter is going to be a Date, and
> send through the driver a Date, why it is having trouble determining the
> datatype of the parameter??
> What can I do to make it work?
>
> For reference, I’m using PostgreSQL 9.2.15 and JDBC driver 9.4.1207.jre6.
>
> Thanks for your advice!
>
> ​
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com