Updating a primary key

From: "Paul Mackay" <mackaypaul(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Updating a primary key
Date: 2006-03-22 11:32:11
Message-ID: 786c2f6d0603220332r38906cd0ud62699ec10387d60@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Terry Lee Tucker 2006-03-22 11:47:47 Re: Updating a primary key
Previous Message Jimbo1 2006-03-22 11:06:29 Advantages of PostgreSQL over MySQL 5.0