Re: incrementing without violating a constraint

"Michael P. Soulier" <michael_soulier@mitel.com> hat am 20. Januar 2014 um 15:33
geschrieben:
> Hi,
>
> I have a uniqueness constraint on an integer value in a table where I would
> like to mass increment all of the existing rows.
>
> ie.
> update rules set rule_number = rule_number + 1;
>
> This violates the uniquness constraint. Is there a way to say, turn off the
> constraint, run the update and then turn it back on?
>
> Thanks,
> Mike

test=# \d mike
     Table "public.mike"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer | not null
Indexes:
    "mike_pkey" PRIMARY KEY, btree (id)

test=# begin;
BEGIN
test=*# alter table mike drop constraint "mike_pkey";
ALTER TABLE
test=*# update mike set id=id+1;
UPDATE 3
test=*# alter table mike add primary key (id);
ALTER TABLE
test=*# select * from mike ;
 id
----
  2
  3
  4
(3 rows)