Re: Problems with function pg_catalog.date_part("unknown", "unknown") is not unique

#1

Michael Ruf <usenet.rufmichael (AT) gmx (DOT) de> wrote:

Quote:

i've two postgres databases. On one the following sql works on the other
i got an error.

ok, it seems that it has something todo with the used jdbc driver. With
version 8.1dev (400) it works. With the current stable version 8.1(407)
it fails.

As i trcked down the code uses a PreparedStatment SELECT member_id FROM
member where Extract( month from ? ) = Extract( month from attribute_44
) Order by member_id ASC

than stmt.setTimeStamp() is called. attribute_44 is a date column.

Is this a bug in the jdbc driver?

I don't know, but could you turn on statement logging in the database
and see what statement the server actually gets when you are using
version 8.1dev (400)?

That might help understand why it works in that case.

Yours,
Laurenz Albe

#2

Michael Ruf <usenet.rufmichael (AT) gmx (DOT) de> wrote:

Quote:

i've two postgres databases. On one the following sql works on the other
i got an error.
ok, it seems that it has something todo with the used jdbc driver. With
version 8.1dev (400) it works. With the current stable version 8.1(407)
it fails.

As i trcked down the code uses a PreparedStatment SELECT member_id FROM
member where Extract( month from ? ) = Extract( month from attribute_44
) Order by member_id ASC

than stmt.setTimeStamp() is called. attribute_44 is a date column.

Is this a bug in the jdbc driver?

I looked into it, and (unfortunately for you) things are working
as designed.

This is a deliberate change made in 2005-08-01, see
http://archives.postgresql.org/pgsql...8/msg00000.php

The setTimestamp() method passes the timestamp parameter now as type
"invalid" instead of "timestamp with time zone". This is to overcome a
problem that caused unexpected results when a timestamp with time zone
was passed to a function that expected a timestamp.

The idea is that now the server figures out what to do automatically.
The drawback is that you'll get errors as the one you observe sometimes :^(

The deeper problem is that java.sql.Timestamp has no time zone support,
so there is no "good" way to fix that.

If 8.1dev (400) does it for you, despite being a development version,
you can work around your problem for now.
But I am afraid that eventually (e.g. when you have to upgrade), the code
will have to be changed to eliminate the ambiguity with a type cast.

Yours,
Laurenz Albe

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Problems with function pg_catalog.date_part("unknown", "unknown") is not unique

comp.databases.postgresql comp.databases.postgresql

Re: Problems with function pg_catalog.date_part("unknown", "unknown") is not unique - 11-03-2006 , 07:09 AM

Re: Problems with function pg_catalog.date_part("unknown", "unknown") is not unique - 11-06-2006 , 06:30 AM