On Tue, May 5, 2015 at 5:27 AM, Heikki Linnakangas <hlinn...@iki.fi> wrote: > To recap, there are three variants: > > A. INSERT ... ON CONFLICT DO NOTHING > > No arbiter is specified. This means that a conflict on any unique or > exclusion constraint is not allowed (and will do nothing instead). This > variant is only accepted for DO NOTHING. > > B. INSERT ... ON CONFLICT ON <constraint name> DO NOTHING/UPDATE > > In this variant, you explicitly specify the constraint by name. > > C. INSERT ... ON CONFLICT (<index params>) [WHERE <expression>] DO > NOTHING/UPDATE > > This specifies an index (or indexes, in the corner case that there are > several identical ones), by listing the columns/expressions and the > predicate for a partial index. The list of columns and WHERE match the > syntax for CREATE INDEX.
I would just say that there are two variants, only one of which mandates the inference clause. But I'm nitpicking. > That's pretty good overall. A few questions: Thanks. I'm glad that we are now able to cover really any conceivable use-case, while playing nice with every existing feature (now updatable views are supported with ON CONFLICT DO UPDATE -- and we're also going to be able to suppor subqueries in the UPDATE). We've been incredibly thorough. > 1. Why is the variant without specifying an index or constraint not allowed > with DO UPDATE? I agree it might not make much sense, but then again, it > might. If we're afraid that it's too unsafe to be the "default" if you don't > specify any constraint, how about allowing it with a more verbose "ON > CONFLICT ON ANY CONSTRAINT" syntax? I think it's dangerous. It's basically wrong headed to omit any constraint for DO UPDATE. I put a lot of effort into covering every possible case with the inference clause, and I think it's pretty cool that we have something that's so flexible. I don't feel bad about forcing users to be explicit about what they want, because the only conceivable downside is that they'll have to do a little extra typing (if even that - it's probably going to be ORM-generated more often than not). The upside -- not having their query break unexpectedly one day, when a new constraint is added -- is huge. > 2. Why can't you specify multiple constraints, even though we implicitly > allow "any" with the first variant? It's just an escape hatch. I don't want to encourage its over use, and I want to keep the grammar simple. > Finally, a couple of suggestions. It would be pretty handy to allow: > > INSERT ... ON CONFLICT ON PRIMARY KEY DO NOTHING/UPDATE Is that really likely to be less verbose than naming the attributes directly? > INSERT ... ON CONFLICT ON *CONSTRAINT* <constraint name> DO NOTHING/UPDATE > > That would allow the syntax can be expanded in the future to specify > conflicts on other kind of things. The "ON PRIMARY KEY" syntax should be > unambiguous with out, because PRIMARY is a reserved keyword, but for > example, we might want to add "ON UNIQUE INDEX <index name>" later. Okay, we can do that. -- 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
