Postgres Pro
Downloads
Home   >   mailing lists

Re: [GENERAL] Fwd: Query parameter types not recognized - Mailing list pgsql-general

From Roberto Balarezo
Subject Re: [GENERAL] Fwd: Query parameter types not recognized
Date
Msg-id CALN83z7akvhuDd-GG64F_oQew7_T4jTDza6aOaLEW-gp4uHPBQ@mail.gmail.com
Whole thread Raw
In response to Re: [GENERAL] Fwd: Query parameter types not recognized  (rob stone <floriparob@gmail.com>)
List pgsql-general
Tree view
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@gmail.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




pgsql-general by date:

Previous
From: Arjen Nienhuis
Date:
Subject: Re: [GENERAL] Fwd: Query parameter types not recognized
Next
From: Adrian Klaver
Date:
Subject: Re: [GENERAL] Fwd: Query parameter types not recognized