Cara menimpa data menggunakan INSERT ON CONFLICT - AnalyticDB

Untuk menghindari kegagalan penulisan akibat konflik kunci utama atau pelanggaran batasan unik selama sinkronisasi data atau impor massal, Anda dapat menggunakan INSERT ON CONFLICT. Ini melakukan operasi UPSERT (update atau insert) standar, mirip dengan REPLACE INTO di MySQL. Topik ini menjelaskan cara menggunakan INSERT ON CONFLICT, mendetail sintaksisnya, dan memberikan contoh penggunaan.

Sebelum memulai

Perhatikan hal-hal berikut:

Versi Instans: INSERT ON CONFLICT pada tabel partisi hanya didukung pada AnalyticDB for PostgreSQL instans dengan versi mesin minor V6.3.6.1 atau lebih baru.
Jenis Tabel yang Didukung: tabel heap (berorientasi baris) dan tabel yang menggunakan Mesin Penyimpanan Beam.
Jenis Tabel yang Tidak Didukung: tabel berorientasi kolom (AO/AOCS) (karena mereka tidak mendukung indeks unik).
Konflik dalam Pernyataan yang Sama: Anda tidak dapat menyisipkan beberapa baris yang bertentangan pada kunci utama yang sama dalam satu pernyataan INSERT, misalnya mencoba menyisipkan kunci utama 1 dua kali dalam satu perintah. Ini adalah batasan SQL standar.

Sintaksis SQL

Klausa opsional ON CONFLICT menentukan tindakan alternatif selain menimbulkan kesalahan pelanggaran unik.

Sintaksis dasar

INSERT INTO nama_tabel (kolom1, kolom2, ...)
VALUES (nilai1, nilai2, ...)
ON CONFLICT [ target_konflik ] aksi_konflik

Sintaksis lengkap

[ WITH [ RECURSIVE ] kueri_dengan [, ...] ]
INSERT INTO nama_tabel [ AS alias ] [ ( nama_kolom [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { ekspresi | DEFAULT } [, ...] ) [, ...] | kueri }
    [ ON CONFLICT [ target_konflik ] aksi_konflik ]
    [ RETURNING * | ekspresi_keluaran [ [ AS ] nama_keluaran ] [, ...] ]
    
di mana target_konflik bisa salah satu dari:
    ( { nama_kolom_indeks | ( ekspresi_indeks ) } [ COLLATE aturan_pengurutan ] [ opclass ] [, ...] )
     ON CONSTRAINT nama_pembatasan
    
dan aksi_konflik adalah salah satu dari:
    DO NOTHING
    DO UPDATE SET { nama_kolom = { ekspresi | DEFAULT } |
                    ( nama_kolom [, ...] ) = ( { ekspresi | DEFAULT } [, ...] )
                  } [, ...]
              [ WHERE kondisi ]

ON CONFLICT memungkinkan Anda memperbarui database. Ini terdiri dari target_konflik dan aksi_konflik, dirinci dalam tabel di bawah ini:

Parameter

Deskripsi

target_konflik

Menentukan bagaimana mengidentifikasi konflik.

Ini memberitahu database indeks unik mana yang digunakan berdasarkan nama_kolom_indeks yang Anda berikan. Kesalahan akan muncul jika tidak ada indeks unik yang cocok ditemukan.

Diperlukan jika Anda mengatur aksi_konflik ke DO UPDATE. Anda harus menentukan kunci utama atau kolom indeks unik untuk mendefinisikan konflik. Contohnya, a adalah kunci utama:
```
ON CONFLICT (a) 
DO UPDATE
```
Opsional jika itu DO NOTHING.
- Jika ditentukan, tindakan hanya berlaku untuk konflik pada indeks tertentu tersebut.
- Jika dihilangkan, tindakan berlaku untuk konflik dengan batasan unik apa pun yang dapat digunakan.
```
ON CONFLICT DO NOTHING
```

aksi_konflik

Menentukan apa yang harus dilakukan ketika konflik terjadi, diidentifikasi oleh target_konflik:

DO NOTHING: secara diam-diam menghindari penyisipan baris.
DO UPDATE: memperbarui baris yang ada yang bertentangan dengan baris yang diusulkan. Klausa SET dan WHERE memiliki akses ke:
- Nilai baris yang ada, menggunakan nama tabel (misalnya, t1.kolom).
- Nilai baris yang diusulkan, menggunakan pseudo-tabel khusus excluded (misalnya, excluded.kolom).

Penting

DO UPDATE keterbatasan:

Anda tidak dapat memperbarui kunci distribusi atau kolom kunci utama.
Subkueri tidak didukung dalam klausa WHERE.
Pembaruan kolom parsial tidak didukung untuk Mesin penyimpanan Beam. Gunakan DO UPDATE ALL untuk pembaruan kolom penuh sebagai gantinya.

Cara menggunakan

1. Persiapkan data

Buat tabel sampel dan sisipkan beberapa data awal. Kolom a adalah kunci utama.

CREATE TABLE t1 (
  a int PRIMARY KEY,
  b int,
  c int,
  d int DEFAULT 0
);

-- Sisipkan baris awal
INSERT INTO t1 VALUES (0, 0, 0, 0);

-- Konfirmasi data
SELECT * FROM t1;

Hasil:

 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
(1 baris)

(Opsional) Jika Anda menggunakan Mesin Penyimpanan Beam dan perlu memperbarui kolom parsial saat konflik, ubah metode akses tabel menjadi heap.

ALTER TABLE t1 SET ACCESS METHOD heap;

2. Demo konflik penyisipan standar

Mencoba INSERT biasa dengan kunci utama yang sudah ada akan menyebabkan kesalahan.

INSERT INTO t1 VALUES (0,1,1,1);

ERROR:  nilai kunci duplikat melanggar batasan unik "t1_pkey"
DETAIL:  Kunci (a)=(0) sudah ada.

Kasus 1. Tangani konflik pada satu baris

Untuk mencegah kesalahan di atas, gunakan ON CONFLICT.

Tidak memperbarui (`DO NOTHING`)

Tindakan ini cukup membuang baris baru jika kuncinya sudah ada. Data asli tetap tidak berubah.

Sisipkan beberapa data.

INSERT INTO t1 
VALUES (0,1,1,1) 
ON CONFLICT DO NOTHING;

Kueri hasilnya.

SELECT * FROM t1;

Data tidak berubah.

 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
(1 baris)

Perbarui semua (`DO UPDATE`)

Tindakan ini memperbarui baris yang ada dengan nilai baru.

Sisipkan beberapa data.

Dalam klausa DO UPDATE SET, pseudo-tabel khusus excluded berisi nilai dari baris penyisipan yang diusulkan. Anda dapat merujuk nilainya untuk memperbarui baris yang ada.

INSERT INTO t1 
VALUES (0,1,1,1) 
ON CONFLICT (a) 
DO UPDATE SET 
  (b, c, d) = (excluded.b, excluded.c, excluded.d);

Atau, jalankan yang berikut ini:

INSERT INTO t1 
VALUES (0,1,1,1) 
ON CONFLICT (a) 
DO UPDATE SET 
  b = excluded.b, c = excluded.c, d = excluded.d;

Kueri hasilnya.

SELECT * FROM t1;

Kolom non-kunci utama (b, c, dan d) diperbarui.

 a | b | c | d
---+---+---+---
 0 | 1 | 1 | 1
(1 baris)

Pembaruan parsial

Anda juga dapat memperbarui hanya kolom tertentu atau memperbarui kolom berdasarkan nilainya yang ada.

Perbarui hanya kolom c dengan nilai baru dari baris excluded.

-- Pertama, pastikan baris memiliki nilai yang diketahui untuk contoh ini
UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

-- Sekarang, sisipkan dengan konflik, memperbarui hanya kolom c
INSERT INTO t1 VALUES (0, 3, 3, 3) ON CONFLICT (a) DO UPDATE SET
  c = excluded.c;

Kueri hasilnya.

SELECT * FROM t1;

Hanya c yang berubah menjadi 3.

 a | b | c | d
---+---+---+---
 0 | 1 | 3 | 1
(1 baris)

Perbarui kolom berdasarkan nilainya yang asli, misalnya tambahkan c.

INSERT INTO t1 
VALUES (0,0,1,0) 
ON CONFLICT (a) 
DO UPDATE SET 
  c = t1.c + 1;

Kueri hasilnya.

SELECT * FROM t1;

c sekarang 4.

 a | b | c | d
---+---+---+---
 0 | 1 | 4 | 1
(1 baris)

Perbarui ke default

Anda dapat memperbarui kolom ke nilai defaultnya.

Sisipkan konflik kunci utama dan perbarui d ke defaultnya.

-- Pertama, pastikan baris memiliki nilai yang diketahui untuk contoh ini
UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

-- Sekarang, sisipkan dengan konflik, menyetel kolom d ke defaultnya
INSERT INTO t1 VALUES (0, 0, 2, 2) ON CONFLICT (a) DO UPDATE SET
  d = DEFAULT;

Kueri hasilnya.
```
SELECT * FROM t1;
```

d dikembalikan ke nilai defaultnya 0.

 a | b | c | d
---+---+---+---
 0 | 1 | 1 | 0
(1 baris)

Kasus 2. Tangani konflik dengan beberapa baris

Contoh ini menunjukkan bagaimana ON CONFLICT berperilaku saat menyisipkan beberapa baris sekaligus, di mana beberapa baris bertentangan dan lainnya tidak.

Penyisipan multi-baris

Kasus 1: `DO NOTHING` dengan beberapa baris

Baris yang bertentangan diabaikan, dan baris yang tidak bertentangan disisipkan.

Perbarui dan sisipkan beberapa data.

-- Reset data untuk contoh ini
UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

-- Sisipkan satu baris yang bertentangan (a=0) dan satu baris baru (a=3)
INSERT INTO t1 VALUES (0, 2, 2, 2), (3, 3, 3, 3) ON CONFLICT DO NOTHING;

Kueri hasilnya.
```
SELECT * FROM t1;
```

Baris a=0 diabaikan, baris a=4 ditambahkan.

 a | b | c | d
---+---+---+---
 3 | 3 | 3 | 3
 0 | 1 | 1 | 1
(2 baris)

Kasus 2: `DO UPDATE` dengan beberapa baris

Baris yang bertentangan diperbarui, dan baris yang tidak bertentangan disisipkan.

Sisipkan beberapa data.

INSERT INTO t1 
VALUES (0,0,0,0), (4,4,4,4) 
ON CONFLICT (a) 
DO UPDATE SET 
  (b, c, d) = (excluded.b, excluded.c, excluded.d);

Kueri hasilnya.
```
SELECT * FROM t1;
```

Baris a=0 diperbarui, dan baris a=4 ditambahkan.

 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
 3 | 3 | 3 | 3
 4 | 4 | 4 | 4
(3 baris)

Penyisipan multi-baris dari subkueri

Anda juga dapat menggunakan ON CONFLICT dengan pernyataan INSERT INTO ... SELECT. Ini adalah metode yang kuat untuk menggabungkan tabel.

Persiapkan tabel.

-- Reset tabel t1
DELETE FROM t1;
INSERT INTO t1 VALUES (0, 1, 1, 1);

-- Buat dan isi tabel t2
CREATE TABLE t2 (LIKE t1);
INSERT INTO t2 VALUES (0, 11, 11, 11), (2, 22, 22, 22);

Sisipkan dari t2 ke t1 dengan penanganan konflik.

INSERT INTO t1 
SELECT * FROM t2 
ON CONFLICT (a) 
DO UPDATE SET 
  (b, c, d) = (excluded.b, excluded.c, excluded.d);

Kueri hasilnya.

SELECT * FROM t1;

Baris a=0 diperbarui, dan baris a=2 disisipkan.

 a | b  | c  | d
---+----+----+----
 2 | 22 | 22 | 22
 0 | 11 | 11 | 11
(4 baris)

Kasus 3. Pembaruan penuh kolom pada tabel Beam

Jika instans Anda menggunakan mesin penyimpanan Beam, gunakan INSERT ON CONFLICT DO UPDATE ALL untuk memperbarui semua kolom saat terjadi konflik.

Buat tabel Beam.

CREATE TABLE beam_test (
  a int PRIMARY KEY, 
  b int, 
  c int, 
  d int DEFAULT 0
) USING  beam;

Masukkan beberapa data uji.

INSERT INTO beam_test 
VALUES
  (0, 0, 0, 0),
  (1, 1, 1, 1),
  (2, 2, 2, 2);

Periksa hasilnya.

SELECT * FROM beam_test;

3 baris dimasukkan.

 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
 1 | 1 | 1 | 1
 2 | 2 | 2 | 2
(3 baris)

Lakukan pembaruan penuh kolom.

INSERT INTO beam_test 
VALUES(0, 4, 4, 4) 
ON CONFLICT (a)  
DO UPDATE ALL;

Periksa hasilnya.

SELECT * FROM beam_test;

Semua kolom diperbarui.

 a | b | c | d
---+---+---+---
 0 | 4 | 4 | 4
 1 | 1 | 1 | 1
 2 | 2 | 2 | 2
(3 baris)

Pertanyaan yang Sering Diajukan

Bagaimana cara saya menanyakan mesin penyimpanan dari sebuah tabel?

Jalankan SQL berikut, ganti schemaname.tablename dengan nama tabel Anda:

SELECT
c.oid::regclass AS rel,
coalesce(a.amname, 'heap') AS table_am
FROM pg_class c
LEFT JOIN pg_am a ON a.oid = c.relam
WHERE c.oid = 'schamename.tablename'::regclass
AND c.relkind = 'r';

Bagaimana cara memperbaiki kesalahan dari pembaruan sebagian pada tabel Beam?

Anda menerima kesalahan berikut:

ERROR:  INSERT ON CONFLICT DO UPDATE SET tidak didukung untuk relasi beam
HINT:  Silakan gunakan INSERT INTO table VALUES(?,?,...) ON CONFLICT DO UPDATE ALL.

Langkah-langkah untuk memperbaiki:

Mesin penyimpanan Beam tidak mendukung pembaruan kolom sebagian. Anda memiliki dua opsi:

Lakukan pembaruan penuh kolom menggunakan INSERT ... ON CONFLICT DO UPDATE ALL.
Ubah metode penyimpanan tabel menjadi heap melalui ALTER TABLE your_table_name SET ACCESS METHOD heap;.

Sebelum memulai

Sintaksis SQL

Sintaksis dasar

Sintaksis lengkap

Cara menggunakan

1. Persiapkan data

2. Demo konflik penyisipan standar

Kasus 1. Tangani konflik pada satu baris

Tidak memperbarui (DO NOTHING)

Perbarui semua (DO UPDATE)

Pembaruan parsial

Perbarui ke default

Kasus 2. Tangani konflik dengan beberapa baris

Penyisipan multi-baris

Kasus 1: DO NOTHING dengan beberapa baris

Kasus 2: DO UPDATE dengan beberapa baris

Penyisipan multi-baris dari subkueri

Kasus 3. Pembaruan penuh kolom pada tabel Beam

Pertanyaan yang Sering Diajukan

Bagaimana cara saya menanyakan mesin penyimpanan dari sebuah tabel?

Bagaimana cara memperbaiki kesalahan dari pembaruan sebagian pada tabel Beam?

Tidak memperbarui (`DO NOTHING`)

Perbarui semua (`DO UPDATE`)

Kasus 1: `DO NOTHING` dengan beberapa baris

Kasus 2: `DO UPDATE` dengan beberapa baris