Re: incrementing without violating a constraint

"Michael P. Soulier" <michael_soulier(at)mitel(dot)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)