如何使用INSERTONCONFLICT覆蓋寫入資料 - 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進行全列更新。