Re: Why is unique constraint needed for upsert?

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Wednesday, July 23, 2014 1:32 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Why is unique constraint needed for upsert?

On 7/23/2014 10:21 AM, Seamus Abshere wrote:
> hi all,
>
> Upsert is usually defined [1] in reference to a violating a unique key:
>
>> Insert, if unique constraint violation then update; or update, if not 
>> found then insert.
>
> 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?



-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast


Well, that's exactly why OP prefers Mongo, which doesn't care about such "small" things as ACID.

Regards,
Igor Neyman