Cara menggunakan INSERT ON CONFLICT untuk menimpa data - AnalyticDB

Pernyataan INSERT ON CONFLICT memungkinkan operasi UPSERT—“perbarui jika ada, sisipkan jika tidak”—untuk mencegah kegagalan penulisan akibat konflik kunci primer selama sinkronisasi data atau impor massal. Fitur ini mirip dengan pernyataan REPLACE INTO MySQL. Topik ini menjelaskan sintaks INSERT ON CONFLICT dan menyediakan contoh penggunaannya.

Perhatian

Untuk tabel partisi, pernyataan INSERT ON CONFLICT hanya didukung pada instans AnalyticDB for PostgreSQL yang menjalankan Minor Kernel Version V6.3.6.1 atau lebih baru.
Catatan
Anda dapat melihat versi minor di halaman Basic Information suatu instans di Konsol AnalyticDB for PostgreSQL. Jika instans Anda belum memenuhi versi yang dipersyaratkan, perbarui versi minor instans tersebut.
Fitur ini hanya didukung untuk tabel heap dan tabel Beam. Fitur ini tidak didukung untuk tabel kolom (AO/AOCS), yang tidak mendukung indeks unik. Untuk memeriksa mesin penyimpanan suatu tabel, lihat Bagaimana cara mengetahui mesin penyimpanan yang digunakan oleh suatu tabel?
Satu pernyataan INSERT tidak boleh mencakup beberapa baris dengan kunci primer yang sama. Ini merupakan batasan dari standar SQL.

Sintaks SQL

Sintaks dasar

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT [ conflict_target ] conflict_action

Sintaks lengkap

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
    
where conflict_target can be one of the following:
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] )[ WHERE index_predicate ]
    ON CONSTRAINT constraint_name
    
and conflict_action can be one of the following:
    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
                  } [, ...]
              [ WHERE condition ]

Klausa ON CONFLICT memungkinkan operasi UPSERT. Klausul ini terdiri dari conflict_target dan conflict_action, yang dijelaskan sebagai berikut:

Parameter

Deskripsi

conflict_target

Menentukan target konflik, yang menetapkan apa yang dianggap sebagai konflik.

Jika conflict_action adalah DO UPDATE, conflict_target harus menentukan kunci primer atau indeks unik yang menyebabkan konflik.
Jika conflict_action adalah DO NOTHING, conflict_target dapat diabaikan.

conflict_action

Menentukan aksi yang dilakukan saat terjadi konflik. Nilai yang valid:

DO NOTHING: Mengabaikan baris penyisipan yang diusulkan ketika terjadi konflik pada conflict_target.
DO UPDATE: Memperbarui baris yang sudah ada menggunakan klausa DO UPDATE SET ketika terjadi konflik pada conflict_target. Dalam klausa ini, referensikan data penyisipan yang diusulkan melalui pseudo-tabel excluded.
Penting
- Anda tidak dapat memperbarui kunci distribusi atau kunci primer dalam klausa SET dari aksi UPDATE.
- Klausa WHERE dari aksi UPDATE tidak mendukung subkueri.
- Tabel Beam tidak mendukung pembaruan kolom parsial. Tabel ini hanya mendukung pembaruan seluruh baris menggunakan DO UPDATE ALL.

Contoh penggunaan

Persiapkan data

Buat tabel bernama t1, dengan kolom a sebagai kunci primer:

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

(Opsional) Jika Anda menggunakan mesin penyimpanan Beam dan perlu melakukan pembaruan parsial saat terjadi konflik, ubah metode akses tabel menjadi heap.
```
ALTER TABLE t1 SET ACCESS METHOD heap;
```
Sisipkan satu baris ke dalam tabel t1 dengan kunci primer a bernilai 0:
```
INSERT INTO t1 VALUES (0,0,0,0);
```

Kueri tabel untuk melihat datanya:

SELECT * FROM t1;

Berikut ini adalah contoh hasilnya:

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

Penyisipan standar

Pernyataan INSERT standar yang mencoba menyisipkan baris dengan kunci primer duplikat (a = 0) akan gagal:

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

Operasi tersebut mengembalikan error:

ERROR:  duplicate key value violates unique constraint "t1_pkey"
DETAIL:  Key (a)=(0) already exists.

Upsert satu baris

Untuk menangani konflik kunci primer tanpa mengembalikan error, gunakan klausa ON CONFLICT.

Abaikan penyisipan saat terjadi konflik

Untuk mengabaikan operasi penyisipan saat terjadi konflik kunci primer, gunakan klausa ON CONFLICT DO NOTHING.

Sisipkan data:

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

Lihat data tabel:

SELECT * FROM t1;

Berikut ini adalah contoh hasilnya:

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

Perbarui data saat terjadi konflik

Untuk menimpa data saat terjadi konflik kunci primer, gunakan klausa ON CONFLICT DO UPDATE.

Sisipkan data.
Dalam klausa DO UPDATE SET, pseudo-tabel khusus excluded berisi nilai-nilai dari baris yang diusulkan untuk disisipkan. Anda dapat mereferensikan nilai-nilai ini untuk memperbarui baris yang sudah 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, Anda dapat menentukan setiap kolom secara individual:
```
INSERT INTO t1 
VALUES (0,1,1,1) 
ON CONFLICT (a) 
DO UPDATE SET 
  b = excluded.b, c = excluded.c, d = excluded.d;
```
Lihat data tabel:
```
SELECT * FROM t1;
```
Hasilnya menunjukkan bahwa kolom non-kunci primer pada tabel t1 telah diperbarui:
```
a | b | c | d
---+---+---+---
 0 | 1 | 1 | 1
(1 row)
```

Perbarui kolom parsial saat terjadi konflik

Anda juga dapat memperbarui sebagian kolom atau memperbarui kolom berdasarkan nilai yang sudah ada. Contoh berikut menunjukkan skenario tersebut.

Untuk memverifikasi bahwa logika penanganan konflik berfungsi sebagaimana mestinya, perbarui data uji.
```
UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```
Timpa data asli dengan data yang baru disisipkan.
1. Timpa hanya data pada kolom c. Pernyataan penyisipannya adalah sebagai berikut:
```
INSERT INTO t1 
VALUES (0,3,3,3) 
ON CONFLICT (a) 
DO UPDATE SET 
  c = excluded.c;
```
2. Lihat data tabel:
```
SELECT * FROM t1;
```
  Berikut ini adalah contoh hasilnya:
```
a | b | c | d
---+---+---+---
 0 | 1 | 3 | 1
(1 row)
```
Perbarui berdasarkan data asli.
1. Setelah terjadi konflik kunci primer, tambahkan nilai pada kolom c sebesar 1. Pernyataan penyisipannya adalah sebagai berikut:
```
INSERT INTO t1 
VALUES (0,0,1,0) 
ON CONFLICT (a) 
DO UPDATE SET 
  c = t1.c + 1;
```
2. Lihat data tabel:
```
SELECT * FROM t1;
```
  Berikut ini adalah contoh hasilnya:
```
a | b | c | d
---+---+---+---
 0 | 1 | 4 | 1
(1 row)
```

Perbarui ke nilai default saat terjadi konflik

Saat terjadi konflik kunci primer, Anda dapat memperbarui kolom ke nilai default-nya:

Untuk memverifikasi bahwa logika penanganan konflik berfungsi sebagaimana mestinya, perbarui data uji.
```
UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```
Saat terjadi konflik kunci primer, kembalikan kolom d ke nilai default-nya. Pernyataan penyisipannya adalah sebagai berikut:
```
INSERT INTO t1 
VALUES (0,0,2,2) 
ON CONFLICT (a) 
DO UPDATE SET 
  d = default;
```

Lihat data tabel:

SELECT * FROM t1;

Berikut ini adalah contoh hasilnya:

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

Upsert beberapa baris

Upsert dengan beberapa klausa VALUES

Sisipkan dua baris data. Untuk baris yang mengalami konflik kunci primer, tidak ada aksi yang dilakukan. Untuk baris yang tidak mengalami konflik kunci primer, data disisipkan secara normal.
1. Untuk memverifikasi bahwa logika penanganan konflik berfungsi sebagaimana mestinya, perbarui data uji.
```
UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```
2. Sisipkan data:
```
INSERT INTO t1 
VALUES (0,2,2,2), (3,3,3,3) 
ON CONFLICT DO NOTHING;
```
3. Lihat data tabel:
```
SELECT * FROM t1;
```
  Berikut ini adalah contoh hasilnya:
```
a | b | c | d
---+---+---+---
 3 | 3 | 3 | 3
 0 | 1 | 1 | 1
(2 rows)
```
Sisipkan dua baris data. Untuk baris yang mengalami konflik kunci primer, data ditimpa. Untuk baris yang tidak mengalami konflik kunci primer, data disisipkan secara normal.
1. Sisipkan 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);
```
2. Lihat data tabel:
```
SELECT * FROM t1;
```
  Berikut ini adalah contoh hasilnya:
```
a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
 3 | 3 | 3 | 3
 4 | 4 | 4 | 4
(3 rows)
```

Upsert dari subkueri

Anda juga dapat melakukan upsert data dari subkueri. Jika terjadi konflik, baris yang sudah ada diperbarui. Metode ini berguna untuk menggabungkan tabel atau untuk skenario INSERT INTO SELECT yang kompleks. Berikut ini adalah contohnya.

Untuk memverifikasi bahwa logika penanganan konflik berfungsi sebagaimana mestinya, perbarui data uji.
```
DELETE FROM t1 WHERE a != 0; 

UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```
Buat tabel bernama t2 dengan skema yang sama seperti t1:
```
CREATE TABLE t2 (like t1);
```
Sisipkan dua baris data ke dalam tabel t2. Pernyataan penyisipannya adalah sebagai berikut:
```
INSERT INTO t2 
VALUES (0,11,11,11),(2,22,22,22);
```

Sisipkan data dari t2 ke t1. Saat terjadi konflik kunci primer, kolom non-kunci primer ditimpa:

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

Lihat data tabel:

SELECT * FROM t1;

Berikut ini adalah contoh hasilnya:

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

Pembaruan seluruh baris untuk tabel Beam saat terjadi konflik

Untuk tabel yang menggunakan mesin penyimpanan Beam, Anda harus menggunakan INSERT ON CONFLICT DO UPDATE ALL untuk melakukan pembaruan seluruh baris saat terjadi konflik.

Buat tabel bernama beam_test, dengan kolom a sebagai kunci primer. Pernyataannya adalah sebagai berikut:
```
CREATE TABLE beam_test (
  a int PRIMARY KEY, 
  b int, 
  c int, 
  d int DEFAULT 0
) USING  beam;
```
Sisipkan data ke dalam tabel beam_test. Pernyataan penyisipannya adalah sebagai berikut:
```
INSERT INTO beam_test 
VALUES
  (0, 0, 0, 0),
  (1, 1, 1, 1),
  (2, 2, 2, 2);
```

Lihat data tabel:

SELECT * FROM beam_test;

Berikut ini adalah contoh hasilnya:

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

Sisipkan data yang bertentangan dengan baris yang sudah ada:

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

Lihat data tabel:

SELECT * FROM beam_test;

Contoh hasil berikut menunjukkan bahwa baris yang bertentangan telah diperbarui:

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

FAQ

Bagaimana cara memeriksa mesin penyimpanan suatu tabel?

Anda dapat menentukan mesin penyimpanan suatu tabel dengan mengkueri katalog sistem pg_class dan pg_am. Gunakan pernyataan SQL berikut, ganti schamename.tablename dengan nama lengkap 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 mengatasi error pembaruan kolom parsial pada tabel Beam?

Masalah: Melakukan pembaruan parsial melalui INSERT ON CONFLICT pada tabel Beam gagal dengan error berikut:

ERROR:  INSERT ON CONFLICT DO UPDATE SET is not supported for beam relations
HINT:  Please use INSERT INTO table VALUES(?,?,...) ON CONFLICT DO UPDATE ALL.

Error ini terjadi karena mesin penyimpanan Beam tidak mendukung pembaruan kolom parsial; mesin ini hanya mengizinkan pembaruan seluruh baris.
Solusi: Untuk memperbaikinya, ganti klausa DO UPDATE SET ... Anda dengan DO UPDATE ALL. Hal ini akan memperbarui seluruh baris yang bertentangan dengan nilai dari pernyataan INSERT Anda.