Re: Something wrong with binding timestamp values

This code

PreparedStatement st = conn.prepareStatement("SELECT date_trunc('day', ?)");
st.setString(1, "2006-12-17");
ResultSet rs = st.executeQuery();

fails, as might be expected, because date_trunc(unknown, varchar)
doesn't exist.

But this code:

PreparedStatement st = conn.prepareStatement("SELECT date_trunc('day', ?)");
st.setTimestamp(1, new Timestamp(222222222));
ResultSet rs = st.executeQuery();

fails with postgresql-8.1-408.jdbc3.jar with

ERROR: function date_trunc("unknown", "unknown") is not unique

It works correctly with postgresql-8.0-318.jdbc3.jar.

What is wrong here? Why does a timestamp value end up being bound as
"unknown"?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

It's to work around the fact that JDBC only knows about one Timestamp
type, while PG knows about more. It ends up being better in most cases
to send the type as unknown and let the server disambiguate to the
correct type than to guess about the type and maybe guess wrong.

This was a recent change, which is why it used to work for you but now
doesn't. It was known that cases like yours which call overloaded
functions may require an explicit cast, something like "SELECT
date_trunc('day', ?::timestamp)", but it was decided that it's better to
force a cast in some cases than to do things demonstrably wrong in other
cases (which the older driver versions were wont to do).

You can dig through the archives for more of the gory details.

-- Mark Lewis

On Wed, 2006-12-06 at 14:58 +0100, Peter Eisentraut wrote:
> This code
>
> PreparedStatement st = conn.prepareStatement("SELECT date_trunc('day', ?)");
> st.setString(1, "2006-12-17");
> ResultSet rs = st.executeQuery();
>
> fails, as might be expected, because date_trunc(unknown, varchar)
> doesn't exist.
>
> But this code:
>
> PreparedStatement st = conn.prepareStatement("SELECT date_trunc('day', ?)");
> st.setTimestamp(1, new Timestamp(222222222));
> ResultSet rs = st.executeQuery();
>
> fails with postgresql-8.1-408.jdbc3.jar with
>
> ERROR: function date_trunc("unknown", "unknown") is not unique
>
> It works correctly with postgresql-8.0-318.jdbc3.jar.
>
> What is wrong here? Why does a timestamp value end up being bound as
> "unknown"?
>

Mark Lewis wrote:
> It's to work around the fact that JDBC only knows about one Timestamp
> type, while PG knows about more. It ends up being better in most cases
> to send the type as unknown and let the server disambiguate to the
> correct type than to guess about the type and maybe guess wrong.
>
> This was a recent change, which is why it used to work for you but now
> doesn't. It was known that cases like yours which call overloaded
> functions may require an explicit cast, something like "SELECT
> date_trunc('day', ?::timestamp)", but it was decided that it's better to
> force a cast in some cases than to do things demonstrably wrong in other
> cases (which the older driver versions were wont to do).
>
> You can dig through the archives for more of the gory details.
>
>

Actually I am not having this problem, but I see every time people has
problems with it.
Would not it be helpful to create, say, PostgreTimestamp extends
java.sql.Timestamp and PostgreZonedTimestamp extends java.sql.Timestamp.
Then check if one uses this classes in setTimestamp/setObject and in
this case use definite bindings. And even more, return one of this types
from getTimestamp, so that some applications that do read/write will
work better even without knowing this problem.
Other application, of course, will be forced to use ::timestamp OR this
new classes explicitly. But this gives more flexibility - one would may
choose to change query text OR parameter creating and it may be much
easier in some cases to change parameter creating then sql texts.

Am Mittwoch, 6. Dezember 2006 15:38 schrieb Mark Lewis:
> It's to work around the fact that JDBC only knows about one Timestamp
> type, while PG knows about more. It ends up being better in most cases
> to send the type as unknown and let the server disambiguate to the
> correct type than to guess about the type and maybe guess wrong.

OK, I found out that it used to bind such values as timestamptz, which failed
when the receiving type was a timestamp (without time zone). But it should
work the other way around, namely binding them as timestamp without time
zone, because that can be casted implicitly to timestamptz.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> OK, I found out that it used to bind such values as timestamptz, which failed
> when the receiving type was a timestamp (without time zone). But it should
> work the other way around, namely binding them as timestamp without time
> zone, because that can be casted implicitly to timestamptz.

Only for rather small values of "work" I'm afraid --- in particular,
that would cause any timezone specification in the input string to
be ignored, which is OK for timestamp without tz but surely not OK
for the other.

regards, tom lane