Hologres：INSERT ON CONFLICT(UPSERT)

應用情境

INSERT ON CONFLICT命令適用於通過SQL方式匯入資料的情境。

命令介紹

INSERT INTO <table_name> [ AS <alias> ] [ ( <column_name> [, ...] ) ]
    {  VALUES ( { <expression> } [, ...] ) [, ...] | <query> }
    [ ON CONFLICT [ conflict_target ] conflict_action ]

where conflict_target is pk

    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { <column_name> = { <expression> } |
                    ( <column_name> [, ...] ) = ( { <expression> } [, ...] ) |
                  } [, ...]
              [ WHERE condition ]

技術原理

使用限制

• INSERT ON CONFLICT語句的條件必須包含所有主鍵。

• Hologres HQE在執行INSERT ON CONFLICT時，本身不會保序（保證順序），因此不能實現keep first、keep last的效果，都是keep any。但在實際應用中，如果資料來源有主鍵重複資料需要去重，建議使用keep last，命令如下：

  --保留重複資料的最後一條資料
  set hg_experimental_affect_row_multiple_times_keep_last = on;

使用樣本

• INSERT ON CONFLICT語句的樣本用法：

  說明

  Hologres從V2.1.17版本起支援Serverless Computing能力，針對巨量資料量離線匯入、大型ETL作業、外表巨量資料量查詢等情境，使用Serverless Computing執行該類任務可以直接使用額外的Serverless資源，避免使用執行個體自身資源，無需為執行個體預留額外的計算資源，顯著提升執行個體穩定性、減少OOM機率，且僅需為任務單獨付費。Serverless Computing詳情請參見Serverless Computing概述，Serverless Computing使用方法請參見Serverless Computing使用指南。

  1. 準備表和資料：

     begin ;
     create table test1 (
         a int NOT NULL PRIMARY KEY,
         b int,
         c int
     );
     commit ;
     
     insert into test1 values (1,2,3);
     

  2. 不同情境下的使用樣本：

     說明

     下面的每個情境樣本結果不相互依賴，沒有循序關聯性，都是基於上述已建立的表和資料的結果。

     • 情境1：實現InsertOrIgnore，即主鍵重複不更新。

       INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
       ON CONFLICT (a)
       DO NOTHING;
       
       --更新後test1表的資料為：
       a	b	c
       1	2	3

     • 情境2：實現InsertOrUpdate的整行更新，可以通過如下兩種方式實現。

       • 方式1：在SET..EXCLUDED中列出所有的列。

         INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
         ON CONFLICT (a)
         DO UPDATE SET b = EXCLUDED.b, c = EXCLUDED.c;
         
         --更新後test1表的資料為：
         a	b	c
         1	1	1

       • 方式2：使用ROW(EXCLUDED.*)代表更新所有列。

         INSERT INTO test1 (a, b, c)VALUES (1, 1, 1)
         ON CONFLICT (a)
         DO UPDATE SET (a,b,c) = ROW(EXCLUDED.*);
         
         --更新後test1表的資料為：
         a	b	c
         1	1	1

     • 情境3：實現InsertOrUpdate的部分列更新，即只更新指定列，缺失的列不更新。

       --要實現部分列更新的效果，需要在set後列出想要更新的列
       INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
       ON CONFLICT (a)
       DO UPDATE SET b = EXCLUDED.b;
       
       --表中c列不更新，更新後test1表的資料為:
       a	b	c
       1	1	3

     • 情境4：實現InsertOrReplace，即整行覆蓋，如果有缺失的列，缺失的列補null。

       --如果要實現InsertOrReplace，且缺失的列補null，則需要在insert的值中手動補null。
       INSERT INTO test1 (a, b,c) VALUES (1, 1,null)
       ON CONFLICT (a)
       DO UPDATE SET b = EXCLUDED.b,c = EXCLUDED.c;
       
       --更新後test1表的資料為：
       a	b	c
       1	1	\N

     • 情境5：從另外一張test2表更新test1表資料。

       --準備test2表和資料
       CREATE TABLE test2 (
           d int NOT NULL PRIMARY KEY,
           e int,
           f int
       );
       INSERT INTO test2 VALUES (1, 5, 6);
       
       
       --將test2整表替換test1表相同主鍵的行
       INSERT INTO test1 (a, b, c) SELECT d,e,f FROM test2 ON CONFLICT (a) DO UPDATE SET
       (a,b,c) = ROW (excluded.*);
       
       --更新後test1表資料如下：
       a	b	c
       1	5	6
       
       --將test2整表替換test1表相同主鍵的行,但調整了更新映射關係，即test2的e列更新到c列，f列更新到b列
       INSERT INTO test1 (a, b, c) SELECT d,e,f FROM test2 ON CONFLICT (a) DO UPDATE SET
       (a,c,b) = ROW (excluded.*);
       --更新後test1表資料如下：       
       a	b	c
       1	6	5

• 行存表INSERT ON CONFLICT語句的最佳化：

  Hologres對行存表的更新情境實行了最佳化，建議您在使用時將UPDATE列的順序與INSERT的順序保持一致，並且更新為整行更新。

  INSERT INTO test1 (a, b, c) 
  SELECT
      d,e,f
  FROM
      test2
  ON CONFLICT (a)
      DO UPDATE SET
          a = excluded.a,
          b = excluded.b,
          c = excluded.c;
          
  INSERT INTO test1 (a, b, c)
  SELECT d,e,f FROM test2
  ON CONFLICT (a)
  DO UPDATE SET(a,b,c) = ROW (excluded.*)

常見報錯