Re: [HACKERS] MERGE SQL Statement for PG11

On 29 January 2018 at 15:44, Bruce Momjian <bruce@momjian.us> wrote:
> On Mon, Jan 29, 2018 at 03:12:23PM +0000, Simon Riggs wrote:
>> On 29 January 2018 at 14:55, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>>
>> > My note was not against MERGE or INSERT ON CONFLICT. If I understand to this
>> > topic, I agree so these commands should be implemented separately. But if we
>> > use two commands with some intersection, there can be nice to have
>> > documentation about recommended use cases. Probably it will be very often
>> > question.
>>
>> That is more qualitative assessment of each, which I think I will defer on.
>>
>> This patch is about implementing the SQL Standard compliant MERGE
>> command which is widely used in other databases and by various tools.
>
> Uh, if we know we are going to get question on this, the patch had
> better have an explanation of when to use it.  Pushing the problem to
> later doesn't seem helpful.

What problem are you referring to? MERGE is not being implemented as
some kind of rival to existing functionality, it does things we cannot
yet do.

Info below is for interest only, it is unrelated to this patch:

INSERT ON CONFLICT UPDATE does only INSERT and UPDATE and has various
restrictions. It violates MVCC when it needed to allow it to succeed
more frequently in updating a concurrently inserted row. It is not SQL
Standard.

MERGE allows you to make INSERTs, UPDATEs and DELETEs against a single
target table using complex conditionals. It follows the SQLStandard;
many developers from other databases, much existing code and many
tools know it.
e.g.

MERGE INTO target t
USING source s
ON t.tid = s.sid
WHEN MATCHED AND balance > delta THEN
  UPDATE SET balance = balance - delta
WHEN MATCHED
 DELETE;
WHEN NOT MATCHED THEN
  INSERT (balance, tid) VALUES (balance + delta, sid)

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