Re: unique & update

From: Ben-Nes Michael <miki(at)canaan(dot)co(dot)il>
To: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: unique & update
Date: 2002-01-30 08:03:00
Message-ID: 200201300803.g0U830M01631@mikispc.canaan.co.il
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Tuesday 29 January 2002 19:13, Stephan Szabo wrote:
> On Tue, 29 Jan 2002, Ben-Nes Michael wrote:
> > Im into SQL trees trying to work with CELKO way to do it:
> > http://www.intelligententerprise.com/001020/celko.shtml
> >
> > The problem is that if I try to add a new descendent which is not the
> > most right sibling I get UNIQUE error.
> >
> > This occur ( if im right ) when the update try to update the lft column
> > which is UNIQUE
> >
> > Example of table:
> >
> > CREATE TABLE areas (
> > lft INT UNIQUE
> > );
> >
> > I inserted 3 rows with value: 1, 2, 3
> >
> > now im trying to update:
> > update areas set lft = lft +1;
> >
> > Then I get the UNIQUE problem.
> >
> > Is there a way to do UNIQUE check after the whole table got updated ?
> > If not, any ideas to walk around the problem ?
>
> Unfortunately no, we don't support deferred unique constraint afaik and
> the unique constraint we have isn't quite correct according to spec (yes,
> the above should work). Technically we should be checking the after all
> updates have occurred, but iirc we do it on the insert into the index
> which is why this happens.
Will 7.2 support it ?
If not do you have estimate time to when it will be supported ?

Can you tip me how to create trigger thats do uniqueness check ?

>
> The closest thing I could think of would be a constraint trigger that did
> a uniqueness check but that'll probably be somewhat slower (a trigger that
> looks for something like: select lft from areas group by lft having
> count(*)>1;)

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alexander Pucher 2002-01-30 09:37:56 Size of Large Object
Previous Message Devrim GUNDUZ 2002-01-30 07:47:54 Re: Pg_dump options