UPSERT

功能描述

向表中添加一行或多行数据。当出现主键或者唯一约束冲突时更新或者忽略冲突的数据。

UPSERT语法仅8.1.1及以上版本支持。

语法格式

详细介绍请参见INSERT的语法格式。有两种UPSERT语法格式：

INSERT INTO ON DUPLICATE KEY UPDATE

INSERT IGNORE
INSERT INTO ON CONFLICT DO NOTHING

INSERT INTO ON CONFLICT(...) DO UPDATE SET
INSERT INTO ON CONFLICT ON CONSTRAINT con_name DO UPDATE SET

INSERT INTO ON CONFLICT(...) DO NOTHING
INSERT INTO ON CONFLICT ON CONSTRAINT con_name DO NOTHING

注意事项

• 当在列存表上执行UPSERT时，建议开启DELTA表。开启DELTA表能够有效防止执行UPSERT时产生小CU(大量的小CU会导致空间膨胀和查询性能差)。
• 对于列存表上的UPSERT、UPDATE、DELETE并发场景，由于并发更新到同一个CU时需要等待CU锁，无法支持这几个操作的并发执行，开启DELTA也无法解决该问题。
• 只有拥有表INSERT、UPDATE权限的用户，才可以通过UPSERT语句向表中插入或更新数据。
• 目标表上必须包含主键或者唯一索引才可以执行UPSERT的冲突更新语句。
• 所有的唯一索引都不可用时不能执行UPSERT的冲突更新语句，重建索引后可以正常执行。
• 可能存在分布式死锁导致查询hang问题。
  

  例如场景：一个事务中或者通过JDBC(setAutoCommit(false))批量执行多条UPSERT语句，多个类似任务同时执行。

  可能产生结果：由于不同线程在不同节点更新顺序可能不同，在存在并发更新同一行的场景里可能会有死锁问题。

  解决办法：

  1. 减小GUC参数lockwait_timeout 值(默认值为20min)。分布式死锁会等待lockwait_timeout 时间然后报错。通过减小此参数的数值，降低死锁造成的业务等待时间。
  2. 保证主键相同的数据从只从一个数据库连接导入数据库。可以并发执行UPSERT语句。
  3. 每个事务中只执行一条UPSERT语句。可以并发执行UPSERT语句。
  4. 单线程执行多条UPSERT语句。不能并发执行UPSERT语句。

  如上解决办法中，方法1只能降低等待时间，无法解决死锁问题。在业务中有UPSERT语句时，仍建议减小此参数数值；方法2、3、4均可以解决死锁问题，但建议采用方法2，其性能优于其他两个方法。

• 不能更新分布列。（例外：当分布键与更新值相同时）。

  1 2 3

  CREATE TABLE t1(dist_key int PRIMARY KEY, a int, b int); INSERT INTO t1 VALUES(1,2,3) ON CONFLICT(dist_key) DO UPDATE SET dist_key = EXCLUDED.dist_key, a = EXCLUDED.a + 1; INSERT INTO t1 VALUES(1,2,3) ON CONFLICT(dist_key) DO UPDATE SET dist_key = dist_key, a = EXCLUDED.a + 1;

• 不支持对包含触发器(触发事件为INSERT或UPDATE)的目标表执行UPSERT语句。
• 不支持对可更新视图执行UPSERT语句。
• UPDATE子句、UPDATE中WHERE子句或者索引条件表达式不能包含不下推函数。
• 不支持延迟唯一索引。
• 列存复制表不支持通过UPSERT语句执行冲突更新操作。
• 通过INSERT INTO SELECT语句执行UPSERT的更新操作时，需要注意SELECT语句的查询结果顺序。在分布式环境中未使用ORDER BY语句时每次执行相同的SELECT语句返回结果顺序可能不一样，这会导致UPSERT语句的执行结果不符合预期。
• 不支持多次更新。插入多组数据间如果有冲突，报错。（例外：当查询计划是PGXC计划时）。

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

  CREATE TABLE t1(id int PRIMARY KEY, a int, b int); -- STREAM计划 EXPLAIN (COSTS OFF) INSERT INTO t1 VALUES(1,2,3),(1,5,6) ON CONFLICT(id) DO UPDATE SET a = EXCLUDED.a + 1; QUERY PLAN ------------------------------------------------ id | operation ----+----------------------------------------- 1 | -> Streaming (type: GATHER) 2 | -> Insert on t1 3 | -> Streaming(type: REDISTRIBUTE) 4 | -> Values Scan on "*VALUES*" Predicate Information (identified by plan id) --------------------------------------------- 2 --Insert on t1 Conflict Resolution: UPDATE Conflict Arbiter Indexes: t1_pkey ====== Query Summary ===== ------------------------------ System available mem: 819200KB Query Max mem: 819200KB Query estimated mem: 3104KB (18 rows) INSERT INTO t1 VALUES(1,2,3),(1,5,6) ON CONFLICT(id) DO UPDATE SET a = EXCLUDED.a + 1; ERROR: INSERT ON CONFLICT DO UPDATE command cannot affect row a second time HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. -- 关闭stream，生成PGXC计划 set enable_stream_operator = off; EXPLAIN (COSTS OFF) INSERT INTO t1 VALUES(1,2,3),(1,5,6) ON CONFLICT(id) DO UPDATE SET a = EXCLUDED.a + 1; QUERY PLAN ----------------------------------------------- id | operation ----+---------------------------------- 1 | -> Insert on t1 2 | -> Values Scan on "*VALUES*" Predicate Information (identified by plan id) --------------------------------------------- 1 --Insert on t1 Conflict Resolution: UPDATE Conflict Arbiter Indexes: t1_pkey Node expr: id (11 rows) INSERT INTO t1 VALUES(1,2,3),(1,5,6) ON CONFLICT(id) DO UPDATE SET a = EXCLUDED.a + 1; INSERT 0 2

示例

创建表reason_t2，并向表中插入数据。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE reason_t2
(
  a    int primary key,
  b    int,
  c    int
);

INSERT INTO reason_t2 VALUES (1, 2, 3);
SELECT * FROM reason_t2 ORDER BY 1;
 a | b | c
---+---+---
 1 | 2 | 3
 (1 rows)

向表reason_t2中插入两条数据，一条有冲突，一条无冲突。有冲突的数据进行忽略，无冲突的数据进行插入。

1
2
3
4
5
6
7

INSERT INTO reason_t2 VALUES (1, 4, 5),(2, 6, 7) ON CONFLICT(a) DO NOTHING;
SELECT * FROM reason_t2 ORDER BY 1;
 a | b | c
---+---+----
 1 | 2 | 3
 2 | 6 | 7
(2 rows)

向表reason_t2中插入数据，一条有冲突，一条无冲突。有冲突的数据进行更新，无冲突的数据进行插入。

1
2
3
4
5
6
7
8

INSERT INTO reason_t2 VALUES (1, 4, 5),(3, 8, 9) ON CONFLICT(a) DO UPDATE SET b = EXCLUDED.b, c = EXCLUDED.c;
SELECT * FROM reason_t2 ORDER BY 1;
 a | b | c
---+---+----
 1 | 4 | 5
 2 | 6 | 7
 3 | 8 | 9
 (3 rows)

根据过滤条件筛选被更新的行。

1
2
3
4
5
6
7
8

INSERT INTO reason_t2 VALUES (2, 7, 8) ON CONFLICT (a) DO UPDATE SET b = excluded.b, c = excluded.c  WHERE reason_t2.c = 7;
SELECT * FROM reason_t2 ORDER BY 1;
 a | b | c
---+---+---
 1 | 4 | 5
 2 | 7 | 8
 3 | 8 | 9
(3 rows)

向表reason_t中插入数据，有冲突的数据进行更新并调整更新映射关系，即c列更新到b，b列更新到c。

1
2
3
4
5
6
7
8

INSERT INTO reason_t2 VALUES (1, 2, 3) ON CONFLICT (a) DO UPDATE SET b = excluded.c, c = excluded.b;
SELECT * FROM reason_t2 ORDER BY 1;
 a | b | c
---+---+---
 1 | 3 | 2
 2 | 7 | 8
 3 | 8 | 9
(3 rows)