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 Query Engine (HQE) を使用して INSERT ON CONFLICT ステートメントを実行する場合、データは順番に書き込まれません。そのため、Keep First ポリシーまたは Keep Last ポリシーを実装することはできません。ただし、データソースの主キー値を重複排除する場合は、Keep Last ポリシーを使用することをお勧めします。ステートメントの例:

  -- 重複行の最後の行を保持します。
  set hg_experimental_affect_row_multiple_times_keep_last = on;

例

• このセクションでは、INSERT ON CONFLICT ステートメントを使用する方法について説明します。

  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 モードを実装して、指定された列を更新します。指定された列のデータのみが更新されます。

       -- 特定の列を更新するには、DO UPDATE 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 値を挿入するには、手動で 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 という名前のテーブルを作成し、test2 テーブルにデータを挿入します。
       CREATE TABLE test2 (
           d int NOT NULL PRIMARY KEY,
           e int,
           f int
       );
       INSERT INTO test2 VALUES (1, 5, 6);
       
       
       -- test2 テーブルを使用して、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 テーブルを使用して、test2 テーブルと同じ主キー値を共有する test1 テーブルの行を上書きします。列マッピングが変更されます。test2 テーブルの列 e のデータは test1 テーブルの列 c に更新され、test2 テーブルの列 f のデータは test1 テーブルの列 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 は、行指向テーブルの INSERT ON CONFLICT ステートメントを最適化します。DO 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.*)

エラーとトラブルシューティング

• 問題の説明

  INSERT ON CONFLICT ステートメントを実行してテーブルにデータを挿入すると、次のいずれかのエラーが発生します。

  • エラーメッセージ 1: duplicate key value violates unique constraint

  • エラーメッセージ 2: Update row with Key (xxx)=(yyy) multiple times

• 原因 1: ソースデータに重複行が含まれています。

  Hologres は PostgreSQL と互換性があり、標準の PostgreSQL 構文を使用できます。標準の PostgreSQL 構文を使用して INSERT ON CONFLICT ステートメントを実行してテーブルにデータを挿入する場合は、ソースデータに重複行が含まれていないことを確認する必要があります。ソースデータに重複行が含まれている場合、上記のエラーメッセージのいずれかが表示されます。

  説明

  ソースデータの重複とは、ソースデータに重複行が含まれていることを意味します。ターゲットテーブルに挿入される行と同じ行が含まれていることを意味するものではありません。

  INSERT ON CONFLICT ステートメントで指定されたソースデータに重複行が含まれています。ステートメントの例:

  INSERT INTO test1 VALUES (1, 2, 3), (1, 2, 3)
  ON CONFLICT (a) 
  DO UPDATE SET (a, b, c) = ROW (excluded.*);

  解決策:

  ソースデータに重複行が含まれている場合は、次のコマンドを実行して最後の重複行を保持します。

  set hg_experimental_affect_row_multiple_times_keep_last = on;

• 原因 2: タイムトゥライブ (TTL) の期限が切れているため、ソーステーブルに重複データが含まれています。

  ソーステーブルに TTL が設定されています。ソーステーブルで TTL の期限が切れた一部のデータは、TTL が正確ではないため削除されません。これにより、ソーステーブルに重複データが発生する可能性があります。この場合、重複する主キー値を持つデータがターゲットテーブルに書き込まれると、上記のエラーメッセージのいずれかが表示されます。

  解決策:

  Hologres V1.3.23 以降では、次のステートメントを使用してこの問題を解決できます。ステートメントが実行されると、システムはテーブル内の重複する主キー値を削除します。デフォルトのクリーンアップポリシーは Keep Last で、重複する主キー値を持つ最後の行が保持され、他の行は削除されることを示します。

  説明

  • 原則として、テーブルには重複する主キー値は含まれません。そのため、このステートメントは、TTL の期限切れによって発生した重複する主キー値のみをクリアします。

  • Hologres V1.3.23 以降では、このステートメントがサポートされています。Hologres インスタンスのバージョンが V1.3.23 より前の場合は、Hologres インスタンスをアップグレードしてください。

  call public.hg_remove_duplicated_pk('<schema>.<table_name>');

  例: tbl_1 テーブルはターゲットテーブルです。tbl_2 テーブルはソーステーブルで、このソーステーブルには TTL が設定されています。TTL は 300 秒 に設定されています。tbl_2 テーブルのデータの行全体を tbl_1 テーブルに挿入します。TTL の期限が切れ、tbl_2 テーブルに重複する主キー値を持つデータが含まれている場合、データの書き込みに失敗し、エラーが発生します。

  
  BEGIN;
  CREATE TABLE tbl_1 (
    a int NOT NULL PRIMARY KEY,
    b int,
    c int
  );
  CREATE TABLE tbl_2 (
    d int NOT NULL PRIMARY KEY,
    e int,
    f int
  );
  CALL set_table_property('tbl_2', 'time_to_live_in_seconds', '300'); 
  COMMIT;
  
  INSERT INTO tbl_1 VALUES (1, 1, 1), (2, 3, 4);
  INSERT INTO tbl_2 VALUES (1, 5, 6);
  -- 300 秒後に tbl_2 テーブルにデータを挿入します。
  INSERT INTO tbl_2 VALUES (1, 3, 6);
  
  -- tbl_2 テーブルの行で tbl_1 テーブルの行を上書きします。tbl_2 テーブルに重複する主キー値を持つデータが含まれているため、エラーが報告されます。
  INSERT INTO tbl_1 (a, b, c)
  SELECT
      d,e,f
  FROM
      tbl_2
  ON CONFLICT (a)
      DO UPDATE SET
          (a,b,c) = ROW (excluded.*);
  -- 次のエラーメッセージが返されます。ERROR: internal error: Duplicate keys detected when building hash table.
  
  -- Grand Unified Configuration (GUC) パラメーターを設定して、tbl_2 テーブル内の重複する主キー値を持つデータを削除します。Keep Last ポリシーが使用されます。
  call public.hg_remove_duplicated_pk('tbl_2');
  
  -- tbl_1 テーブルにデータを再度挿入します。操作は成功します。