[GENERAL] Updating a primary key

[Paul Mackay]

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.Thanks,Paul

Re: [GENERAL] Updating a primary key

[Terry Lee Tucker]

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


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster