使用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表中。

假设Microsoft将email字段的值从contact@microsoft.com更改为hotline@microft.com ,我们可以使用UPDATE语句对其进行更新。但是,为了演示upsert功能,我们使用以下INSERT ON CONFLICT语句:

该声明指出,如果name字段的值已经存在于customers表中,则忽略它(不执行任何操作)。
以下语句等效于上述语句,但它使用name列而不是唯一约束名作为INSERT语句的target。

假设您希望在插入已存在的name值时将新email值与旧email值连接起来,在这种情况下,使用UPDATE子句作为INSERT语句的操作,如下所示:

以上便是如何使用INSERT ON CONFLICT语句使用PostgreSQL upsert功能减少DB交互的一个小技巧。

注:对于有自增主键(nextval())的表,使用UPSERT方法后,无论走的是INSERT还是UPDATE还是DO NOTHING逻辑,nextval()都会+1,会导致id不连续。因为nextval()在ON CONFLICT检测前就已经触发了.(感谢chenyuanfei同学指出遗漏)

From "9.16. Sequence Manipulation Functions":

nextval

(...)

Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Such cases will leave unused "holes" in the sequence of assigned values. Thus, PostgreSQL sequence objects cannot be used to obtain "gapless" sequences.