Thread: Updating a primary key

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

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