Re: AUTO_INCREMENT patch

Moving to -hackers

> I'm working on getting auto-incrementing CMP fields in JBoss 3.2.1 to
> work with PostgreSQL data sources. There are currently two obstacles
> to this.

Yeah.. JBoss is very annoying in this regard. A temporary solution seems
to be to use BEFORE triggers to force the sequence to be used for the
default value. You could also do this with an INSTEAD rule (something
like the below):

CREATE OR REPLACE RULE rulename AS ON INSERT
TO tablename
DO INSTEAD
INSERT INTO tablename
( id, col1, ...)
VALUES ( DEFAULT, NEW.col1, ...);

AUTO_INCREMENT is non-standard (MySQL only?), however the SQL200X
proposals do have support for the more common IDENTITY syntax which can
accomplish the same job as well as many others. (PostgreSQL does NOT
have the general identity implementation yet)

What you're looking for is the ability to force the column to use the
IDENTITY even when the client provides a specific value:

CREATE TABLE test(col integer GENERATED ALWAYS AS IDENTITY);

See sections 10.22, 10.23, 11.3, and 11.4 of the SQL200X working draft
for full details.

ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-02-Foundation-2002-01.pdf

DB2 and MSSql have implementations of the IDENTITY syntax if you want a
reference point.