Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

On 19 May 2015 at 20:11, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> I'm sure we'll be asked these questions many times.
>
> Can you comment on whether the docs are sufficiently detailed to explain
> this answer?
>
​
Well http://www.postgresql.org/docs/devel/static/sql-insert.html explains
that a conflict_target clause is required but doesn't explain why.

It _does_ make clear that multiple UPDATEs to the same row are not allowed,
but that in itself doesn't automatically restrict the use of multiple
constraint targets; I could easily INSERT a set of values that would
trigger that failure with just one constraint target.

http://www.postgresql.org/docs/devel/static/sql-insert.html talks about how
MySQL's ON DUPLICATE can only act against the first matching row where
multiple constraints match against multiple rows. I suppose if that were
the case here (ie the first excluding row would stop other rows firing
against the UPDATE) would break the deterministic feature, but it's not
clear if that's true or not. I don't see why multiple target rows couldn't
be updated based on multiple constraints, that would not in-and-of-itself
break determinism.

If I'm missing the obvious, accept my apologies.

Geoff