PostgreSQL技术问答19 - Merge

本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文笔者想要来讨论一下在PostgreSQL中的Merge语句。

原本这一部分，是作为笔者系列文章中的Upsert主题中的一部分规划的，但在成文的过程中，笔者发现这一部分的内容还是有一些值得突出探讨的地方，所以独立成文进行说明。

什么是Merge语句

关于Merge语句，在PG官方技术文档中，是如此定义的：

MERGE — conditionally insert, update, or delete rows of a table

MERGE - 有条件的对表中的记录进行插入、修改和删除

这个定义，其实已经指出了Merge语句的一些特性。就是基于一个已经存在的记录集（表或者查询结果），并将其作为一个参照条件，对已有表中的数据，进行相关的修改，修改操作可以包括插入、修改和删除。就是说，数据的修改基于某种查询结果和条件，而非像以前的增删改操作必须事先确定操作的类型，这样可以在批量操作时大大简化相关代码的编写和实现。

所以，笔者理解，Merge本质上是一种符合操作语句，它先进行一个查询，然后根据查询的条件，来决定后续的修改操作，从而实现了一个简单的抽象化通用流程。以往的实现方式，需要通过编程和流程控制来实现，现在将其语义化和标准化了，并精简到单独的SQL语句中，同时也能够体现SQL语句指令化编程和数据集操作的精髓。

鉴于这个操作的实用性和需求的广泛性，在SQL:2003标准中引入了Merge作为SQL的标准语句，并在后续的版本中进行了扩展和改进。从现在的状况来看，Oracle对于Merge的实现和应用的时间是最长最成熟的，其他的数据库系统在这方面特别是成熟度方面都有所不足，有一个发展的过程，我们在应用的过程中，需要了解和理解这个状况。

PostgreSQL支持Merge语句吗

简单而言，就是Postgres以前不支持，但现在(确切的说是15版本)之后支持了。

Upsert的基础逻辑和需求是简单而明确的，但由于早期没有标准的SQL实现，各个数据库系统就进行了各种自己的实现，如前面PostgreSQl的实现方式是Insert On Conflict，而Oracle的原生实现则使用了自己设计的Merge Into语句。实际上Merge Into和IOC在实际操作上，还是有一些细微的逻辑上的差别，所以在PostgreSQL的15版本之后，PG也提高并实现了Mergt Into语句，并且实现了和Oracle相似的用法和逻辑，并满足了SQL:2003标准中的相关要求。

Postgres官方技术文档中，关于merge有专门的章节，地址如下：

www.postgresql.org/docs/curren…

文中，Merge的基本语法如下：

[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]

and when_clause is:

{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING } }

可以看到，Merge由以下几个部分和要点构成：

• Merge Into 关键字，表面当前需要进行Merge操作
• targle table，目标表，需要进行数据操作的表
• Using 数据源，数据源可以是表或者查询(CTE)
• 目标表和数据源的关联关系
• 条件匹配操作子句，当关联匹配或不匹配时，需要进行的操作
• 匹配时，可以进行更新和删除操作，当然也可以不做操作
• 匹配操作，可选匹配时的附加条件(And Condition)
• 不匹配时，可选插入操作，也可以不做任何操作
• 不匹配操作同样可以定义附加条件
• 增改操作的语法同标准正常操作语法
• 删除操作就是简单的 Delete，因为匹配条件已经确定要删除的记录

举例说明Merge的使用

我们从一个官方示例来展开分析：

MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);
  
MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
  DELETE;
  

这个示例中，我们可以看到Merge Into实现的要点包括：

• 基本语法是 Merge Into (目标数据表) Using (关联表) On (关联条件) (匹配操作语句) (不匹配操作语句)
• 需要使用一个关联参考数据集Using，并设置相关的关联条件，这个非常重要，特别是将作为数据更新和删除的依据
• 由于关联的问题，需要数据集关联的唯一性(和Oracle的实现类似)
• 对于每个关联记录进行匹配处理
• 如果能够匹配，则可以选择更新记录或者删除记录
• 如果不能匹配，可以选择插入记录
• 当然匹配处理，都是可选的
• 可以多次组合匹配条件，并遵循先后的逻辑顺序，有点像多次if判断

所以，Merge的逻辑就是从已有的记录集角度出发来构思的了。虽然和insert on conflict都能达到类似的目标，但出发点稍有不同，所以Merge Into在使用上，有一个逻辑的限制，就是using关联的记录集是需要有唯一标识的。

有什么限制和需要注意的问题

根据官方技术文档，在使用merge时，可能需要注意以下问题：

• Postgres是从版本15之后，才提供的正式的实现和支持，现在可能还在发展和优化的阶段，可能需要谨慎使用，实际应用前，多做测试和验证
• merge的返回值，是修改的数据记录数量
• 所以，merge的数据操作语句，不能使用returing的形式
• merge没有独立的权限设置，收到其具体操作(插入、修改、删除)的权限设置的限制，同时还需要select权限
• merge的目标数据表，不能为实体化视图或者外部表
• merge目标表不能定义规则
• 虽然是批量处理，但其实merge操作，是基于记录的，需要保证查询和关联记录集，对于更新条件的唯一性，从而不会造成逻辑上的冲突
• 在某些情况下，大规模的数据关联更新操作的效率和性能比较低，可能需要注意进行简化或者优化，如分批，分条件操作等
• 如果目标表使用了Trigger，要注意其操作和触发器之间的关系，是有相关的规则的，如对于语句触发器，Merge会触发所有类型，而非仅限于指定类型的触发器，这个规则在文档中有专门的章节，使用是需要明确和注意
• 不同的数据库系统和版本，merge的实现方式和细节可能会有差异，需要注意兼容的问题

小结

本文探讨了PG中Merge SQL语句的相关问题，包括Merge的基本概念，相关SQL标准，和Upsert的联系和区别，实际的实现代码和示例，需要注意的问题等等。