SQL覆盖写入 INSERT ON CONFLICT

SQL覆盖写入 INSERT ON CONFLICT

ON CONFLICT DO UPDATE SET column_name = { expression | DEFAULT }

ON CONFLICT DO UPDATE NOTHING

[ 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需要指定用来定义冲突的主键列或唯一索引列。conflick_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)