Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

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

-- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0