使用INSERT ON CONFLICT覆盖写入数据

本文介绍在AnalyticDB PostgreSQL版数据库中,如何使用INSERT ON CONFLICT语法覆盖写入数据。

针对数据写入时有主键冲突的情况,INSERT ON CONFLICT语法可以将冲突主键的INSERT行为转换为UPDATE行为,从而实现冲突主键的覆盖写入。该特性又称UPSERT覆盖写,与MySQL的REPLACE INTO类似。

注意事项

  • 仅支持行存表,不支持列存表(由于列存表不支持唯一索引,所以该特性无法支持列存表)。

  • 仅V6.3.6.1及以上内核版本支持在分区表中使用。如何升级内核版本,请参见版本升级

  • 不支持在UPDATE的SET子句中更新分布列和主键列。

  • 不支持在UPDATE的WHERE子句中使用子查询。

  • 不支持Updatable View(可更新视图)。

  • 不支持在同一条INSERT语句中对同一主键插入多条数据(国际SQL标准约束)。

SQL语法

覆盖写入语法基于INSERT语句,INSERT语句的语法大纲如下:

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
其中,conflict_target为:
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] )
其中,conflict_action为:
    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
                  } [, ...]
              [ WHERE condition ] 

ON CONFLICT子句可以实现覆盖写入。该子句由conflict_target和conflict_action组成。

参数

说明

conflict_target

  • conflict_action取值为Do Update时,conflict_target需要指定用来定义冲突的主键列或唯一索引列。

  • conflict_action取值为Do Nothing时,conflict_target可省略。

conflict_action

用于指定冲突后需要执行的动作。取值说明:

  • DO NOTHING:如果conflict_target指定的列有冲突,则丢弃待插入的数据。

  • DO UPDATE:如果conflict_target指定的列有冲突,则按照后面的UPDATE子句进行数据覆盖。

示例

创建一个表t1,表中拥有4列,其中a列为主键,建表语句如下:

CREATE TABLE t1 (a int PRIMARY KEY, b int, c int, d int DEFAULT 0);

对表t1插入一行数据,主键列a的值为0,插入数据语句如下:

INSERT INTO t1 VALUES (0,0,0,0);

查看表数据:

SELECT * FROM t1;

返回信息如下:

 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
(1 row)

如果再对表t1插入一行数据,主键列a的值还是0,则会返回一个报错,插入数据语句如下:

INSERT INTO t1 VALUES (0,1,1,1);

报错信息如下:

ERROR:  duplicate key value violates unique constraint "t1_pkey"
DETAIL:  Key (a)=(0) already exists.

如果不希望出现上述报错信息,可以使用本文介绍的覆盖写入特性来进行处理:

  • 使用ON CONFLICT DO NOTHING子句:主键冲突的情况下,不执行任何操作(适用于有冲突丢弃冲突数据的场景)。

    插入数据语句如下:

    INSERT INTO t1 VALUES (0,1,1,1) ON CONFLICT DO NOTHING;

    查看表数据:

    SELECT * FROM t1;

    表t1没有进行任何操作,返回示例如下:

     a | b | c | d
    ---+---+---+---
     0 | 0 | 0 | 0
    (1 row)
  • 使用ON CONFLICT DO UPDATE子句:主键冲突的情况下,更新非主键的列(适用于全部列覆盖写入的场景)。

    插入数据语句如下:

    INSERT INTO t1 VALUES (0,2,2,2) ON CONFLICT (a) DO UPDATE SET (b, c, d) = (excluded.b, excluded.c, excluded.d);

    INSERT INTO t1 VALUES (0,2,2,2) ON CONFLICT (a) DO UPDATE SET b = excluded.b, c = excluded.c, d = excluded.d; 

    在DO UPDATE SET子句中,可以使用excluded表示冲突的数据构成的伪表,在主键冲突的情况下,引用伪表中列的值覆盖原来列的值。上述语句中,新插入的数据(0,2,2,2)构成了一个伪表,伪表包含1行4列数据,表名为excluded,可以使用excluded.b, excluded.c, excluded.d去引用伪表中的列。

    查看表数据:

    SELECT * FROM t1;

    表t1中的非主键列进行了更新,返回示例如下:

     a | b | c | d
    ---+---+---+---
     0 | 2 | 2 | 2
    (1 row)

除了上述两种情况,覆盖写入功能支持更多使用场景,场景如下:

  • 主键冲突的情况下,在部分列中覆盖写入数据(适用于基于冲突数据覆盖部分列的场景):

    例如主键冲突后,仅覆盖c列的数据,插入数据语句如下:

    INSERT INTO t1 VALUES (0,0,3,0) ON CONFLICT (a) DO UPDATE SET c = excluded.c;

    查看表数据:

    SELECT * FROM t1;

    返回示例如下:

     a | b | c | d
    ---+---+---+---
     0 | 2 | 3 | 2
    (1 row)

  • 主键冲突的情况下,更新部分列的数据(适用于基于原始数据更新部分列场景):

    例如主键冲突后,将d列的数据加1,插入数据语句如下:

    INSERT INTO t1 VALUES (0,0,3,0) ON CONFLICT (a) DO UPDATE SET d = t1.d + 1;

    查看表数据:

    SELECT * FROM t1;

    返回示例如下:

     a | b | c | d
    ---+---+---+---
     0 | 2 | 3 | 3
    (1 row)

  • 主键冲突的情况下,更新数据为默认值(适用于冲突后,回退数据到默认值的场景):

    例如主键冲突后,将d列恢复到默认值(上文中d列的默认值为0),插入数据语句如下:

    INSERT INTO t1 VALUES (0,0,3,0) ON CONFLICT (a) DO UPDATE SET d = default;

    查看表数据:

    SELECT * FROM t1;

    返回示例如下:

     a | b | c | d
    ---+---+---+---
     0 | 2 | 3 | 0
    (1 row)

  • 插入多条数据:

    • 例如插入2行数据,其中主键冲突的行不进行任何操作,主键不冲突的行正常插入,插入数据语句如下:

      INSERT INTO t1 VALUES (0,0,0,0), (1,1,1,1) ON CONFLICT DO NOTHING;

      查看表数据:

      SELECT * FROM t1;

      返回示例如下:

       a | b | c | d
      ---+---+---+---
       0 | 2 | 3 | 0
       1 | 1 | 1 | 1
      (2 rows)

    • 例如插入2行数据,主键冲突的行进行覆盖写入,主键不冲突的行正常插入,插入数据语句如下:

      INSERT INTO t1 VALUES (0,0,0,0), (2,2,2,2) ON CONFLICT (a) DO UPDATE SET (b, c, d) = (excluded.b, excluded.c, excluded.d);

      查看表数据:

      SELECT * FROM t1;

      返回示例如下:

       a | b | c | d
      ---+---+---+---
       0 | 0 | 0 | 0
       1 | 1 | 1 | 1
       2 | 2 | 2 | 2
      (3 rows)

  • 插入的数据来自于子查询,如果主键冲突,则覆盖写入(用于合并两表数据或更复杂的INSERT INTO SELECT场景):

    创建表t2,数据结构与表t1一致,建表语句如下:

    CREATE TABLE t2 (like t1);

    在表t2中插入两行数据,插入数据语句如下:

    INSERT INTO t2 VALUES (2,22,22,22),(3,33,33,33);

    将表t2的数据插入表t1,如果主键冲突,则覆盖写入非主键的列,插入数据语句如下:

    INSERT INTO t1 SELECT * FROM t2 ON CONFLICT (a) DO UPDATE SET (b, c, d) = (excluded.b, excluded.c, excluded.d);

    查看表数据:

    SELECT * FROM t1;

    返回示例如下:

     a | b  | c  | d
    ---+----+----+----
     0 |  0 |  0 |  0
     1 |  1 |  1 |  1
     2 | 22 | 22 | 22
     3 | 33 | 33 | 33
    (4 rows)