Increment field with not null and unique constraint in PostgreSQL 8.3

Question

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:

UPDATE items SET position = position + 1 WHERE position >= x;

This results in a unique constraint violation:

ERROR:  duplicate key value violates unique constraint

The problem seems to be the order in which PostgreSQL performs the updates. Unique constraints in PostgreSQL < 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):

UPDATE items SET position = position + 1 WHERE id IN (
  SELECT id FROM items WHERE position >= x ORDER BY position DESC)

Does somebody know a solution that doesn't involve iterating over all items in code?

Answer 1

Hi,
as PostgreSQL supports full set of transactional DDL, you can easily do something like this:

create table utest(id integer unique not null);
insert into utest(id) select generate_series(1,4);

The table looks now like this:

test=# \d utest
     Table "public.utest"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
Indexes:
    "utest_id_key" UNIQUE, btree (id)

test=# select * from utest;
 id 
----
  1
  2
  3
  4
(4 rows)

And now the whole magic:

begin;
alter table utest drop constraint utest_id_key;
update utest set id = id + 1;
alter table utest add constraint utest_id_key unique(id);
commit;

After that we have:

test=# \d utest
     Table "public.utest"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
Indexes:
    "utest_id_key" UNIQUE, btree (id)

test=# select * from utest;
 id 
----
  2
  3
  4
  5
(4 rows)

This solution has one drawback: it needs to lock the whole table, but maybe this is not a problem here.

Answer 2

Another table, with multiple unique index:

create table utest(id integer, position integer not null, unique(id, position));
test=# \d utest
      Table "public.utest"
  Column  |  Type   | Modifiers 
----------+---------+-----------
 id       | integer | 
 position | integer | not null
Indexes:
    "utest_id_key" UNIQUE, btree (id, "position")

Some data:

insert into utest(id, position) select generate_series(1,3), 1;
insert into utest(id, position) select generate_series(1,3), 2;
insert into utest(id, position) select generate_series(1,3), 3;

test=# select * from utest order by id, position;
 id | position 
----+----------
  1 |        1
  1 |        2
  1 |        3
  2 |        1
  2 |        2
  2 |        3
  3 |        1
  3 |        2
  3 |        3
(9 rows)

I created a procedure that updates position values in the proper order:

create or replace function update_positions(i integer, p integer) 
  returns void as $$
declare
  temprec record;
begin
  for temprec in 
    select * 
      from utest u 
      where id = i and position >= p 
      order by position desc 
  loop
    raise notice 'Id = [%], Moving % to %', 
      i, 
      temprec.position, 
      temprec.position+1;

    update utest 
      set position = position+1 
      where position=temprec.position and id = i;
  end loop;
end;
$$ language plpgsql;

Some tests:

test=# select * from update_positions(1, 2);
NOTICE:  Id = [1], Moving 3 to 4
NOTICE:  Id = [1], Moving 2 to 3
 update_positions 
------------------

(1 row)

test=# select * from utest order by id, position;
 id | position 
----+----------
  1 |        1
  1 |        3
  1 |        4
  2 |        1
  2 |        2
  2 |        3
  3 |        1
  3 |        2
  3 |        3
(9 rows)

Hope it helps.