Re: Fwd: Query parameter types not recognized

Hi Rob,

Thanks for your answer. The query is just an example I made to illustrate
the problem. In the database I'm working with, duedate is a timestamp
without timezone column, which can contain null values. The parameter is
supposed to be of type DATE. From Java, I'm sending a Date object (which
contains no timezone information, so the driver should not have problem
with this). So if the field duedate has a null value, a default date with
one day added is returned.
I read that the driver has problems with timestamp columns, because it
cannot tell the server if it is a timestamp with or without timezone, but
dates should not present this problem. The server should know it is of DATE
type.

PS: I have changed the code of the application to send the value
(defaultDate + 1 day) calculated in the application and sent this as a
parameter to make it work, but there are many queries like this and I would
like to know why it happens and if I can make it work changing the query
and not the code.

2017-02-10 15:38 GMT-05:00 rob stone <floriparob(at)gmail(dot)com>:

> Hello Roberto,
> On Fri, 2017-02-10 at 10:17 -0500, 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;
> > 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??
> >
> >
>
> Prepared statement's set.Date applies the current server timezone to
> the value. So, if the database column duedate is of type DATE, it can't
> interpret what you are trying to do.
>
> If duedate can be null, then I really don't understand your query.
>
> HTH.
>
> Rob
>
>
>
>