John R Pierce <pierce@xxxxxxxxxxxx> 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
