On 29 January 2018 at 15:44, Bruce Momjian <br...@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.steh...@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
