On Wednesday 22 March 2006 06:32 am, Paul Mackay saith: > 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. Actually, I think its done before the update, but I'm not sure and I'm certainly not a developer of Postgres. > > 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. > > Thanks, > Paul We do things like this in plpgsql using a loop. We go backwards from the end making updates to the point where the new record is to be inserted. I'm sure others have more exotic methods. HTH
