Received: from localhost (IDENT:root@hectic-2.jpl.nasa.gov [128.149.68.204]) by hub.org (8.9.3/8.9.3) with ESMTP id VAA96589 for ; Tue, 14 Sep 1999 21:53:30 -0400 (EDT) (envelope-from lockhart@alumni.caltech.edu) Received: from alumni.caltech.edu (lockhart@localhost [127.0.0.1]) by localhost (8.8.7/8.8.7) with ESMTP id BAA24518; Wed, 15 Sep 1999 01:52:43 GMT Sender: lockhart@hub.org Message-ID: <37DEFBEB.8D75D0FB@alumni.caltech.edu> Date: Wed, 15 Sep 1999 01:52:43 +0000 From: Thomas Lockhart X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.0.36 i686) X-Accept-Language: en MIME-Version: 1.0 To: =?iso-8859-1?Q?St=E9phane?= FILLON CC: pgsql-general Subject: Re: [HACKERS] BUG with UNIQUE clause References: <000f01befedd$3f26fda0$a6373ad1@portable> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit > 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 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