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 高 QPS エンジン (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 以降では、サーバーレスコンピューティングがサポートされています。大規模なオフラインデータインポート、大規模な抽出、変換、ロード (ETL) ジョブ、外部テーブルに対する大量のクエリなどのシナリオでは、サーバーレスコンピューティングを使用してこれらのタスクを実行できます。この機能は、ご利用のインスタンス独自のリソースの代わりに、追加のサーバーレスリソースを使用します。インスタンスに追加の計算リソースを予約する必要はありません。これにより、インスタンスの安定性が大幅に向上し、メモリ不足 (OOM) エラーの可能性が減少し、個々のタスクに対してのみ課金されます。サーバーレスコンピューティングの詳細については、「サーバーレスコンピューティング」をご参照ください。サーバーレスコンピューティングの使用方法については、「サーバーレスコンピューティングの使用ガイド」をご参照ください。

  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 値で埋められます。

       -- 欠落している列が null で埋められる InsertOrReplace 操作を実装するには、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：test1 テーブルのデータを別のテーブル test2 から更新します。

       -- test2 テーブルとデータを準備します。
       CREATE TABLE test2 (
           d int NOT NULL PRIMARY KEY,
           e int,
           f int
       );
       INSERT INTO test2 VALUES (1, 5, 6);
       
       
       -- test1 テーブルの行を、同じプライマリキーを持つ test2 テーブルの行で置き換えます。
       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
       
       -- test1 テーブルの行を、同じプライマリキーを持つ test2 テーブルの行で置き換えますが、更新マッピングを調整します。test2 の列 e は列 c を更新し、test2 の列 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.*)

よくあるエラー