On 28/04/15 03:51, Peter Geoghegan wrote:
On Mon, Apr 27, 2015 at 4:21 PM, Peter Geoghegan <p...@heroku.com> wrote:
* Don't change the ON CONFLICT spelling.
* Don't change the names of the pseudo-alias EXCLUDED.* (or the alias
TARGET.*). Those seem fine to me as well.
* Change the syntax to put the WHERE clause used to infer partial
indexes outside parens.
* 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.
I've pushed code that does all this to Github. Documentation changes
will follow soon.
* 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 have yet to do this, but it should be fairly straightforward.
Most of this sounds reasonable to me. However I still dislike the "ON
CONFLICT ON" part, the idea of WITH Andres suggested feels better.
I am also very sure that every time I'll write this statement I will
have to look into manual for the names of TARGET and EXCLUDED because
they don't seem intuitive to me at all (especially the EXCLUDED).
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
