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

On 25/09/2007, Anoo Sivadasan Pillai <aspillai(at)in(dot)rm(dot)com> wrote:
>
>
>
>
> Hi,
>
> On further testing I found the same behaviour in Unique keys too, The following batch can reproduce the behaviour.
>
> CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;
>
> INSERT INTO master VALUES ( 1, 1 ) ;
>
> INSERT INTO master VALUES ( 2, 2) ;
>
> UPDATE master SET m2 = m2 + 1;
>
>

Isn't this expected behavior? When you update the m2 of the first
record, it becomes 2 and violates the unqiue constraint as the second
row already has an m2 value of 2.

I have missed the thread, but this reeks of an unusual DB design. If
your really want the updates to go in reverse order so that keys are
not violated, you can do that in your application -- SELECT the keys
you wish to update sorted in DESC order by m2, and then foreach of
them, just increment the value accordingly. If you wrap this in a
transaction, it'll be pretty fast, depending on how many rows you're
talking about (I've found great speeds on records up to 50,000 for a
live DB).