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 is done:

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 tbl_test_pkey constraint DETAILS: Key (testkey) = (2) already exists.

I need to increase each value of one column in one table, which is part of a complex unique constraint. How can I do this in one statement?

Thank!

---

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

I have one table of items organized in categories. Each item has a specific 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 such as:

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

, (foo4, bar4) (foo3, bar3) 2. , , category_position... - PK PostgreSQL, , SQL Server.

Answer 1

, , PK/ DML.

, :

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;

, , initially immediate, . , , set constraint.

---

, , : ? PK , , ( ).