Re: INSERT ... ON CONFLICT syntax issues

On 28/04/15 03:51, Peter Geoghegan wrote:
> On Mon, Apr 27, 2015 at 4:21 PM, Peter Geoghegan <pg(at)heroku(dot)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