Skip site navigation (1) Skip section navigation (2)

Re: unique & update

From: Juan Carlos Moscardó Pérez <jcmoscardo(at)dorna(dot)es>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Re: unique & update
Date: 2002-01-29 14:28:57
Message-ID: 005501c1a8d1$4a20cf60$d18da8c0@taz (view raw or flat)
Thread:
Lists: pgsql-general
----- Original Message -----
From: "Ben-Nes Michael" <miki(at)canaan(dot)co(dot)il>
To: "Frank Schafer" <frank(dot)schafer(at)setuza(dot)cz>;
<pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, January 29, 2002 2:52 PM
Subject: Re: [GENERAL] unique & update


>
> > On Tue, 2002-01-29 at 13:38, Ben-Nes Michael wrote:
> > > Hi All
> > >
> > > 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
> > > );
> >
> > O.K. ... we used a column constraint ...
> >
> > > I inserted 3 rows with value: 1, 2, 3
> >
> > ... everything is unique, so all is fine ...
> >
> > > now im trying to update:
> > > update areas set lft = lft +1;
> >
> > ... update works on the first row, so the table would be 2, 2, 3 ...
> >
> > > Then I get the UNIQUE problem.
> >
> > ... what is exectly what the unique constraint is for.
> >
> > > Is there a way to do UNIQUE check after the whole table got updated ?
> > > If not, any ideas to walk around the problem ?
> >
> > Try:
> > CREATE TABLE areas (
> > UNIQUE ( lft ),
> > lft INT
> > );
>
> I tried your syntax and the following with no success ( same problem)
> CREATE TABLE areas (
> lft INT,
> CONSTRAINT testunique UNIQUE ( lft )
> );
>
> >
> > Then we have a table with a table constraint.
> > I'm only guessing ( using ``\h CREATE TABLE'' on the psql prompt ) and
> > I'm a PostgreSQL -- and in fact SQL -- newbie, but if I'm interpreting
> > the syntax diagram and MY mind about these constraints right, this
> > should do what you need.
> >
> > Regards
> > Frank
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>


In response to

pgsql-general by date

Next:From: Darren FergusonDate: 2002-01-29 14:29:34
Subject: Re: unique & update
Previous:From: Ben-Nes MichaelDate: 2002-01-29 13:52:18
Subject: Re: unique & update

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group