On Mon, Apr 27, 2015 at 7:21 PM, Peter Geoghegan <p...@heroku.com> wrote: > On Mon, Apr 27, 2015 at 10:20 AM, Bruce Momjian <br...@momjian.us> wrote: >> Agreed, and I like the DO [ UPDATE | NOTHING ] too. > > Here is what I think I need to do: > > * Don't change the ON CONFLICT spelling.
What I proposed originally was ON DUPLICATE. Not ON CONFLICT. And I still like that better. ON UNIQUE CONFLICT, which Andres mentioned, gets us there too, but it's > * Don't change the names of the pseudo-alias EXCLUDED.* (or the alias > TARGET.*). Those seem fine to me as well. There seem to be a few votes for NEW and OLD. That's what I proposed originally, and (surprise, surprise) I still like that better too. > * Change the syntax to put the WHERE clause used to infer partial > indexes outside parens. +1. > * Change the syntax to make this work, by adding the fully reserved > keyword DO. Assuming you have a partial index (WHERE is_active) on the > column "key", you're left with: > > INSERT .... ON CONFLICT (key) where is_active DO UPDATE set ... WHERE ... ; > > or: > > INSERT .... ON CONFLICT (key) where is_active DO NOTHING; > > The DO keyword makes this work where it cannot otherwise, because it's > a RESERVED_KEYWORD. Seems fine. > * Finally, add "ON CONFLICT ON CONSTRAINT my_constraint" support, so > you can name (exactly one) constraint by name. Particularly useful for > unique constraints. I really don't want to make this accept multiple > constraints, even though we may infer multiple constraints, because > messy, and is probably too complex to every be put to good use > (bearing in mind that exclusion constraints, that really need this, > will still only be supported by the IGNORE/DO NOTHING variant). I still think that constraints should never be named in the syntax. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers