Re: AUTO_INCREMENT patch - Mailing list pgsql-hackers

From Rod Taylor
Subject Re: AUTO_INCREMENT patch
Date
Msg-id 1059855994.43336.113.camel@jester
Whole thread Raw
Responses Re: AUTO_INCREMENT patch
Re: AUTO_INCREMENT patch
List pgsql-hackers
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.

pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: pgindent run coming
Next
From: des@des.no (Dag-Erling Smørgrav)
Date:
Subject: Re: AUTO_INCREMENT patch