使用upsert语句减少应用与数据库交互

在开发过程中,经常会遇到一种场景:先判断表中是否存在某一行数据,如果存在,更新它,如果不存在,插入它。这种场景的代码写起来比较繁琐,还会增加一次数据库的交互。使用upsert语句能极大的简化这种操作
,本文将介绍如何使用PostgreSQL upsert功能插入或更新数据。

upsert简介

在关系数据库中,术语upsert称为合并。 这个想法是当你在表中插入一个新行时,如果它已经存在PostgreSQL会更新该行,否则,PostgreSQL会插入新行。 这就是为什么称这个动作是upsert(更新或插入)。

要在PostgreSQL中使用upsert功能,请使用INSERT ON CONFLICT语句,如下所示:

PostgreSQL将ON CONFLICT target action子句添加到INSERT语句以支持upsert功能

target可以是:

  • (column_name):列名。
  • ON CONSTRAINT constraint_name:其中约束名称可以是UNIQUE约束的名称。
  • WHERE谓语:带WHERE谓语的WHERE子句

action可以是:

  • DO NOTHING:如果表中已存在行,则表示不执行任何操作。
  • DO UPDATE SET column_1 = value_1, .. WHERE condition:更新表中的一些字段。

请注意, ON CONFLICT子句仅适用于PostgreSQL 9.5以后的版本,目前我厂的数据库都已经升级到10,所以可以放心使用了。 如果您使用的是早期版本,则需要通过抛出和捕获INSERT异常的解决方法来获得upsert功能,这种方法在我们目前线上的函数中有很多处,如果有研发同学感兴趣的可以去代码仓库搜索“upsert”关键字。

如果您还使用过MySQL,会发现upsert功能类似于MySQL中的重复键更新语句中的插入 。

upsert例子

创建一个新表 customers 来演示PostgresQL upsert功能。

customers表由四列组成: customer_id,name,email和active 。 name列具有关联的唯一约束,以保证唯一性。

以下INSERT语句将一些行插入到customers表中。