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

On 19 May 2015 at 21:12, Peter Geoghegan <pg(at)heroku(dot)com> wrote:

> It's trivial to modify Postgres to not require that a specific unique
> index be inferred, so that you can omit the inference specification
> for DO UPDATE just as you can for DO NOTHING. That would make it work
> in a similar way to MySQL; whatever actually conflict was detected
> would be assumed to be cause to take the alternative update path.
>

​Except that would break the deterministic behaviour, surely? Because if
you only updated one row based on which constraint matched first, the row
that was updated would depend on the order in which the constraints were
evaluated, yes? I was expecting that matching two constraints would end up
UPDATEing two separate rows.

I have a hard time imagining why you'd ever not want to be explicit
> about what to take the alternative path on for the DO UPDATE variant.
>
> What do you have in mind?

If I'm being honest, my main driver is laziness :) I don't mind specifying
the constraint if I can understand why it's required, but otherwise it just
seems like I need to do more typing for no reason. Especially when there's
only one unique constraint on a table.

Geoff