Thread: [GENERAL] Fwd: Query parameter types not recognized
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.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.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!
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
On 02/10/2017 07:17 AM, 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; | What is the 1 in ? + 1 supposed to represent? > > 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 | So what is the actual value of defaultDueDate? Looks like it is an integer from the ERROR message. Might want to look in the Postgres logs to see if they show anything that might help. > > Why is it inferring that the type is integer, when I send it as Date?? I don't use Java, but I did find the below, don't know if it helps?: https://jdbc.postgresql.org/documentation/94/escapes-datetime.html > > 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! > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Feb 10, 2017 8:11 PM, "Roberto Balarezo" <rober710@gmail.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;where ? is a query parameter. I’m using JDBC to connect to the database, and sending parameters like this:
query.setDate(1, defaultDueDate);
If you want to add to a date you cannot just add 1. You need an interval: coalesce(duedate, ? + interval '1 day')
See:
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
On 02/10/2017 01:33 PM, Arjen Nienhuis wrote: > > > On Feb 10, 2017 8:11 PM, "Roberto Balarezo" <rober710@gmail.com > <mailto:rober710@gmail.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; | > > where ? is a query parameter. I’m using JDBC to connect to the > database, and sending parameters like this: > > |query.setDate(1, defaultDueDate); | > > If you want to add to a date you cannot just add 1. You need an > interval: coalesce(duedate, ? + interval '1 day') > > See: > > https://www.postgresql.org/docs/9.6/static/functions-datetime.html Actually that is not the case, from above docs: "Also, the + and * operators come in commutative pairs (for example both date + integer and integer + date); we show only one of each such pair." and: test=# select current_date; date ------------ 2017-02-10 (1 row) test=# select current_date + 1; ?column? ------------ 2017-02-11 (1 row) -- Adrian Klaver adrian.klaver@aklaver.com
Hi,
The parameter defaultDueDate is a java.sql.Date object, an actual Date. When I run the query with the value in it, it works:
```sql
db=> select COALESCE(duedate, date '2017-02-01' + 1) from invoices order by duedate desc;
coalesce
---------------------
2017-02-02 00:00:00
2017-02-02 00:00:00
2016-11-14 00:00:00
2017-02-10 00:00:00
2017-02-02 00:00:00
2017-02-13 00:00:00
2017-02-02 00:00:00
2017-02-02 00:00:00
```
But when I send it as a parameter, it ignores it and seems to think the expression is of type interger.
2017-02-10 16:32 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 02/10/2017 07:17 AM, 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; |
What is the 1 in ? + 1 supposed to represent?
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 |
So what is the actual value of defaultDueDate?
Looks like it is an integer from the ERROR message.
Might want to look in the Postgres logs to see if they show anything that might help.
Why is it inferring that the type is integer, when I send it as Date??
I don't use Java, but I did find the below, don't know if it helps?:
https://jdbc.postgresql.org/documentation/94/escapes-datetim e.html --
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!
Adrian Klaver
adrian.klaver@aklaver.com
Hi Arjen,
I already tried that too. In that case, the error changes to `org.postgresql.util.PSQLException: ERROR: COALESCE types timestamp without time zone and interval cannot be matched`.
I listed all the operators available for dates, and `+` and `-` take a date and an integer to return a date with one day added. So the query is correct.
2017-02-10 16:33 GMT-05:00 Arjen Nienhuis <a.g.nienhuis@gmail.com>:
On Feb 10, 2017 8:11 PM, "Roberto Balarezo" <rober710@gmail.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;where ? is a query parameter. I’m using JDBC to connect to the database, and sending parameters like this:
query.setDate(1, defaultDueDate);If you want to add to a date you cannot just add 1. You need an interval: coalesce(duedate, ? + interval '1 day')See:
On 02/10/2017 01:51 PM, Roberto Balarezo wrote: > Hi, > > The parameter defaultDueDate is a java.sql.Date object, an actual Date. > When I run the query with the value in it, it works: > ```sql > db=> select COALESCE(duedate, date '2017-02-01' + 1) from invoices order > by duedate desc; > coalesce > --------------------- > 2017-02-02 00:00:00 > 2017-02-02 00:00:00 > 2016-11-14 00:00:00 > 2017-02-10 00:00:00 > 2017-02-02 00:00:00 > 2017-02-13 00:00:00 > 2017-02-02 00:00:00 > 2017-02-02 00:00:00 > ``` > > But when I send it as a parameter, it ignores it and seems to think the > expression is of type interger. Which would indicate to me that is what is being passed in the parameter. If I would guess, from information here: https://docs.oracle.com/javase/7/docs/api/java/sql/Date.html milliseconds since January 1, 1970 00:00:00.000 GMT. Turn on/up logging in Postgres and run a query with that java.sql.Date object. I am betting that what you will see in the logs is an integer. > > 2017-02-10 16:32 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>: > > On 02/10/2017 07:17 AM, 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; | > > > What is the 1 in ? + 1 supposed to represent? > > > 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 | > > > So what is the actual value of defaultDueDate? > > Looks like it is an integer from the ERROR message. > > Might want to look in the Postgres logs to see if they show anything > that might help. > > > Why is it inferring that the type is integer, when I send it as > Date?? > > > I don't use Java, but I did find the below, don't know if it helps?: > > https://jdbc.postgresql.org/documentation/94/escapes-datetime.html > <https://jdbc.postgresql.org/documentation/94/escapes-datetime.html> > > > > 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! > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
Hmmm... I didn't know PostgreSQL had a facility for query logging and debugging of parameters to a logfile. Thought I had to execute a describe or something like that. Thanks, I'll try it to see what's happening!
2017-02-10 16:57 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 02/10/2017 01:51 PM, Roberto Balarezo wrote:Hi,
The parameter defaultDueDate is a java.sql.Date object, an actual Date.
When I run the query with the value in it, it works:
```sql
db=> select COALESCE(duedate, date '2017-02-01' + 1) from invoices order
by duedate desc;
coalesce
---------------------
2017-02-02 00:00:00
2017-02-02 00:00:00
2016-11-14 00:00:00
2017-02-10 00:00:00
2017-02-02 00:00:00
2017-02-13 00:00:00
2017-02-02 00:00:00
2017-02-02 00:00:00
```
But when I send it as a parameter, it ignores it and seems to think the
expression is of type interger.
Which would indicate to me that is what is being passed in the parameter. If I would guess, from information here:
https://docs.oracle.com/javase/7/docs/api/java/sql/Date.html
milliseconds since January 1, 1970 00:00:00.000 GMT.
Turn on/up logging in Postgres and run a query with that java.sql.Date object. I am betting that what you will see in the logs is an integer.
2017-02-10 16:32 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>: adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.
On 02/10/2017 07:17 AM, 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; |
What is the 1 in ? + 1 supposed to represent?
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 |
So what is the actual value of defaultDueDate?
Looks like it is an integer from the ERROR message.
Might want to look in the Postgres logs to see if they show anything
that might help.
Why is it inferring that the type is integer, when I send it as
Date??
I don't use Java, but I did find the below, don't know if it helps?:
https://jdbc.postgresql.org/documentation/94/escapes-datetim e.html
<https://jdbc.postgresql.org/documentation/94/escapes-dateti me.html>
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!
--
Adrian Klavercom>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 02/10/2017 02:14 PM, Roberto Balarezo wrote: > Hmmm... I didn't know PostgreSQL had a facility for query logging and > debugging of parameters to a logfile. Thought I had to execute a > describe or something like that. Thanks, I'll try it to see what's > happening! Start here: https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html Set up logging and then set : log_statement = 'all' This will generate a lot of logs so you will probably not want to keep it that way. A Python example: In [6]: date.today() Out[6]: datetime.date(2017, 2, 10) In [7]: cur.execute('select %s', [date.today()]) From Postgres log; aklaver-2017-02-10 14:35:42.842 PST-0LOG: statement: BEGIN aklaver-2017-02-10 14:35:42.842 PST-0LOG: statement: select '2017-02-10'::date > > 2017-02-10 16:57 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>: > > On 02/10/2017 01:51 PM, Roberto Balarezo wrote: > > Hi, > > The parameter defaultDueDate is a java.sql.Date object, an > actual Date. > When I run the query with the value in it, it works: > ```sql > db=> select COALESCE(duedate, date '2017-02-01' + 1) from > invoices order > by duedate desc; > coalesce > --------------------- > 2017-02-02 00:00:00 > 2017-02-02 00:00:00 > 2016-11-14 00:00:00 > 2017-02-10 00:00:00 > 2017-02-02 00:00:00 > 2017-02-13 00:00:00 > 2017-02-02 00:00:00 > 2017-02-02 00:00:00 > ``` > > But when I send it as a parameter, it ignores it and seems to > think the > expression is of type interger. > > > Which would indicate to me that is what is being passed in the > parameter. If I would guess, from information here: > > https://docs.oracle.com/javase/7/docs/api/java/sql/Date.html > <https://docs.oracle.com/javase/7/docs/api/java/sql/Date.html> > > milliseconds since January 1, 1970 00:00:00.000 GMT. > > Turn on/up logging in Postgres and run a query with that > java.sql.Date object. I am betting that what you will see in the > logs is an integer. > > > > 2017-02-10 16:32 GMT-05:00 Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>>: > > > On 02/10/2017 07:17 AM, 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; | > > > What is the 1 in ? + 1 supposed to represent? > > > 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 | > > > So what is the actual value of defaultDueDate? > > Looks like it is an integer from the ERROR message. > > Might want to look in the Postgres logs to see if they show > anything > that might help. > > > Why is it inferring that the type is integer, when I > send it as > Date?? > > > I don't use Java, but I did find the below, don't know if it > helps?: > > > https://jdbc.postgresql.org/documentation/94/escapes-datetime.html > <https://jdbc.postgresql.org/documentation/94/escapes-datetime.html> > > <https://jdbc.postgresql.org/documentation/94/escapes-datetime.html > <https://jdbc.postgresql.org/documentation/94/escapes-datetime.html>> > > > > 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! > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
Hello Roberto, On Fri, 2017-02-10 at 16:43 -0500, Roberto Balarezo wrote: > 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. > If the column duedate is defined as a timestamp, then setDate is not the answer. java.sql.Date is just a "date". java.util.Date is a timestamp object but from 1.8 onwards it is pretty well deprecated in favour of the Calendar methods. You can of course have 0:0:0 as the time part. All I can suggest is a spot of reading the docs about Calendar and formatting your (defaultDate + 1) as a timestamp. HTH, Rob
In short - this is the wrong list (pgsql-jdbc@postgresql.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.
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.
On Friday, February 10, 2017 6:46:08 PM EST David G. Johnston wrote: > In short - this is the wrong list (pgsql-jdbc@postgresql.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@gmail.com> > > wrote: > [snip] One thing to note is that JDBC PreparedStatement objects are not abstractions of pgsql prepared statements; the drivers performs parameter interpolation and sends a standard text query to the server. At least this was how it was many moons ago when I last hacked on the driver. So it's a case of different concepts using the same name. Not quite sure how that impacts your analysis.