Re: Duplicate key error when updating unique columns

On May 7, 2005, at 1:10, Tom Lane wrote:

> Michael Glaesemann <grzm(at)myrealbox(dot)com> writes:
>> I thought that the unique key would be checked at the end of the
>> update
>> statement:
>
> No, it's never worked that way. Each row is checked as it is inserted.
> With an example like this, you may or may not get a failure depending
> on
> chance physical ordering of rows.
>
> This is not per spec, and I believe we have a TODO to fix it, but a fix
> is far from trivial.

Thanks for clarifying this point. I've since made a simple workaround
for my particular situation:

update a1 set a_id = (-1) * (a_id + 1);
update a1 set a_id = (-1) * (a_id);

This relies on all a_id values to have the same sign (or at least
satisfy unique(abs(a_id))), which is true in my case.

Thanks again!

Michael Glaesemann
grzm myrealbox com