likes
comments
collection
share

PostgreSQL技术问答03 - Upsert

作者站长头像
站长
· 阅读数 53

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

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

本文的主要内容是关于Upsert操作模式和实现相关的问题。

什么是Upsert

在Web应用开发过程中，经常有这样一种数据处理的需求模式。就是需要更新数据，但有可能这个数据现在并不存在，就需要先创建这个记录。就是所谓的 Update Or(If Not Exist) Insert 模式。传统标准的SQL只有Update和Insert模式，要实现这样一个业务需求，就需要先做一个存在性判断，然后来决定使用Update或者Insert。

在程序实现中，就需要先用Select语句进行一个查询，如果有匹配的记录，则执行更新操作，否则执行插入操作。这并不是一个理想的模式，因为它需要执行多个操作，并且需要在客户端和数据库服务器之间反复进行请求响应和传输数据。而且对于多个数据，无法进行批量操作，需要分拆为单个的记录进行处理。

为此，增强的SQL语句和数据库系统实现，就提出了可以使用单一SQL语句，能够执行Upsert操作的方式。

Postgres如何实现Upsert

实际上，Postgres中，并没有一个SQL语句叫做Upsert。它实际上是通过扩展insert语句，即insert ... on conflict(IOC) 这种模式实现的。

我们先看一个实际的SQL示例，然后再分析这其中的要点：

// 示例数据表：

CREATE TABLE udata (
  id SERIAL PRIMARY KEY,
  ivalue int default 0
);

// 插入数据
insert into udata (id,ivalue) values(10,20);

// 再次插入相同的数据， 报错
insert into udata (id,ivalue) values(10,20);
ERROR:  duplicate key value violates unique constraint "udata_pkey"

// 修改语句后再次插入数据
insert into udata (id,ivalue) values(10,20) 
on conflict(id) do update set ivalue = excluded.ivalue ;
INSERT 0 1

// 改进后的insert语句，返回记录
insert into udata (id,ivalue) values(10,20) on conflict(id) do update set ivalue = udata.ivalue + excluded.ivalue returning * ;

 id | ivalue 
----+--------
 10 |     40
(1 row)

查看PostgreSQL的技术文档，有关于insert语句中，可选on conflict子句的标准形式是：

[ ON CONFLICT [ conflict_target ] conflict_action ]

结合以上示例，这个操作的应用要点是：

  • ON CONFLICT 需要和insert语句结合使用，是其可选子句
  • ON CONFLICT 也有两个可选部分，分别为目标和行为
  • 冲突目标是指可以构成冲突的检测条件，在本例子中，id作为主键，就是一个冲突目标，需要设置在子句参数中
  • 冲突目标可以如示例般使用主键，也可以使用唯一索引和复合唯一索引
  • 冲突行为，是指检测到冲突的时候，进行的操作，本例中是执行一个Update操作，格式是do update set ....，和标准update语句类似
  • 这里要特别注意和update有一个很大的差异，就是如果要引用一个插入数据逻辑记录集excluded，也可以理解成为它会应用一个逻辑数据表，名称为excluded，字段名称就是插入时的目标名称
  • 这时Insert的结果是： insert 0 1，这里和标准insert相同有两个信息，第一个信息是oid，insert语句总是为0；第二个是插入操作影响的记录数量，这里有1条
  • 冲突行为可选不进行任何操作，即 do nothing，看起来有点多余，但和无on conflict子句的差异在于这个执行，在冲突的时候，不会抛出SQL执行错误，相当于内部处理了

写到这里笔者觉得这个思维方式也是比较奇怪和有意思的。如果按照一个更直观正常的逻辑，难道不是应该是“ Update on noexist insert ” 这种实现方案吗？但实际上，我们确实也是应该按照“先有数据，然后才有更新”这个次序进行思维，另外，插入数据应该是完整的信息，但更新可能只会更新部分字段，所以这个处理也是比较合理的。

什么是EXCLUDED

按照PG技术文档的说法，EXCLUDED是在insert on conflict操作执行时，使用的一个临时的特殊表，从这个表中可以引用要插入的数值，字段名称就是要插入的目标字段。所以使用这个表，需要插入目标表和字段的访问权限。引用方式就是EXCLUDED.filedName (在PG中不区分大小写)。

笔者尚未发现在PostgreSQl中，EXCLUDED这个特殊逻辑表应用在其他场合的情况。顺便提一句，类似的用法，在Trigger中，使用OLD和NEW作为逻辑记录集的名称。所以EXCLUDED在PG SQL中，是一个关键字，在进行相关编程时要谨慎使用。

PostgreSQL支持类似于Oracle中的Merge语句吗

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

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

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

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实现的要点包括：

  • 基本语法是 Merge Into (目标数据表) Using (关联表) On (关联条件) (匹配操作语句) (不匹配操作语句)
  • 需要使用一个关联参考数据集Using，并设置相关的关联条件
  • 由于关联的问题，需要数据集关联的唯一性(和Oracle的实现类似)
  • 对于每个关联记录进行匹配处理
  • 如果能够匹配，则可以选择更新记录或者删除记录
  • 如果不能匹配，可以选择插入记录
  • 当然匹配处理，都是可选的

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

有其他的解决方案吗

当然。传统的解决方案就是使用程序客户端进行查询-判断存在性-更新或者删除。显然，这需要一个外部应用来访问数据库系统，获取数据进行操作，而且需要进行多次执行。

还可以把这个过程，使用存储过程实现，并部署在数据库服务器里面，这样从客户端只需要调用这个存储过程就可以了，性能也比较好。但这个方案比较麻烦，而且不够灵活，调整起来比较麻烦。

笔者构想，在PG中，使用CTE也许可以辅助实现这个需求。就是先用数据来构造一个CTE。然后和原始数据表进行关联查询，过滤出两种情况，需要更新的数据，用一个更新CTE来处理；需要插入的数据，直接插入数据库。虽然在实际开发中并不建议这么做，但作为一个思想实验，笔者还是编写了一个示例代码，来展示这个操作确实是可行的：

// udata表结构是 id(int), ivalue(int)
// 插入两条数据是 (1, 1) 和 (2, 2)
with 
D (id1, ivalue1) as (values
(1, 1),
(2, 2)
),
U as ( update udata set ivalue = ivalue + ivalue1 from D where id1 = id returning id ),
I as ( insert into udata(id,ivalue) select id1, ivalue1 from D left join udata O on id1 = id where id is null returning id  )
select id, 'I' from I
union all 
select id, 'U' from U;

// 检查结果
select * from udata;

小结

本文讨论了在PostgreSQL如何实现“更新数据，如果不存在就插入”这样一种常见的数据处理的业务需求。讨论了几种可行的技术方案，具体的实现和操作，以及方案之间的优缺点和差异。

转载自:https://juejin.cn/post/7368761530382876691
评论
请登录