Shift (update) Unique Column Values In Postgresql

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 #temptabl

Solution 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:

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

insertinto 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)

Solution 2:

Solution without altering constraint as deferrable initially immediate

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

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