推荐阅读
【PostgreSQL中Upsert如何区分执行的是Insert还是Update?】
【PostgreSQL中Upsert实现最小化更新】
【PostgreSQL中实现Update前的备份骚操作】
众所周知，PostgreSQL提供了Upsert的功能（具体是9.5版本之后提供的），这里的upsert并不是通过UPSERT关键字来实现有则更新，无则插入，而是通过INSERT ... ON CONFLICT DO UPDATE ...的方式实现，具体的功能说明请参考官方文档：【传送门】
这不是我们今天讨论的重点，今天所讨论的是PGSQL中批量插入的时候的UPSERT用法。
首先我们假设有一个表aa，分别三个字段：id、a、b
PGSQL批量插入
以下是两种常见的基本插入操作：
-- 插入单条记录的语句可以如下：
INSERT INTO aa VALUES (1,2,3);
-- 插入单条语句（指定某几个字段）可以如下：
INSERT INTO aa (id,a,b) VALUES (1,2,3);
那么如果我们需要使用到批量插入呢：
-- 批量插入多条记录
INSERT INTO aa VALUES (1,2,3),(2,3,4),(3,4,5);
当需要使用UPSERT时候，可以如下操作：
-- 插入或更新id为1的记录
INSERT INTO aa VALUES (1,2,3) ON CONFLICT (id) DO UPDATE SET a=0,b=0;
那么问题来了，当需要用到批量UPSERT的时候该怎么做呢？如果按照下面的做法：
-- 批量操作更新或插入？
INSERT INTO aa VALUES (1,2,3),(2,3,4),(3,4,5) ON CONFLICT DO UPDATE SET a=0,b=0;
这样会造成所有已经存在的行的a和b字段都会被更新为0，这显然与我们的目的不符。那么该怎么做呢？
这里需要用到PG中提供的一个特性：关键字EXCLUDED！在PG中提供了一个特殊的表EXCLUDED，用来引用原来要插入的值。（TIPS：如果你要往一个本身就叫做EXCLUDED的表中插入数据，这里记得给那个表起个别名来避免由于关键字冲突导致的不必要错误）
-- 批量插入或更新的正确操作：
INSERT INTO aa VALUES (1,2,3),(2,3,4),(3,4,5) ON CONFLICT DO UPDATE SET a=EXCLUDED.a,b=EXCLUDED.b;
若执行前的表中数据如下：
| id | a | b |
|---|---|---|
| 1 | 0 | 0 |
| 2 | 1 | 1 |
则执行上述语句后表中数据如下：
| id | a | b |
|---|---|---|
| 1 | 2 | 3 |
| 2 | 3 | 4 |
| 3 | 4 | 5 |
这样一来，便实现了批量UPSERT的目的。当然，DO UPDATE ... WHERE xxx也是可以的，这样就可以限制更新条件了。
至于更多的UPSERT相关内容，请自行查阅资料哦。
补充
今天在开发中突然发现，通过serial或者sequence创建的自增ID列，在执行Upsert的时候也会增加，即使因为冲突导致执行了UPDATE操作，但是SEQUENCE的值也会增加。具体如何解决没有探究。
网友评论