On Wed, Oct 8, 2014 at 6:25 AM, Simon Riggs <si...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
