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
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 |