Re: unique & update

To do this create the table in the system without the unique.

Do all the insertions that you are wanting to do then use the create index
and create a unique index. This will solve the unique problem. Postgres is
working fine just now because the command you run gives a duplicate and so
the unique field flagged an error.

Syntax:
CREATE UNIQUE INDEX index_name ON areas(lft);

Hope this helps

Darren Ferguson

On Tue, 29 Jan 2002, Ben-Nes Michael wrote:

>
> > 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)
>