Shift (update) unique column values in PostgreSQL

Question

Using MS SQL Server, the following works fine:

CREATE TABLE #temptable(mykey int primary key)

INSERT INTO #temptable VALUES (1)
INSERT INTO #temptable VALUES (2)

UPDATE #temptable SET mykey=mykey+1

However, using PostgreSQL, the following fails:

CREATE TABLE pg_temp.tbl_test(testkey integer primary key)

INSERT INTO pg_temp.tbl_test VALUES (1)
INSERT INTO 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) already exists.

I need to increment every value of one column in one table, which is part of a composite unique constraint. How can I do this in one statement ?

Thanks !

---

Edit: If you are wondering why this makes sense (at least to me), here is a more complete scenario.

I have one table of items organized in categories. Each item has a particular position in the 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 that (foo4, bar4) comes before (foo3, bar3) in category2. Now if I want to reorder items in one category, I need to update category_position... But because of the PK, I cannot shift values using PostgreSQL as I could with SQL Server.

Answer 1

This is indeed a bit confusing as all other constraints are evaluated on a statement level, only PK/unique constraint are evaluated on a per row level during DML operations.

But you can work around that by declaring the primary key constraint as deferrable:

create table tbl_test 
(
  testkey   INTEGER,
  constraint pk_tbl_test primary key (testkey) deferrable initially immediate
);

insert into tbl_test values (1), (2);

set constraints all deferred;

update tbl_test
   set testkey = testkey +1;

Deferred constraints do have some overhead, so by defining it as initially immediate this overhead is kept to a minimum. You can the defer the constraint evaluation when you need it by using set constraint.

---

The real question however is: why would you need to do this on a primary key value? The PK values has no meaning whatsoever, so it seems rather unnecessary to increment all values (regardless of the DBMS being used)

Answer 2

Solution without altering constraint as deferrable initially immediate

UPDATE tbl_test t1 
SET    testkey = t2.testkey + 1 
FROM   (SELECT testkey 
    FROM   tbl_test 
    ORDER  BY testkey DESC) t2 
WHERE  t1.testkey = t2.testkey 

Online example: http://rextester.com/edit/GMJ48099