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@xxxxxxxxx>:
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 Prepared statement's set.Date applies the current server timezone to
> without time zone and integer cannot be matched
> Why is it inferring that the type is integer, when I send it as
> Date??
>
>
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
