Re: Why is unique constraint needed for upsert?

John R Pierce <pierce(at)hogranch(dot)com> writes:
> On 7/23/2014 10:21 AM, Seamus Abshere wrote:
>> Upsert is usually defined [1] in reference to a violating a unique key:
>> Is this theoretically preferable to just looking for a row that
>> matches certain criteria, updating it if found or inserting otherwise?

> what happens when two connections do this more or less concurrently, in
> transactions?

For the OP's benefit --- the subtext John left unstated is that the
unique-key mechanism has already solved the problem of preventing
concurrent updates from creating duplicate keys. If we build a version of
UPSERT that doesn't rely on a unique index then it'll need some entirely
new mechanism to prevent concurrent key insertion. (And if you don't care
about concurrent cases, you don't really need UPSERT ...)

regards, tom lane