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

Alban Hertroys skrev:
> Nis Jørgensen wrote:
>> If you can't wait, you are probably better off working around the
>> problem. Standard solution is to do:
>>
>> UPDATE  master SET m2 = -m2;
>> UPDATE  master SET m2 = -m2+1;
>>
>> or something similar.
> 
> Would something like
> 
> UPDATE master set m2 = master2.m2
>   FROM (
> 	SELECT m2 +1
> 	  FROM master m
> 	 WHERE m.master_id = master.master_id
> 	 ORDER BY m2 DESC
>  ) master2
> 
> work? I think it might be faster (and possibly cause less index bloat)
> than doing two consequent updates.


I don't understand your query. I don't think you can use a correlated
subquery in that way.

Anyway, tricks like these might work. They might stop working without
warning, if the plan changes. Relying on unspecified behavior is a
recipe for trouble.

Nis