hibernate 2 and 3 both fail with the postgreSQL 8.0 jdbc drivers in the following way:

Whenever an attempt is made to insert something into the database with a null column (of apparently any type, bigint in my case), the psql 8.0 driver fails with an "Unknown types value" error. Searching other mailing lists reveals that this is due to a jdbc requirement that the correct type be specified even when setting a value to null, and the postgresql8.0 driver is apparently strict about enforcing this.

hibernate does work with the postgres 7.4 (and earlier) jdbc3 driver.

i'm not really sure whether hibernate or postgresql is at fault here. I last tried with the postgres 8.0 build310 driver and still had problems.

Are you pointing to a Postgres 8.0 database?

Hibernate ALWAYS complies with this JDBC requirement.

It is either a postgres driver bug, or a bug in a UserType or something like that.

Yes, i'm pointing at an 8.0 database, and it was/is happening with a bigint field which allows nulls.

At first I was using the driver that was packaged with the database itself.

This problem seems to be causing some grief for other (non-hibernate) postgresql users as well and it does look like the driver is probably broken, but sometimes it really is the rest of the world that is out of step (although this is looking less likely). There was a suggestion that the 310 build of the psql driver would fix it, and I tried that build, but it didn't.

generically, so far i've ran into the following issues attempting to keep my application database agnostic (suggestions/comments/constructive criticisms welcome):

postgresql: unknown types with 8.0 driver, workaround use 7.4 driver

postgresql: blobs don't work with hibernate, workaround use binary

mysql: mangles exact numeric types going through jdbc driver, no workaround found. (not willing to use a string or some other thing besides numeric for this field). They appear to be getting converted to float or double somewhere in the process and back again, causing things like 29.99 to turn into 29.99999998 somewhere between session.save and select price from table directly in the database. Also, things like 300.00 turn into an exception because its getting turned into 3e++2 (and mysql barfs on the 2 + signs). exact numerics work fine in the postgres drivers.

mysql: binary type defaults to tinyblob (max length 255), workaround, put length=2000000 in hibernate column mapping (forcing a mediumblob to be used) and hope that there isn't a database out there that takes that as an exact size. (is there a way to say min-length in a hibernate mapping?) (putting a length in the property itself doesn't seem to affect the sql type at all)

I don't want to think what will happen if I try to add oracle, db2, or sql-server to the mix now... (whats really bothersome is everyone claiming that their drivers pass testing, and then you run into issues like these...)

Hi,

the 312 build of the postgresql jdbc3 driver solves this bug.

Bye!