Hologres：INSERT ON CONFLICT (UPSERT)

Skenario

Perintah INSERT ON CONFLICT cocok untuk skenario impor data menggunakan pernyataan SQL.

Pengenalan perintah

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 ]

Cara Kerja

Batasan

• Kondisi dalam pernyataan INSERT ON CONFLICT harus mencakup semua primary key.

• Saat Hologres High-QPS Engine (HQE) mengeksekusi pernyataan INSERT ON CONFLICT, urutan operasi tidak dijamin. Oleh karena itu, Anda tidak dapat mencapai semantik keep-first atau keep-last. Perilakunya adalah keep-any. Dalam praktiknya, jika sumber data berisi primary key duplikat yang perlu dihapus, Anda dapat menggunakan semantik keep-last dengan menjalankan perintah berikut:

  -- Simpan baris terakhir dari baris duplikat.
  set hg_experimental_affect_row_multiple_times_keep_last = on;

Contoh

• Contoh penggunaan pernyataan INSERT ON CONFLICT:

  Catatan

  Hologres V2.1.17 dan versi yang lebih baru mendukung Serverless Computing. Untuk skenario seperti impor data offline berskala besar, pekerjaan extract, transform, and load (ETL) besar, dan kueri volume besar pada tabel eksternal, Anda dapat menggunakan Serverless Computing untuk mengeksekusi tugas-tugas tersebut. Fitur ini menggunakan sumber daya serverless tambahan alih-alih sumber daya instans Anda sendiri. Anda tidak perlu menyediakan sumber daya komputasi tambahan untuk instans Anda. Hal ini secara signifikan meningkatkan stabilitas instans, mengurangi kemungkinan error out-of-memory (OOM), dan Anda hanya dikenai biaya untuk setiap tugas individual. Untuk informasi selengkapnya tentang Serverless Computing, lihat Serverless Computing. Untuk informasi tentang cara menggunakan Serverless Computing, lihat Panduan penggunaan Serverless Computing.

  1. Persiapkan tabel dan data:

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

  2. Lihat contoh untuk skenario berbeda:

     Catatan

     Contoh skenario berikut bersifat independen satu sama lain. Mereka tidak berurutan dan semuanya didasarkan pada tabel dan data yang dibuat pada langkah sebelumnya.

     • Skenario 1: Terapkan operasi InsertOrIgnore. Jika terdapat primary key duplikat, baris tidak diperbarui.

       INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
       ON CONFLICT (a)
       DO NOTHING;
       
       -- Data dalam tabel test1 setelah pembaruan:
       a	b	c
       1	2	3

     • Skenario 2: Terapkan operasi InsertOrUpdate untuk pembaruan seluruh baris. Anda dapat menggunakan salah satu metode berikut:

       • Metode 1: Cantumkan semua kolom dalam klausa SET..EXCLUDED.

         INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
         ON CONFLICT (a)
         DO UPDATE SET b = EXCLUDED.b, c = EXCLUDED.c;
         
         -- Data dalam tabel test1 setelah pembaruan:
         a	b	c
         1	1	1

       • Metode 2: Gunakan ROW(EXCLUDED.*) untuk memperbarui semua kolom.

         INSERT INTO test1 (a, b, c)VALUES (1, 1, 1)
         ON CONFLICT (a)
         DO UPDATE SET (a,b,c) = ROW(EXCLUDED.*);
         
         -- Data dalam tabel test1 setelah pembaruan:
         a	b	c
         1	1	1

     • Skenario 3: Terapkan operasi InsertOrUpdate untuk pembaruan kolom parsial. Hanya kolom yang ditentukan yang diperbarui, dan kolom yang tidak disebutkan tidak diperbarui.

       -- Untuk melakukan pembaruan kolom parsial, cantumkan kolom yang ingin Anda perbarui setelah SET.
       INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
       ON CONFLICT (a)
       DO UPDATE SET b = EXCLUDED.b;
       
       -- Kolom c dalam tabel tidak diperbarui. Data dalam tabel test1 setelah pembaruan:
       a	b	c
       1	1	3

     • Skenario 4: Terapkan operasi InsertOrReplace. Ini menimpa seluruh baris. Jika kolom tidak disebutkan, kolom tersebut diisi dengan nilai null.

       -- Untuk menerapkan operasi InsertOrReplace di mana kolom yang tidak disebutkan diisi dengan null, Anda harus secara manual memberikan null dalam nilai insert.
       INSERT INTO test1 (a, b,c) VALUES (1, 1,null)
       ON CONFLICT (a)
       DO UPDATE SET b = EXCLUDED.b,c = EXCLUDED.c;
       
       -- Data dalam tabel test1 setelah pembaruan:
       a	b	c
       1	1	\N

     • Skenario 5: Perbarui data dalam tabel test1 dari tabel lain bernama test2.

       -- Persiapkan tabel test2 dan data.
       CREATE TABLE test2 (
           d int NOT NULL PRIMARY KEY,
           e int,
           f int
       );
       INSERT INTO test2 VALUES (1, 5, 6);
       
       
       -- Ganti baris dalam tabel test1 dengan baris dari tabel test2 yang memiliki primary key yang sama.
       INSERT INTO test1 (a, b, c) SELECT d,e,f FROM test2 ON CONFLICT (a) DO UPDATE SET
       (a,b,c) = ROW (excluded.*);
       
       -- Data dalam tabel test1 setelah pembaruan:
       a	b	c
       1	5	6
       
       -- Ganti baris dalam tabel test1 dengan baris dari tabel test2 yang memiliki primary key yang sama, tetapi sesuaikan pemetaan pembaruan. Kolom e dari test2 memperbarui kolom c, dan kolom f dari test2 memperbarui kolom b.
       INSERT INTO test1 (a, b, c) SELECT d,e,f FROM test2 ON CONFLICT (a) DO UPDATE SET
       (a,c,b) = ROW (excluded.*);
       -- Data dalam tabel test1 setelah pembaruan:       
       a	b	c
       1	6	5

• Optimalisasi untuk pernyataan INSERT ON CONFLICT pada tabel berorientasi baris:

  Hologres mengoptimalkan pembaruan untuk tabel berorientasi baris. Untuk kinerja terbaik, pertahankan urutan kolom dalam klausa UPDATE konsisten dengan urutan dalam klausa INSERT dan lakukan pembaruan seluruh baris.

  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.*)

Error umum