Re: [HACKERS] BUG with UNIQUE clause

> The UNIQUE constraint doesn't work on a field if I use a DEFAULT
> clause on a table.
> The following table works with UNIQUE constraint:
> but this one accept several same tcnom value:
> create table cltclt001(
>   tcid int2 default nextval('cltcls001'),
>   tcnom text unique
> );
> What's wrong with my table ?

Nothing. You have stumbled across a bug recently discovered by Mark
Dalphin <mdalphin@amgen.com> in the parser. It was repaired in the
source trees 1999-08-15 so will appear in v6.5.2 (any day now) and
v6.6.

postgres=> create sequence cltcls001;
CREATE
postgres=> insert into cltclt001 (tcnom) values ('one');
INSERT 150559 1
postgres=> insert into cltclt001 (tcnom) values ('one');
ERROR:  Cannot insert a duplicate key into a unique index

I imagine that the repair is posted to the patches or hacker's mailing
list; look in the archives around that date and you should be able to
patch your existing recent system.

Good luck.

                   - Thomas

--
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California