Fwd: Query parameter types not recognized

From: Roberto Balarezo <rober710(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Fwd: Query parameter types not recognized
Date: 2017-02-10 15:17:02
Message-ID: CALN83z5hoybgObn+7Mm3Z-U8Y8mJx_3bp+7DWNDT4+u+MTCahg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

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

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

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!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Janes 2017-02-10 17:01:21 Re: Locks Postgres
Previous Message Peter J. Holzer 2017-02-10 14:04:32 Re: Loose indexscan and partial indexes