全部產品
Search
文件中心

AnalyticDB:使用INSERT ON CONFLICT覆蓋寫入資料

更新時間:Feb 05, 2024

本文介紹在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)