From fillons@offratel.nc Tue Sep 14 18:16:13 1999 Received: from mail_dns.lagoon.nc (mail.offratel.nc [209.58.55.28]) by hub.org (8.9.3/8.9.3) with ESMTP id OAA36045; Tue, 14 Sep 1999 14:16:13 -0400 (EDT) (envelope-from fillons@offratel.nc) Received: from portable (unverified [209.58.55.166]) by mail_dns.lagoon.nc (Rockliffe SMTPRA 3.4.3) with SMTP id ; Wed, 15 Sep 1999 05:17:00 +1100 Message-ID: <000f01befedd$3f26fda0$a6373ad1@portable> From: "=?iso-8859-1?Q?St=E9phane_FILLON?=" To: "pgsql-sql" , "pgsql-hackers" , "pgsql-general" Subject: BUG with UNIQUE clause Date: Wed, 15 Sep 1999 05:14:15 +1100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000B_01BEFF39.27AFBD40" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 4.72.3110.5 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 This is a multi-part message in MIME format. ------=_NextPart_000_000B_01BEFF39.27AFBD40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, 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: create table cltclt001( tcid int2, tcnom text unique ); 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 ? Thanks in advance. Stephane FILLON ------=_NextPart_000_000B_01BEFF39.27AFBD40 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
The UNIQUE constraint doesn't work = on a field if=20 I use a DEFAULT clause on a table.
 
The following table works with = UNIQUE=20 constraint:
 
create table cltclt001(
  tcid int2,
  tcnom text = unique
);
 
but this one accept several same = tcnom=20 value:
 
create table cltclt001(
  tcid int2 default=20 nextval('cltcls001'),
  tcnom text = unique
);
 
 
What's wrong with my table ?
 
Thanks in advance.
 
Stephane FILLON
------=_NextPart_000_000B_01BEFF39.27AFBD40-- From lockhart@alumni.caltech.edu Wed Sep 15 01:53:30 1999 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 From tgl@sss.pgh.pa.us Wed Sep 15 13:47:23 1999 Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA74437; Wed, 15 Sep 1999 09:47:23 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id JAA19037; Wed, 15 Sep 1999 09:46:35 -0400 (EDT) To: "=?iso-8859-1?Q?St=E9phane_FILLON?=" cc: "pgsql-sql" , "pgsql-hackers" , "pgsql-general" Subject: Re: [HACKERS] BUG with UNIQUE clause In-reply-to: Your message of Wed, 15 Sep 1999 05:14:15 +1100 <000f01befedd$3f26fda0$a6373ad1@portable> Date: Wed, 15 Sep 1999 09:46:35 -0400 Message-ID: <19035.937403195@sss.pgh.pa.us> From: Tom Lane "=?iso-8859-1?Q?St=E9phane_FILLON?=" writes: > The UNIQUE constraint doesn't work on a field if I use a DEFAULT clause > on a table. This sounds closely related to a fix that Thomas Lockhart just made. IIRC the complained-of symptom was that PRIMARY KEY on one column plus UNIQUE on another didn't work, but the real problem was that PRIMARY KEY implies UNIQUE and the table declaration code was getting confused by two different UNIQUE columns in one table. It could be that his fix addresses your problem too. Check the pghackers archives for the last couple weeks to find the patch. regards, tom lane