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表关闭会导致并发性能较差和空间膨胀的问题。
• 只有拥有表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

  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 --------------------------------------------- Streaming (type: GATHER) Node/s: All datanodes -> Insert on t1 Conflict Resolution: UPDATE Conflict Arbiter Indexes: t1_pkey -> Streaming(type: REDISTRIBUTE) Spawn on: datanode2 -> Values Scan on "*VALUES*" (8 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 ------------------------------------- Insert on t1 Conflict Resolution: UPDATE Conflict Arbiter Indexes: t1_pkey Node/s: All datanodes Node expr: id -> Values Scan on "*VALUES*" (6 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_t1。

1
2
3
4
5
6

CREATE TABLE reason_t1
(
  r_reason_sk    integer primary key,
  r_reason_id    character(16),
  r_reason_desc  character(100)
);

向表中插入两条记录。

1

INSERT INTO reason_t1 VALUES (2, 'AAAAAAAABAAAAAAA', 'reason2'),(3, 'AAAAAAAACAAAAAAA','reason3');

向表中插入两条记录，一条有冲突，一条无冲突。有冲突的记录进行更新，无冲突的记录进行插入。

1
2
3
4
5
6
7
8
9

INSERT INTO reason_t1 VALUES (2, 'BBBBBBBBBBBBB','reason2_new'),(4, 'AAAAAAAADAAAAAAA', 'reason4')
ON CONFLICT(r_reason_sk) DO UPDATE SET r_reason_id = EXCLUDED.r_reason_id, r_reason_desc = EXCLUDED.r_reason_desc;
SELECT * FROM reason_t1 ORDER BY 1;
 r_reason_sk |   r_reason_id    |                                            r_reason_desc
-------------+------------------+------------------------------------------------------------------------------------------------------
           2 | BBBBBBBBBBBBB    | reason2_new
           3 | AAAAAAAACAAAAAAA | reason3
           4 | AAAAAAAADAAAAAAA | reason4
(3 rows)

向表中插入两条记录，一条有冲突，一条无冲突。有冲突的记录进行忽略，无冲突的记录进行插入。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

INSERT INTO reason_t1 VALUES (2, 'CCCCCCCCCCCC','reason2_new2'),(5, 'AAAAAAAADAAAAAAA', 'reason5') 
ON CONFLICT(r_reason_sk) DO NOTHING;
INSERT 0 1
SELECT * FROM reason_t1 ORDER BY 1;
 r_reason_sk |   r_reason_id    |                                            r_reason_desc
-------------+------------------+------------------------------------------------------------------------------------------------------
           2 | BBBBBBBBBBBBB    | reason2_new
           3 | AAAAAAAACAAAAAAA | reason3
           4 | AAAAAAAADAAAAAAA | reason4
           5 | AAAAAAAADAAAAAAA | reason5
(4 rows)

删除表reason_t1。

1

DROP TABLE reason_t1;