From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Roberto Balarezo <rober710(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Fwd: Query parameter types not recognized |
Date: | 2017-02-11 01:46:08 |
Message-ID: | CAKFQuwYJu6vxEn=0XuYAX8kfYKVemtL2TrH4QzVDFYEY83gR4Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
In short - this is the wrong list (pgsql-jdbc(at)postgresql(dot)org is the
appropriate one; or the official GitHub repo) and you need to provide some
working self-contained examples showing exactly what you are doing.
On Fri, Feb 10, 2017 at 8:17 AM, Roberto Balarezo <rober710(at)gmail(dot)com>
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;
>
> If anything is wrong here it is a JDBC bug - not PostgreSQL core - and
should be reported there. If you write the above using:
PREPARE testquery AS ... - i.e., no type specification - you get the same
error - which happens because PostgreSQL guesses and assumes the left
operand of the"+(?,integer)" operator is going to be integer (how exactly
it comes to that conclusion I do not know).
> [...]
>
>
>
> 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 JDBC must not telling the server about the expected type of parameter
#1 when it sends the prepare command and statement text, otherwise the
server wouldn't complain. If you are using "setDate" that seems unlikely -
but I'm not familiar enough with the JDBC implementation to know for sure.
> 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
>
> This form can be prepared successfully directly in SQL even without an
explicit specification of the data type in the PREPARE SQL command - so I'm
led to believe, due to the lack of a self-contained test case - that you
are possibly doing something wrong in the actual code - hence my second
piece of advice at the top.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | prakash ramakrishnan | 2017-02-11 06:48:14 | Postgres |
Previous Message | Tanner Kerr | 2017-02-11 00:34:53 | BDR problem rejoining node |