Does anyone use PostgreSQL with spring?

[bobmanc]

If so do you have any SERIAL columns? I am unable to get the generated keys returned to me.

[cmgharris]

To the best of my knowledge, PostgreSQL doesn't support this. I'm pretty sure it didn't in v. 7, and I'm not aware that's changed in 8.

From the manual:

The data types serial and bigserial are not true types, but merely a notational convenience for setting up unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying

CREATE TABLE tablename (
colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
);

Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator.

If you need to know the value for a row you insert, I beleive the recommended method is to select nextval('sequenceName') and then use that value for your insert into the field.

HTH

[ojs]

If so do you have any SERIAL columns? I am unable to get the generated keys returned to me.

PostgreSQL doesn't support SERIAL columns. This is considered as a very bad thing. PostgreSQL has support for sequnces which offers a lot more flexibility. Just ask if you have further questions...

[croco]

The PostgreSQL database suports the SERIAL type, but as far as I know the JDBC driver does not support returning the autogenerated keys (yet).

[ojs]

The PostgreSQL database suports the SERIAL type, but as far as I know the JDBC driver does not support returning the autogenerated keys (yet).

No, there's no serial datatype. PostgreSQL has a serial keyword that - if it is used as a type - automatically creates a sequence and sets a nextval() function call for this column.

Example:

CREATE TABLE foo(id serial);

is the same as:

CREATE SEQUENCE foo_id;
CREATE TABLE foo(id integer not null default nextval('foo_id_seq'::regclass));

The only difference is, that in the firstcase a drop table cascades and also deletes the sequence - this isn't the case if the sequence is created manually.


So, there is a serial. But this serial is just used for creating sequences in a convenient way. What the initial author was asking for (if I understood him correctly) is "auto increment" - and that isn't supported by PostgreSQL.

Best
Oliver