title: Shift (update) unique column values in PostgreSQL
body: <p>Using MS SQL Server, the following works fine:</p>
  
  <pre><code>CREATE TABLE #temptable(mykey int primary key)
  
  INSERT INTO #temptable VALUES (1)
  INSERT INTO #temptable VALUES (2)
  
  UPDATE #temptable SET mykey=mykey+1
  </code></pre>
  
  <p>However, using PostgreSQL, the following fails:</p>
  
  <pre><code>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
  </code></pre>
  
  <blockquote>
    <p>ERROR:  duplicate key value violates unique constraint "tbl_test_pkey"
    DETAIL:  Key (testkey)=(2) already exists.</p>
  </blockquote>
  
  <p>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 ?</p>
  
  <p>Thanks !</p>
  
  <hr>
  
  <p>Edit: If you are wondering why this makes sense (at least to me), here is a more complete scenario.</p>
  
  <p>I have one table of items organized in categories. Each item has a particular position in the category.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This table contains data like:</p>
  
  <pre><code>category1 : (foo, bar), (foo2, bar2)
  category2 : (foo4, bar4), (foo3, bar3)
  </code></pre>
  
  <p>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.</p>