Re: MERGE SQL Statement for PG11

Can I add my 2c worth, as someone without a horse in the race, as it
were, in the hope that telling me how I've got this wrong might
clarify the argument a bit (or at least you can all start shouting at
me rather than each other :) )

The point of merge is to allow you to choose to either INSERT or
UPDATE (or indeed DELETE) records based on existing state, yes? That
state is whatever the state of the system at the start of the
transaction?

If I understand correctly, the only time when this would be
problematic is if you try to insert a record into a table which would
not allow that INSERT because another transaction has performed an
INSERT by the time the COMMIT happens, and where that new record would
have changed the state of the MERGE clause, yes?

Isn't the only reason this would fail if there is a unique constraint
on that table?

Yes, you could end up INSERTing values from the merge when another
transaction has INSERTed another, but (again, unless I've
misunderstood) there's nothing in the spec that says that shouldn't
happen; meanwhile for those tables that do require unique values you
can use the UPSERT mechanism, no?

Geoff