Naz Gassiep <naz@mira.net> writes:
> I would like more information on this deficiency and what causes it so I
> know when to anticipate it.
The uniqueness constraint is checked on a row-by-row basis, so if you
update one row to hold the same value as another row holds, you get an
error immediately. It doesn't matter that if the query had been allowed
to finish, it would have updated that other row to some non-conflicting
value. (You might be able to work around this if you could control the
order in which rows are updated, but you can't.)
This is not what the SQL spec says should happen, but so far no one has
proposed a reimplementation that doesn't give up unreasonable amounts
of performance. It's on the TODO list ...
regards, tom lane