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