PG Merge功能和语法解读本文借助讨论PG15的新的Merge功能，回顾和分析了业务开发者中常见的“插入或更新”的数

场景和需求

在业务应用开发中，经常会遇到一类数据操作的需求，就是“插入或者更新(Insert or Update)”。就是需要在数据库中插入一条记录，但如果这条记录存在，就改为按照新的数据更新这条记录的一些字段内容，有时候也被称为upsert。

这个需求看起来比较简单，但其实有很多细节问题，如：

记录存在的标准是什么，简单的可能就是需要主键，复杂点的条件查询组合可以吗？(Oracle的Merge就有记录集稳定的问题)
更新的时候，是不是够灵活，比如可以引用原来的值
是否能够支持增、改、删多种操作
除了记录标识之外，能否再加入其他条件来控制记录的修改
是否能够很好的支持关联查询记录集，而不仅仅是简单的数值行
是否能返回每条修改记录的状态(是插入的还是更新的)
操作的性能，关联查询的稳定性等等

在早期数据库系统的理论和功能不那么完善，或者开发者不熟悉和了解数据库系统的相关能力的时候，这个需求通常在应用程序层面实现。就是程序先查询记录的存在性，如果存在，则构造并且执行更新语句；如果不存在则执行插入语句。这样的做法，在业务满足的层面是没有问题的。但显然这样的操作比较低效，因为要执行多个语句，并且对于大批量的数据操作，遍历执行的效率更低，也不符合数据库系统批量处理的设计理念。

因此，后续的现代化数据库系统，其实都在某个层面上实现了这个功能。比如Oracle的Merge，还有PG的Insert on conflict，MySQL有insert ignore和on duplicate key等等。我们今天讨论的主题主要是在PG中进行实现的两个主要技术方案。

Insert On Conflict(IOC)

我们先来复习一下PG的IOC。这个功能起码在11版本就提供了。当时的开发者可能认为不需要专门设计一个upsert语句，只需要将insert语句扩展一下就可以了。

它的语法是：

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name

and conflict_action is one of:

DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
                ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                ( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ WHERE condition ]

我们来依次分析一下这个语句：

插入数据，可以是一个简单的值集合，也可以基于一个可选的查询结果集，就是可选的CTE(with语句)
插入语句还是和原定义相同，但可选一个on conflict子句，作为插入更新的操作接口
on conflict的意思是，遇到冲突，则执行...

我们只看on conflict子句

子句包括冲突条件(conflict target)和操作行为(conflict action)
冲突条件可以包括(括号括起来的)索引字段名词，索引表达式等，还可以使用where来过滤记录
冲突条件还可以是约束名称，使用on constraint，不需要括号
操作行为可以包括： do nothing，忽略，不做任何操作
操作行为可以包括： do update，更新数据，但nothing和update只能选择其一
更新数据的子句定义和普通更新一样
可以使用新记录的逻辑数据表excluded
更新操作可以使用where来增加条件

一般技术化的语法定义还是比较抽象，下面是一些示例代码，可以帮助我们理解这些用法：

-- Don't update existing distributors based in a certain ZIP code
INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')
ON CONFLICT (did) DO UPDATE
SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'
WHERE d.zipcode <> '21201';

-- This statement could infer a partial unique index on "did"
-- with a predicate of "WHERE is_active", but it could also
-- just use a regular unique constraint on "did"
INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
ON CONFLICT (did) WHERE is_active DO NOTHING;

这里有几个要点：

do nothing和do update只能选择其中之一
要使用新记录中的字段值，可以使用excluded逻辑记录集
冲突检测，必须是可以作为唯一的行区别方式，如主键、约束、唯一索引等等

Merge On PG

在一般情况下，IOC已经能够满足最基础的需求了。但从其定义和使用我们也可以看到，它还是有一些不足。比如只能处理冲突或者不冲突的简单条件，不能条件删除等等。熟悉Oracle的开发者都知道，Oracle提供了Merge语句是实现upsert的主要方式，但能够提供更强的控制能力。

可能是这些原因，为了使这个操作更加完善，在postgresql的15版本，他们也实现了Merge(合并)语句。相关的概念、结构、语法和应用方式，都比较接近于oracle的merge了。按照PostgreSQL的技术文档，merge语句的语法定义是：

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

where data_source is:

{ [ ONLY ] source_table_name [ * ] | ( source_query ) } [ [ AS ] source_alias ]

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 } }

and merge_insert is:

INSERT [( column_name [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

and merge_update is:

UPDATE SET { column_name = { expression | DEFAULT } |
             ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]

and merge_delete is:

DELETE


// sample code
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 ... using
其他要素包括数据源、when子句、insert子句、update子句、delete子句等等
其匹配依据，主要来源于关联查询(而非冲突定义)
可以进行多次匹配和数据修改执行操作(使用When Then)
可以执行删除操作
由于使用关联记录，更新时无需使用excluded虚记录集

笔者的初步感觉，这个merge的实现还是挺不错的，按照笔者在文章开始提出的那么多需求细节，其满足的程度是比较高的。其实现的功能和细节，已经完全超出了初步的“插入或更新”的初级需求，而已经演进称为新的构造记录集的通用操作模式了。

但现在由于版本和应用开发条件限制的问题，笔者，还没有机会在实际的应用场景中充分实践和测试，特别是对于比较大或者复杂的数据场景，不知道它的性能、稳定性、鲁棒性表现如何。当然，笔者原意在有可能的情况下，优先考虑应用和检验这个技术，也希望有实际使用过的读者，能够反馈更多的情况。

我们也可以看到，IOC和Merge也不是简单的替换关系，而是在都可以满足基础业务需求的情况下，各有比较适合的应用场景，需要开发者根据实际情况灵活选择使用。IOC需要唯一索引，可以处理比较简单的情况；Merge则基于关联条件，并且可以构造更复杂的条件化的数据操作包括删除等等，当然对开发者的技术水平要求更高。

小结

本文借助讨论PG15的新的Merge功能，回顾和分析了业务开发者中常见的“插入或更新”的数据操作的实现方式，并分析了各种方案的要素和特定，希望能够帮助完善开发者对于相关问题的理解和认知。