Shift (update) unique column values in PostgreSQL -

using ms sql server, following works fine:

create table #temptable(mykey int primary key)  insert #temptable values (1) insert #temptable values (2)  update #temptable set mykey=mykey+1 

however, using postgresql, following fails:

create table pg_temp.tbl_test(testkey integer primary key)  insert pg_temp.tbl_test values (1) insert pg_temp.tbl_test values (2)  update pg_temp.tbl_test set testkey=testkey+1 

error: duplicate key value violates unique constraint "tbl_test_pkey" detail: key (testkey)=(2) exists.

i need increment every value of 1 column in 1 table, part of composite unique constraint. how can in 1 statement ?

thanks !

---

edit: if wondering why makes sense (at least me), here more complete scenario.

i have 1 table of items organized in categories. each item has particular position in category.

category_id (pk) | category_position (pk) | item_attribute_1 | item_attribute_2 1 | 1 | foo | bar 1 | 2 | foo2 | bar2 2 | 1 | foo4 | bar4 2 | 2 | foo3 | bar3 

this table contains data like:

category1 : (foo, bar), (foo2, bar2) category2 : (foo4, bar4), (foo3, bar3) 

note (foo4, bar4) comes before (foo3, bar3) in category2. if want reorder items in 1 category, need update category_position... because of pk, cannot shift values using postgresql sql server.

this indeed bit confusing other constraints evaluated on statement level, pk/unique constraint evaluated on per row level during dml operations.

but can work around declaring primary key constraint deferrable:

create table tbl_test  (   testkey   integer,   constraint pk_tbl_test primary key (testkey) deferrable immediate );  insert tbl_test values (1), (2);  set constraints deferred;  update tbl_test    set testkey = testkey +1; 

deferred constraints have overhead, defining initially immediate overhead kept minimum. can defer constraint evaluation when need using set constraint.

---

the real question is: why need on primary key value? pk values has no meaning whatsoever, seems rather unnecessary increment values (regardless of dbms being used)