使用INSERT ON CONFLICT覆盖写入数据_云原生数据仓库AnalyticDB(AnalyticDB)-阿里云帮助中心

在数据同步或批量导入等场景中，为避免主键冲突导致写入失败，您可以使用INSERT ON CONFLICT语句。该语句能够实现“存在即更新，不存在即插入”的覆盖写入逻辑，该特性又称UPSERT覆盖写，类似于MySQL的REPLACE INTO功能。本文为您介绍INSERT ON CONFLICT语句的语法与使用示例。

注意事项

仅内核版本为V6.3.6.1及以上的AnalyticDB for PostgreSQL实例支持在分区表中使用INSERT ON CONFLICT语句。
说明
您可以在控制台实例的基本信息页查看内核小版本。如不满足上述版本要求，需要您升级内核小版本。
仅支持行存表Heap和行列存储引擎Beam，不支持列存表AO/AOCS（由于列存表不支持唯一索引，所以该特性无法支持列存表）。查询指定表的存储引擎，具体方法请参见如何查询表的存储引擎？。
不支持在同一条INSERT语句中对同一主键插入多条数据（国际SQL标准约束）。

SQL语法

基础语法

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT [ conflict_target ] conflict_action

完整语法

[ 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 ] [, ...] )[ WHERE index_predicate ]
    ON CONSTRAINT constraint_name
    
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子句进行数据覆盖。在DO UPDATE SET子句中，可通过伪表excluded访问冲突行的数据。
重要
- 不支持在UPDATE的SET子句中更新分布列和主键列。
- 不支持在UPDATE的WHERE子句中使用子查询。
- Beam存储引擎不支持部分列更新，支持DO UPDATE ALL全列更新。

使用示例

数据准备

创建一个表t1，其中a列为主键，建表语句如下：

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

（可选）当存储引擎为Beam时，如果需要冲突时更新部分列数据，则需将表的存储引擎变更为heap。
```
ALTER TABLE t1 SET ACCESS METHOD heap;
```
向表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;

返回示例如下：

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

冲突时覆盖非主键数据

如果您希望在主键冲突的情况下，覆盖写入数据，可以使用ON CONFLICT DO UPDATE子句。

插入数据。

在DO UPDATE SET子句中，excluded是一个特殊的伪表，包含了试图插入但冲突的行数据。在主键冲突的情况下，引用伪表中列的值覆盖原来列的值。

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

或

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

查看表数据：
```
SELECT * FROM t1;
```
返回示例如下，可以看到表t1中的非主键列进行了更新：
```
 a | b | c | d
---+---+---+---
 0 | 1 | 1 | 1
(1 row)
```

冲突时更新部分列数据

如果您希望在主键冲突的情况下，仅需要对部分列，用新插入的数据覆盖原数据，或者基于原始数据进行更新，可参考以下示例。

为了验证冲突处理逻辑是否按预期工作，建议更新测试数据。
```
UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```

用新插入的数据覆盖原数据。

仅覆盖c列的数据，插入数据语句：

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

查看表数据：

SELECT * FROM t1;

返回示例如下：

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

基于原始数据进行更新。

主键冲突后，将c列的数据加1，插入数据语句如下：

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

查看表数据：

SELECT * FROM t1;

返回示例如下：

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

冲突时更新为默认值

在主键冲突的情况下，更新数据为默认值：

为了验证冲突处理逻辑是否按预期工作，建议更新测试数据。
```
UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```

主键冲突时，将d列恢复到默认值，插入数据语句如下：

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

查看表数据：

SELECT * FROM t1;

返回示例如下：

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

覆盖写入多条数据

直接插入多条数据

插入2行数据，其中主键冲突的行不进行任何操作，主键不冲突的行正常插入。
1. 为了验证冲突处理逻辑是否按预期工作，建议更新测试数据。
```
UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```
2. 插入数据：
```
INSERT INTO t1 
VALUES (0,2,2,2), (3,3,3,3) 
ON CONFLICT DO NOTHING;
```
3. 查看表数据：
```
SELECT * FROM t1;
```
  返回示例如下：
```
 a | b | c | d
---+---+---+---
 3 | 3 | 3 | 3
 0 | 1 | 1 | 1
(2 rows)
```

插入2行数据，主键冲突的行进行覆盖写入，主键不冲突的行正常插入.

插入数据：

INSERT INTO t1 
VALUES (0,0,0,0), (4,4,4,4) 
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
 3 | 3 | 3 | 3
 4 | 4 | 4 | 4
(3 rows)

通过子查询语句插入多条数据

当您插入的数据来源于子查询时，如果主键冲突，则覆盖写入（用于合并两表数据或更复杂的INSERT INTO SELECT场景），示例如下。

为了验证冲突处理逻辑是否按预期工作，建议更新测试数据。
```
DELETE FROM t1 WHERE a != 0; 

UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```
创建表t2，数据结构与表t1一致，建表语句如下：
```
CREATE TABLE t2 (like t1);
```
在表t2中插入两行数据，插入数据语句如下：
```
INSERT INTO t2 
VALUES (0,11,11,11),(2,22,22,22);
```

将表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
---+----+----+----
 2 | 22 | 22 | 22
 0 | 11 | 11 | 11
(2 rows)

Beam表冲突时全列更新

如果您的实例存储引擎为Beam，支持使用INSERT ON CONFLICT DO UPDATE ALL进行全列更新。

创建一个表beam_test，其中a列为主键，建表语句如下：

CREATE TABLE beam_test (
  a int PRIMARY KEY, 
  b int, 
  c int, 
  d int DEFAULT 0
) USING  beam;

向表beam_test中插入一行数据，主键列a的值为0，插入数据语句如下：
```
INSERT INTO beam_test 
VALUES
  (0, 0, 0, 0),
  (1, 1, 1, 1),
  (2, 2, 2, 2);
```

查看表数据：

SELECT * FROM beam_test;

返回示例如下：

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

插入数据：

INSERT INTO beam_test 
VALUES(0, 4, 4, 4) 
ON CONFLICT (a)  
DO UPDATE ALL;

查看表数据：

SELECT * FROM beam_test;

返回示例如下：

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

常见问题

如何查询表的存储引擎？

您可以通过以下SQL查询指定表的存储引擎：

SELECT
c.oid::regclass AS rel,
coalesce(a.amname, 'heap') AS table_am
FROM pg_class c
LEFT JOIN pg_am a ON a.oid = c.relam
WHERE c.oid = 'schamename.tablename'::regclass
AND c.relkind = 'r';

Beam表覆盖写入数据时，仅更新部分列报错如何解决？

问题：如果您的实例存储引擎为Beam，并使用INSERT ON CONFLICT语句仅更新部分列，会出现如下报错。

ERROR:  INSERT ON CONFLICT DO UPDATE SET is not supported for beam relations
HINT:  Please use INSERT INTO table VALUES(?,?,...) ON CONFLICT DO UPDATE ALL.

原因及解决：Beam存储引擎不支持更新部分列，您可以使用INSERT ON CONFLICT DO UPDATE ALL进行全列更新。