On Tue, May 19, 2015 at 12:57 PM, Geoff Winkless <pgsqlad...@geoff.dj> wrote: > Well http://www.postgresql.org/docs/devel/static/sql-insert.html explains > that a conflict_target clause is required but doesn't explain why.
Yes, for ON CONFLICT DO UPDATE, it is mandatory. > 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. True. > 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. 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. The only reason I can see for wanting to do this is where you're running a migration or something, and two unique indexes are equivalent anyway. Like maybe you have a partial index and a non-partial index, and you're just about to drop one of them. But the inference specification will do the right thing here anyway -- multiple unique indexes can be inferred for edge cases like this. 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. Unless perhaps you have a different UPDATE targetlist and so on corresponding to that case, which is currently not possible -- but then what if multiple constraints have would-be violations at the same time? It gets awfully messy very quickly. What do you have in mind? -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers