文档首页/ 数据仓库服务 GaussDB(DWS)/ SQL语法参考/ SQL语法参考(8.3.0.x)/ DML语法/ INSERT和UPSERT/ UPSERT
更新时间：2024-10-25 GMT+08:00
查看PDF
分享

UPSERT

功能描述

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

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

语法格式

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

表1 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

第一种不指定索引。会在所有主键或唯一索引上检查冲突，有冲突就会忽略或者更新。

第二种指定索引。会从ON CONFLICT子句中指定列名、包含列名的表达式或者约束名上推断主键或者唯一索引。

  • 唯一索引推断

    对于第二种语法形式，通过指定列名或者约束名推断主键或者唯一索引。列名可以是单一列名，或者由多个列名组成的表达式，比如（column1，column2，column3）。

    由于创建索引时可以指定collation和opclass，所以此处列名后也可以指定。

    COLLATE collation指定列的排序规则。opclass指定操作符类的名字。具体参考CREATE INDEX。

    从指定列名的表达式中推断出唯一索引，整体原则是判断某唯一索引是否能够恰好完全包含conflict_target指定的列名。
    • 如果没有指定collation和opclass，那么只要列或者列名的表达式相同（不管索引列上指定的collation和opclass是什么），都认为匹配。
    • 如果指定collation和opclass，那么需要与索引的collation和opclass匹配才可以。
  • UPDATE子句

UPDATE子句可以通过VALUES(colname)或者EXCLUDED.colname引用插入的数据。EXCLUDED表示因冲突原本该排除的数据行。示例如下：

1
2
3
4
5
6
7
 
CREATE TABLE t1(id int PRIMARY KEY, a int, b int);
INSERT INTO t1 VALUES(1,1,1);
-- 对于冲突行，把a列修改为目标表a列值加1，更新为(1,2,1)
INSERT INTO t1 VALUES(1,10,20) ON CONFLICT(id) DO UPDATE SET a = a + 1;
-- EXCLUDED.a 表示引用插入值的a列。本例中为10。
-- 对于冲突行，把a列修改为引用插入的a列值。更新为(1,11,1)
INSERT INTO t1 VALUES(1,10,20) ON CONFLICT(id) DO UPDATE SET a = EXCLUDED.a + 1;
  • WHERE子句
    • 用于在数据冲突时，判断是否满足指定条件。如果满足，则更新冲突数据。否则忽略。
    • 只有第二种语法形式的冲突更新语法可以指定WHERE子句。即 INSERT INTO ON CONFLICT(...) DO UPDATE SET WHERE
语法使用注意事项：
  • 表1中几种语法形式不能在同一个语句中一起使用。
  • 不支持与WITH子句同时使用。
  • 不支持与INSERT OVERWRITE同时使用。
  • UPDATE子句和UPDATE的WHERE子句不能有子查询。
  • UPDATE子句中VALUES(colname)用法不支持外层嵌套函数，即不支持类似sqrt(VALUES(colname))用法。如需支持，使用EXCLUDED.colname语法。
  • INSERT INTO ON CONFLICT(...) DO UPDATE必须有conflict_target。即必须指定列或者约束名。

注意事项

  • 当在列存表上执行UPSERT时，建议开启DELTA表，能够有效防止执行UPSERT时产生小CU（大量的小CU会导致空间膨胀和查询性能差）。
  • 对于列存表上的UPSERT、UPDATE、DELETE并发场景，由于并发更新到同一个CU时需要等待CU锁，无法支持这几个操作的并发执行，开启DELTA也无法解决该问题。如果需要支持列存表的UPSERT、UPDATE、DELETE并发场景，请使用HStore表。
  • 只有拥有表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子句或者索引条件表达式不能包含不下推函数。
  • 不支持延迟唯一索引。
  • 通过INSERT INTO SELECT语句执行UPSERT的更新操作时，需要注意SELECT语句的查询结果顺序。在分布式环境中未使用ORDER BY语句时每次执行相同的SELECT语句返回结果顺序可能不一样，这会导致UPSERT语句的执行结果不符合预期。
  • 不支持多次更新。插入多组数据间如果有冲突，则会出现类似“INSERT ON CONFLICT DO UPDATE command cannot affect row a second time”报错（例外：当查询计划是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
44
    		 
    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

示例

创建表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)
父主题： INSERT和UPSERT