Re: unique & update - Mailing list pgsql-general

Sorry for the last email...

Hi!.
Try to use the deferred type of constraint, i'm not pretty sure about the
exact syntax.

http://www.ninthwonder.com/info/postgres/user/sql-createtable.htm

UNIQUE clause
SQL92 specifies some additional capabilities for UNIQUE:

Table Constraint definition

      [ CONSTRAINT name ]
      UNIQUE ( column [, ...] )
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]

Column Constraint definition

      [ CONSTRAINT name ]
      UNIQUE
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]

Also try with
SET CONSTRAINTS ALL DEFERRED;

In a transaction. I'm not 100% if it can be the way. I'm sorry, if it can't
help you.

Best regards

Juan Carlos

P.D.: Sorry about my english :)




> 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@postgresql.org)
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>