pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG with UNIQUE clause
3+ messages / 3 participants
[nested] [flat]

* BUG with UNIQUE clause
@ 1999-09-14 18:14 Stéphane FILLON <fillons@offratel.nc>
  1999-09-15 01:52 ` Re: [HACKERS] BUG with UNIQUE clause Thomas Lockhart <lockhart@alumni.caltech.edu>
  1999-09-15 13:46 ` Re: [HACKERS] BUG with UNIQUE clause Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 3+ messages in thread

From: Stéphane FILLON @ 1999-09-14 18:14 UTC (permalink / raw)
  To: pgsql-sql; pgsql-hackers <pgsql-hackers@postgresql.org>; pgsql-general <pgsql-general@postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [HACKERS] BUG with UNIQUE clause
  1999-09-14 18:14 BUG with UNIQUE clause Stéphane FILLON <fillons@offratel.nc>
@ 1999-09-15 01:52 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
  1 sibling, 0 replies; 3+ messages in thread

From: Thomas Lockhart @ 1999-09-15 01:52 UTC (permalink / raw)
  To: Stéphane FILLON <fillons@offratel.nc>; +Cc: pgsql-general <pgsql-general@postgreSQL.org>

> 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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [HACKERS] BUG with UNIQUE clause
  1999-09-14 18:14 BUG with UNIQUE clause Stéphane FILLON <fillons@offratel.nc>
@ 1999-09-15 13:46 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 1999-09-15 13:46 UTC (permalink / raw)
  To: Stéphane FILLON <fillons@offratel.nc>; +Cc: pgsql-sql; pgsql-hackers <pgsql-hackers@postgreSQL.org>; pgsql-general <pgsql-general@postgreSQL.org>

"=?iso-8859-1?Q?St=E9phane_FILLON?=" <fillons@offratel.nc> 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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-09-15 13:46 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-09-14 18:14 BUG with UNIQUE clause Stéphane FILLON <fillons@offratel.nc>
1999-09-15 01:52 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-09-15 13:46 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox