I'm new to PostgreSQL, but according to the docs, SERIAL and BIGSERIAL are similar to AUTO_INCREMENT in MySQL and IDENTITY in MS SQL.

Why then PostgreSQL dialect does not support identity generator?

PS. BTW, I'm using PostgreSQL 8.0 Beta 3 Dev 1. I guess it's possible that SERIAL & BIGSERIAL are new for 8.0 and did not exist in 7.x. Is this the reason the PostgreSQL dialect does not support the identity generator?

Any ideas?

Hi,

I don't know why Hibernate doesn't support them, but they're old old features of Postgresql.

Anyways, from what I've seen in the Hibernate source code, it shouldn't be that hard to add some support for them; the hardest part will be that you need to find out how to retrieve the generated-key-value after doing an insert.
I don't know off the top of my head how to do that but it shouldn't be hard to find in the manual or on the mailing lists.

Another thing to watch out for, is that when you drop the table you generally don't drop the associated sequence that was created behind-the-scenes by Postgresql. And that could, perhaps, be a problem.

cheers,

--Tim

Hi,

Out of curiosity I looked up the SQL needed to get the ID of the last inserted row, in PG...

Turns out you need to enquire on the sequence by name from which that ID came. So if you have a serial or bigserial column, you need to know the name of the sequence that PG creates for you behind the scenes. And then after inserting a row, you need to ask for the last value in that sequence.

I couldn't find in the docs for PG (version 8.0b4) any SQL function or statement that is similar to the functions last_insert_id or identity_val_local of MySQL and DB2.

As far as I can read the Hibernate source, there's no way to pass the name of the table when getting the SQL for getting the getIdentitySelectString() -- This method can thus not generate dynamically the sequence name.

So the upshot is that instead of using serial / bigserial as columntypes for your tables in PG, you need to work out how to tell Hibernate to use sequences in PG and use a sequence for assigning PK values.

The PG sequence support is implemented in the PG dialect of Hibernate, so I guess that it'll work and it will be functionally equivalent.

cheers,

--Tim