INSERT ON CONFLICT语句用于在指定列插入某行数据时,如果主键存在重复的行数据,则对该数据执行更新或跳过操作,实现UPSERT(INSERT OR UPDATE)的效果。INSERT ON CONFLICT的执行开销要小于UPDATE语句,推荐将UPDATE语句改写为INSERT ON CONFLICT,语义一致。本文为您介绍在Hologres中INSERT ON CONFLICT语句的用法。

使用限制

  • INSERT ON CONFLICT语句的条件必须包含所有主键。
  • 推荐使用Fixed Plan优化Insert on Conflict执行效率,参考INSERT场景
  • 如果系统提示实例版本过低不支持该功能。您可以执行如下命令或提交工单升级实例至最新版本。
    set hg_experimental_enable_insert_on_conflict = on; 

应用场景

INSERT ON CONFLICT命令适用于通过SQL方式导入数据的场景。

使用数据集成或Flink写入数据时,如果需要对主键重复的行数据执行更新或跳过操作,则需进行如下配置:
  • 通过DataWorks的数据集成导入数据。
    数据集成已内置INSERT ON CONFLICT功能,该功能的实现原理请参见Hologres Writer。同时,您需要进行如下配置:
    • 离线同步数据时,写入冲突策略选择忽略(Ignore)或者更新(Replace)
    • 实时同步数据时,写入冲突策略选择忽略(Ignore)或者更新(Replace)
    说明 同步数据时,Hologres的表均需要设置主键,才能更新数据。
  • 通过Flink写入数据。

    通过Flink写入数据默认写入冲突策略使用更新(Replace),但是需要您在Hologres建表时设置主键。详情请参见Hologres结果表

命令格式

INSERT ON CONFLICT的语法格式如下。
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    {  VALUES ( { expression } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]

where conflict_target is pk

    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression } |
                    ( column_name [, ...] ) = ( { expression } [, ...] ) |
                  } [, ...]
              [ WHERE condition ]
参数说明如下表所示。
参数 描述
DO NOTHING 在指定列插入某行数据时,如果主键存在重复的行数据,则对该数据执行跳过操作。
DO UPDATE 在指定列插入某行数据时,如果主键存在重复的行数据,则对该数据执行更新操作。
expression

对应列执行的相关表达式,您可以参考Postgres来设置表达式,详情请参见INSERT ON CONFLICT

常用表达式 b=excluded.b,或者(a, b, c) = ROW (excluded.*)简化表达,等式左侧表示要被更新的字段,等式右侧表示插入的表达式,即values部分的值,或者select表达式,excluded是对插入表达式的别名,不是写入源头表的别名。例如,column_name = excluded.column_namecolumn_name为插入数据至目标表指定列的列名称,假设column_name为目标表的第N列,则excluded.column_name为插入表达式的第N列,当使用excluded.*时,表示选择所有列,列的顺序为插入表达式中列的顺序,需要保证插入目标列的顺序与被写入表的DDL顺序一致。

使用示例

  • INSERT ON CONFLICT语句的示例用法:
    BEGIN;
    CREATE TABLE tbl_1 (
        a int NOT NULL PRIMARY KEY,
        b int,
        c int
    );
    CREATE TABLE tbl_2 (
        d int NOT NULL PRIMARY KEY,
        e int,
        f int
    );
    COMMIT;
    
    INSERT INTO tbl_1
        VALUES (1, 1, 1), (2, 3, 4);
    --此时tbl_1数据如下
    a    b    c
    2    3    4
    1    1    1
    
    INSERT INTO tbl_2
        VALUES (1, 5, 6);
    --此时tbl_2数据如下
    d    e    f
    1    5    6
    
    --主键相同时,将表tbl_2的某列数据更新到表tbl_1中。
    INSERT INTO tbl_1 (a, b)
    SELECT
        d,
        e
    FROM
        tbl_2
    ON CONFLICT (a)
        DO UPDATE SET
            b = excluded.b;
    --此时tbl_1数据如下
    a    b    c
    2    3    4
    1    5    1
    
    --根据过滤条件筛选被更新的行
    INSERT INTO tbl_1
        VALUES (2, 7, 8)
    ON CONFLICT (a)
        DO UPDATE SET
            b = excluded.b, c = excluded.c
        WHERE
            tbl_1.c = 4;
    --此时tbl_1数据如下
    a    b    c
    2    7    8
    1    5    1
    
    --主键相同时,向表tbl_1插入表tbl_2的数据,系统直接跳过表tbl_2的数据(即插入数据失败)。
    INSERT INTO tbl_1
    SELECT
        *
    FROM
        tbl_2
    ON CONFLICT (a)
        DO NOTHING;
    --此时tbl_1数据如下
    a    b    c
    2    7    8
    1    5    1
    
    --do nothing不指定冲突列时,默认冲突列为主键。
    INSERT INTO tbl_1
    SELECT
        *
    FROM
        tbl_2
    ON CONFLICT
        DO NOTHING;
    --此时tbl_1数据如下
    a    b    c
    2    7    8
    1    5    1
    
    ---更新整行数据。
    INSERT INTO tbl_1
        VALUES (1, 2, 3)
    ON CONFLICT (a)
        DO UPDATE SET
            (a, b, c) = ROW (excluded.*);
    --此时tbl_1数据如下
    a    b    c
    2    7    8
    1    2    3
    
    --将tbl_2整表替换tbl1表相同主键的行
    INSERT INTO tbl_1 (a, b, c)
    SELECT
        d,e,f
    FROM
        tbl_2
    ON CONFLICT (a)
        DO UPDATE SET
            (a,b,c) = ROW (excluded.*)
    --此时tbl_1数据如下
    a    b    c
    2    7    8
    1    5    6
    
    --将tbl_2整表替换tbl1表相同主键的行,但调整了更新映射关系,即tbl_2的e列更新到c,f列更新到b
    INSERT INTO tbl_1 (a, b, c)
    SELECT
        d,e,f
    FROM
        tbl_2
    ON CONFLICT (a)
        DO UPDATE SET
            (a,c,b) = ROW (excluded.*)
    --此时tbl_1数据如下
    a    b    c
    2    7    8
    1    6    5
      
  • 行存表INSERT ON CONFLICT语句的优化:

    Hologres对行存表的更新场景实行了优化,建议您在使用时将UPDATE列的顺序与INSERT的顺序保持一致,并且更新为整行更新。

    INSERT INTO tbl_1 (a, b, c)
    SELECT
        d,e,f
    FROM
        tbl_2
    ON CONFLICT (a)
        DO UPDATE SET
            a = excluded.a,
            b = excluded.b,
            c = excluded.c;
    
    INSERT INTO tbl_1 (a, b, c)
    SELECT
        d,e,f
    FROM
        tbl_2
    ON CONFLICT (a)
        DO UPDATE SET
            (a,b,c) = ROW (excluded.*)

常见报错

  • 问题现象
    对数据源执行INSERT ON CONFLICT语句时出现如下两种报错其中一个。
    • 报错一:

      Update row with Key ()=() multiple times.

      insert on conflict 常见报错
    • 报错二:

      ERROR:duplicate key value violates unique constraint

      常见报错
  • 问题原因一:数据源存在重复数据。
    Hologres兼容PostgreSQL,使用的也是标准PostgreSQL语法。在标准的PostgreSQL语义中,对数据源执行INSERT ON CONFLICT语句时,数据源不能包含重复数据,如果包含重复数据则会产生上述报错。
    说明 数据源重复是指待插入的数据中包含重复数据,不是指待插入的数据与表里的数据重复。
    使用INSERT ON CONFLICT语句插入数据时包含重复数据,示例语句如下。
    INSERT INTO tbl_1
        VALUES (1, 2, 3), (1, 2, 3)
    ON CONFLICT (a)
        DO UPDATE SET
            (a, b, c) = ROW (excluded.*);
    解决方法:
    如果数据源包含重复数据,可以配置如下参数,分别选择保留重复数据的第一条数据和最后一条数据。
    • 保留重复数据的第一条数据
      set hg_experimental_affect_row_multiple_times_keep_first = on;
    • 保留重复数据的最后一条数据
      set hg_experimental_affect_row_multiple_times_keep_last = on;
  • 问题原因二:数据源因TTL过期出现重复数据。

    数据源中有表设置过表数据生命周期(TTL),表中有部分数据已经过了TTL,因TTL不是准确的时间,导致过期的数据未被清理,导入时主键数据重复,从而出现报错。

    解决方法:
    Hologres从 V1.3.23版本开始,通过以下命令能快速修正因TTL过期PK重复的数据。执行后该命令后,系统会将该表PK重复的数据清理掉,清理策略默认为Keep Last即保留重复PK中最后一条写入的PK数据,其余重复PK数据进行清理。
    call hg_remove_duplicated_pk('<schema>.<table_name>');
    使用示例:假设有两个表,tbl_1为目标表,tbl_2为源表且配置了TTL,时间设置为300s。将tbl_2的数据整行更新至tbl_1,因TTL过期后,tbl_2的主键重复,导致PK重复。
    BEGIN;
    CREATE TABLE tbl_1 (
        a int NOT NULL PRIMARY KEY,
        b int,
        c int
    );
    CREATE TABLE tbl_2 (
        d int NOT NULL PRIMARY KEY,
        e int,
        f int
    );
    CALL set_table_property('tbl_2', 'time_to_live_in_seconds', '300');
    COMMIT;
    
    INSERT INTO tbl_1 VALUES (1, 1, 1), (2, 3, 4);
    INSERT INTO tbl_2 VALUES (1, 5, 6);
    --过300s后再向tbl_2插入数据
    INSERT INTO tbl_2 VALUES (1, 3, 6);
    
    --将tbl_2整表替换tbl1表相同主键的行,pk因ttl重复了导致更新报错
    INSERT INTO tbl_1 (a, b, c)
    SELECT
        d,e,f
    FROM
        tbl_2
    ON CONFLICT (a)
        DO UPDATE SET
            (a,b,c) = ROW (excluded.*);
    --执行报错:错误原因:ERROR: internal error: Duplicate keys detected when building hash table.
    
    --guc清理tbl_2的pk重复数据,策略为keep last,
    call hg_remove_duplicated_pk('tbl_2');
    
    --再重新导入tbl_1数据,数据导入成功
    INSERT INTO tbl_1 (a, b, c)
    SELECT
        d,e,f
    FROM
        tbl_2
    ON CONFLICT (a)
        DO UPDATE SET
            (a,b,c) = ROW (excluded.*);