更新时间:2024-09-02 GMT+08:00

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)