On Mon, 2007-09-24 at 12:50 +0530, Anoo Sivadasan Pillai wrote:
CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;
INSERT INTO master VALUES ( 1, 'm1' ) ;
INSERT INTO master VALUES ( 2, 'm2' ) ;
UPDATE master SET m1 = m1 + 1;
Update fails with the message - ERROR: duplicate key violates unique constraint "master_pkey"
Primary key constraints are not deferred and are not deferrable (try: \x, then select * from pg_constraint where conname~'master_pkey'). This means that the constraint is checked immediately for each row updated. Success will depend on the order in which postgresql decides to visit rows: if it visits all rows in descending order, you'll be fine, but that's unlikely and uncontrollable (AFAIK).
Here's a sketch of an easy workaround. You might have to modify it for your particular range of m1.
begin;
update master set m1=-m1;
update master set m1=-m1+1;
commit;
You could just as easily add N to m1, then subtract (N-1) from m1. You'll need N>max(m1).
Good luck,
Reece