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

 

OK, let me summarise. First, thanks for putting time into this feature; we all wish to see it work and work well.

The current ON CONFLICT syntax requires us to specify one-and-only-one conflict_target/conflict_action pair. I would like to be able to specify 0, 1 or more conflict_targets, as the developer desires.

It is very desirable to be able to specify DO UPDATE without any conflict_target, relying instead on our ability to infer a conflict_target deterministically. That is the way other systems work and we should be aiming to provide similar ease of use. Having said that, we all recognize that MySQL is broken for multiple constraints and we have done well to come up with a design that allows us to specify finer grained control when we have multiple constraints. (Ideally, we would use the identical syntax to MySQL, but that is secondary to simply avoiding specifying a conflict_target).

If we do have multiple constraints then we should be allowed to specify multiple conflict_target/conflict_action pairs (or similar), since few people believe that one conflict_action would cover the various permutations that occur with multiple potential constraint failures.