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.kla...@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.kla...@aklaver.com >> <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
