Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On Wed, Oct 8, 2014 at 6:25 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> I change my view on this, after some more thought. (Hope that helps)

Great.

> If we implement MERGE, I can see we may also wish to implement MERGE
> CONCURRENTLY one day. That would be different to UPSERT.
>
> So in the future I think we will need 3 commands
>
> 1. MERGE
> 2. MERGE CONCURRENTLY
> 3. UPSERT
>
> So we no longer need to have the command start with the MERGE keyword.

As I've outlined, I don't see how MERGE CONCURRENTLY could ever work,
but I'm glad that you agree that it should not block this effort (or
indeed, some later effort to implement a MERGE that is comparable to
the implementations of other database systems).

> We will one day have MERGE according to the SQL Standard.

Agreed.

> My opinion is that syntax for this should be similar to MERGE in the
> *body* of the command, rather than some completely different syntax.
> e.g.
>
>> WHEN NOT MATCHED THEN
>>   INSERT
>> WHEN MATCHED THEN
>>  UPDATE
>
> I'm happy that we put that to a vote on what the syntax should be, as
> long as we bear in mind that we will one day have MERGE as well.

While I am also happy with taking a vote, if we do so I vote against
even this much less MERGE-like syntax. It's verbose, and makes much
less sense when the mechanism is driven by would-be duplicate key
violations rather than an outer join. I also like that when you UPSERT
with the proposed ON CONFLICT UPDATE syntax, you get all the
flexibility of an INSERT - you can use data-modifying CTEs, and nest
the statement in a data-modifying CTE, and "INSERT ... SELECT... ON
CONFLICT UPDATE ..." . And to be honest, it's much simpler to
implement this whole feature as an adjunct to how INSERT statements
are currently processed (during parse analysis, planning and
execution); I don't want to make the syntax work against that. For
example, consider how little I had to change the grammar to make all
of this work:

$ git diff master --stat | grep gram
 src/backend/parser/gram.y                          |  72 ++-

The code footprint of this patch is relatively small, and I think we
can all agree that that's a good thing.

-- 
Peter Geoghegan