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

On 8 October 2014 01:47, Peter Geoghegan <pg@heroku.com> wrote:

> It seems like what you're talking about here is just changing the
> spelling of what I already have. I think that would be confusing to
> users when the time comes to actually implement a fully-generalized
> MERGE, even with the clearly distinct MERGE CONCURRENTLY variant
> outlined here (which, of course, lacks an outer join, unlike MERGE
> proper).

I change my view on this, after some more thought. (Hope that helps)

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.

> However, unlike the idea of trying to square the circle of producing a
> general purpose MERGE command that also supports the UPSERT use-case,
> my objection to this much more limited proposal is made purely on
> aesthetic grounds. I think that it is not very user-friendly; I do not
> think that it's a total disaster, which is what trying to solve both
> problems at once (MERGE bulkloading and UPSERTing) would result in. So
> FWIW, if the community is really set on something that includes the
> keyword MERGE, which is really all you outline here, then I can live
> with that.

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

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.

-- Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services