Why is unique constraint needed for upsert? - Mailing list pgsql-general

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?

For an example of the latter approach, see MongoDB's flavor of upsert
[2]. You just give it a "query" and an "update". It seems to me this is
better because it functions correctly whether or not an index is in place.

Best, thanks,
Seamus


[1]
http://postgresql.uservoice.com/forums/21853-general/suggestions/245202-merge-upsert-replace
[2] http://docs.mongodb.org/manual/reference/method/db.collection.update/

--
Seamus Abshere, SCEA
https://github.com/seamusabshere