4

I want to update the primary key in several rows of a table. If all rows were updated, the key would be unique again, but the update of the first row results in a temporary conflict with the key of the second row. Is there an elegant way to solve this?

Example:

create table erichtest ( i integer, v varchar(200) );
alter table erichtest add constraint pk_erichtest primary key(i);
insert into erichtest values(1, 'Eins');
insert into erichtest values(2, 'Zwei');
update erichtest set i=i+1;

ERROR: duplicate key value violates unique constraint "pk_erichtest"

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
Erich Kitzmueller
  • 36,381
  • 5
  • 80
  • 102
  • 2
    `DEFERRED` is the term here. – joop Dec 06 '16 at 09:58
  • 1
    Maybe dump and import the table. But why update primary key? It should not relate directly to the record. – kometen Dec 06 '16 at 10:03
  • @kometen: In my case, it's a small table and the user can rearrange the order in which the items in this table appear. If the user moves the third item to the first position, the actual `update` statement goes like this: `update foobar set i=case when i=3 then 1 else i+1 end where i between 1 and 3`. Maybe `i` is not a good choice for the primary key, but in Oracle this worked as expected... and since we use Slony-I for replication, we need a primary key. – Erich Kitzmueller Dec 06 '16 at 10:10
  • I see. Maybe this thread is useful: http://stackoverflow.com/questions/2499246/how-to-update-primary-key – kometen Dec 06 '16 at 10:20

2 Answers2

5

Something like this should help:

b=# begin;
BEGIN
b=# alter table erichtest drop constraint pk_erichtest ;
ALTER TABLE
b=#  alter table erichtest add constraint pk_erichtest primary key (i) DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE
b=# set constraints pk_erichtest deferred ;
SET CONSTRAINTS
b=# update erichtest set i=i+1;
UPDATE 2
b=# select * from erichtest ;
 i |  v
---+------
 2 | Eins
 3 | Zwei
(2 rows)

b=# end;
COMMIT
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
1

Add 10 subtract 9:

update erichtest set i=i+10;
update erichtest set i=i-9;
mehmet
  • 7,720
  • 5
  • 42
  • 48