title: Increment field with not null and unique constraint in PostgreSQL 8.3
body: <p>I have a table "items" with a column "position". position has a unique and not-null constraint. In order to insert a new row at position x I first try increment the positions of the subsequent items:</p>
  
  <pre><code>UPDATE items SET position = position + 1 WHERE position &gt;= x;
  </code></pre>
  
  <p>This results in a unique constraint violation:</p>
  
  <pre><code>ERROR:  duplicate key value violates unique constraint
  </code></pre>
  
  <p>The problem seems to be the order in which PostgreSQL performs the updates. Unique constraints in PostgreSQL &lt; 9.0 aren't deferrable and unfortunately using 9.0 is currently not an option. Also, the UPDATE statement doesn't support an ORDER BY clause and the following doesn't work, too (still duplicate key violation):</p>
  
  <pre><code>UPDATE items SET position = position + 1 WHERE id IN (
    SELECT id FROM items WHERE position &gt;= x ORDER BY position DESC)
  </code></pre>
  
  <p>Does somebody know a solution that doesn't involve iterating over all items in code?</p>