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
