I was surprised to see that PostgreSQL doesn't execute a multiple row update as an atomic operation, but apparently one row at a time, with primary key uniqueness being checked after each row update.
For example, let's say we have this table :
CREATE TABLE mytable ( pos int PRIMARY KEY, t text );
into witch we insert two rows :
INSERT INTO mytable (pos,t) VALUES (1,'test1'); INSERT INTO mytable (pos,t) VALUES (2,'test2');
Then, in order to insert a new record in position 1, we first try this update to bump any existing position number by 1 :
UPDATE mytable SET pos = pos + 1;
This actually raises the error "ERROR: duplicate key violates unique constraint "mytable_pkey"".
I'd be interested in any suggestions of workaround for this.