# UPSERT #### 功能描述 向表中添加一行或多行数据。当出现主键或者唯一约束冲突时，它可以选择更新冲突的数据，或者忽略这些冲突。 ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/notice_3.0-zh-cn.png) UPSERT语法仅8.1.1及以上版本支持。 #### 注意事项 ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/warning_3.0-zh-cn.png) - 避免UPDATE/UPSERT并发更新同一张列存表。 - 更多开发设计规范参见[总体开发设计规范](https://support.huaweicloud.com/devg-dws/dws_04_0075.html)。 - **权限要求**：只有拥有表INSERT、UPDATE权限的用户，才可以通过UPSERT语句向表中插入或更新数据。 - **索引要求**：目标表上必须包含主键或者唯一索引才可以执行UPSERT的冲突更新语句。所有的唯一索引都不可用时不能执行UPSERT的冲突更新语句，重建索引后可以正常执行。 - **其它约束：** - 不支持与WITH子句同时使用。 - 不支持与INSERT OVERWRITE同时使用。 - UPDATE子句和UPDATE的WHERE子句不能有子查询。 - UPDATE子句中VALUES(colname)用法不支持外层嵌套函数，即不支持类似sqrt(VALUES(colname))用法。如需支持，使用EXCLUDED.colname语法。 - INSERT INTO ON CONFLICT(...) DO UPDATE必须有conflict_target。即必须指定列或者约束名。 - 不能更新分布列。**（例外：当分布键与更新值相同时）** 。 ``` 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子句或者索引条件表达式不能包含[不下推函数](https://support.huaweicloud.com/sqlreference-dws/dws_06_0163.html#ZH-CN_TOPIC_0000001811634697__section1365845133718)。 - 不支持延迟唯一索引。 - 通过INSERT INTO SELECT语句执行UPSERT的更新操作时，需要注意SELECT语句的查询结果顺序。在分布式环境中未使用ORDER BY语句时每次执行相同的SELECT语句返回结果顺序可能不一样，这会导致UPSERT语句的执行结果不符合预期。 - 不支持多次更新。插入多组数据间如果有冲突，则会出现类似"INSERT ON CONFLICT DO UPDATE command cannot affect row a second time"报错**（例外：当查询计划是PGXC计划时），** 详细案例参见[常见问题]。 - **性能影响** ： - 当在列存表上执行UPSERT时，建议开启DELTA表，能够有效防止执行UPSERT时产生小CU（大量的小CU会导致空间膨胀和查询性能差）。 - 对于列存表上的UPSERT、UPDATE、DELETE并发场景，由于并发更新到同一个CU时需要等待CU锁，无法支持这几个操作的并发执行，开启DELTA也无法解决该问题。如果需要支持列存表的UPSERT、UPDATE、DELETE并发场景，请使用HStore表。 - **死锁问题** ：可能存在分布式死锁导致查询hang问题。例如在一个事务中或者通过JDBC(setAutoCommit(false))批量执行多条UPSERT语句，多个类似任务同时执行时，由于不同线程在不同节点更新顺序可能不同，在存在并发更新同一行的场景里可能会有死锁问题。**解决办法** 如下： 1. 减小GUC参数[lockwait_timeout](https://support.huaweicloud.com/devg-dws/dws_04_0928.html)值（默认20分钟）。分布式死锁会等待lockwait_timeout时间然后报错。通过减小此参数的数值，降低死锁造成的业务等待时间。 2. 保证主键相同的数据只从一个数据库连接导入数据库，可以并发执行UPSERT语句。 3. 每个事务中只执行一条UPSERT语句，这样也可以并发执行UPSERT语句。 4. 单线程执行多条UPSERT语句，不能并发执行UPSERT语句。 如上解决办法中，方法1只能降低等待时间，无法解决死锁问题，在业务中有UPSERT语句时，仍建议减小此参数值。方法2、3、4均可以解决死锁问题，但建议采用方法2，其性能优于其他两个方法。 #### 语法格式 详细介绍请参见INSERT的[语法格式](https://support.huaweicloud.com/sqlreference-dws/dws_06_0236.html#ZH-CN_TOPIC_0000001764516334__se26969fe97994814b5f45a6173164204)。有两种UPSERT语法格式： 第一种**不指定索引**。会在所有主键或唯一索引上检查冲突，有冲突就会忽略或者更新。 第二种**指定索引**。会从ON CONFLICT子句中指定列名、包含列名的表达式或者约束名上推断主键或者唯一索引。 - **场景一：冲突更新语法** ：当插入的数据发生冲突时，使用新数据更新已有数据。 - **不指定索引的冲突更新** ： ``` INSERT INTO ON DUPLICATE KEY UPDATE ``` - **指定索引的冲突更新** ： ``` INSERT INTO ON CONFLICT(...) DO UPDATE SET INSERT INTO ON CONFLICT ON CONSTRAINT con_name DO UPDATE SET ``` - **场景二：冲突忽略语法** ：当插入的数据发生冲突时，直接忽略这次插入操作。 - **不指定索引的冲突忽略** ： ``` INSERT IGNORE INSERT INTO ON CONFLICT DO NOTHING ``` - **指定索引的冲突忽略** ： ``` INSERT INTO ON CONFLICT(...) DO NOTHING INSERT INTO ON CONFLICT ON CONSTRAINT con_name DO NOTHING ``` 表1UPSERT参数说明 | 参数 | 描述 | 取值范围或示例 | |:---|:---|:---| | ON CONFLICT (...) | 对于指定索引的语法形式，通过指定列名或者约束名推断主键或者唯一索引。列名可以是单一列名，或者由多个列名组成的表达式，比如（column1，column2，column3）。 由于创建索引时可以指定collation和opclass，所以此处列名后也可以指定。 COLLATE collation指定列的排序规则。opclass指定操作符类的名字。具体参考[CREATE INDEX](https://support.huaweicloud.com/sqlreference-dws/dws_06_0165.html)。 从指定列名的表达式中推断出唯一索引，整体原则是判断某唯一索引是否能够恰好完全包含conflict_target指定的列名。 - 如果没有指定collation和opclass，那么只要列或者列名的表达式相同（不管索引列上指定的collation和opclass是什么），都认为匹配。 - 如果指定collation和opclass，那么需要与索引的collation和opclass匹配才可以。 | - | | UPDATE子句 | UPDATE子句可以通过VALUES(colname)或者EXCLUDED.colname引用插入的数据。EXCLUDED表示因冲突原本该排除的数据行，语法示例参见[示例]。 | - | | WHERE子句 | - 用于在数据冲突时，判断是否满足指定条件。如果满足，则更新冲突数据；否则忽略。 - 只有第二种语法形式的冲突更新语法可以指定WHERE子句。即INSERT INTO ON CONFLICT(...) DO UPDATE SET WHERE | - | #### 示例 创建表reason_t2，并向表中插入数据： ``` DROP TABLE IF EXISTS reason_t2; 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; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002624830876.png "点击放大") 向表reason_t2中插入两条数据，一条有冲突，一条无冲突。有冲突的数据进行忽略，无冲突的数据进行插入： ``` INSERT INTO reason_t2 VALUES (1, 4, 5),(2, 6, 7) ON CONFLICT(a) DO NOTHING; SELECT * FROM reason_t2 ORDER BY 1; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002655190287.png "点击放大") 向表reason_t2中插入数据，一条有冲突，一条无冲突。有冲突的数据进行更新，无冲突的数据进行插入，EXCLUDED.b和EXCLUDED.c分别表示引用插入值的b列和c列，本例中为4，5。 ``` 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; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002655270363.png "点击放大") 根据过滤条件筛选被更新的行： ``` 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; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002624831032.png "点击放大") 向表reason_t中插入数据，有冲突的数据进行更新并调整更新映射关系，即c列更新到b，b列更新到c： ``` 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; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002655190371.png) #### 常见问题 不支持多次更新。插入多组数据间如果有冲突，则会出现类似"INSERT ON CONFLICT DO UPDATE command cannot affect row a second time"报错**（例外：当查询计划是PGXC计划时），** 以下是报错示例。 ``` CREATE TABLE t1(id int PRIMARY KEY, a int, b int); SET explain_perf_mode=pretty; -- 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: dn_xxxx: 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 ```