ON CONFLICT

Aplikasi nyata penuh tabrakan data yang justru wajar: job sinkronisasi berjalan ulang setelah gagal di tengah jalan, pengguna menekan tombol simpan dua kali, file impor yang sama dikirim berulang oleh sistem hulu. Tanpa perlakuan khusus, setiap kejadian itu berujung pada error duplicate key value violates unique constraint — dan seluruh batch dibatalkan. Klausa ON CONFLICT milik PostgreSQL mengubah tabrakan dari bencana menjadi cabang logika: baris yang bentrok bisa diam-diam dilewati (DO NOTHING) atau diperbarui dengan data yang baru datang (DO UPDATE) — pola terakhir inilah yang dikenal sebagai upsert.

Menyiapkan Tabel Latihan #

Kembali ke tabel produk dengan tiga baris awal — cukup untuk menjadi sasaran tabrakan:

CREATE TABLE produk (
    id       integer PRIMARY KEY,
    nama     text NOT NULL,
    kategori text NOT NULL,
    harga    numeric(10, 2) NOT NULL CHECK (harga > 0),
    stok     integer NOT NULL DEFAULT 0
);

INSERT INTO produk (id, nama, kategori, harga, stok) VALUES
    (1, 'Buku Tulis', 'alat tulis', 4500, 120),
    (2, 'Pulpen Hitam', 'alat tulis', 3000, 200),
    (3, 'Kertas HVS A4', 'kertas', 55000, 40);

SELECT id, nama, kategori, harga, stok FROM produk ORDER BY id;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 3
 id |     nama      |  kategori  |  harga   | stok 
----+---------------+------------+----------+------
  1 | Buku Tulis    | alat tulis |  4500.00 |  120
  2 | Pulpen Hitam  | alat tulis |  3000.00 |  200
  3 | Kertas HVS A4 | kertas     | 55000.00 |   40
(3 rows)

Primary key pada kolom id adalah magnet tabrakan: satu id hanya boleh dimiliki satu baris. Semua contoh berikut mengadu baris baru bertanda id yang sudah dipakai baris lama.

Upsert dengan DO UPDATE #

Skenario klasiknya: file harga terbaru dari supplier berisi Buku Tulis (id 1, sudah ada di tabel — harganya perlu diperbarui) dan Map Plastik (id 4, belum ada — perlu disisipkan). Satu perintah INSERT ... ON CONFLICT DO UPDATE menangani keduanya:

INSERT INTO produk (id, nama, kategori, harga, stok) VALUES
    (1, 'Buku Tulis', 'alat tulis', 5000, 50),
    (4, 'Map Plastik', 'alat tulis', 12000, 25)
ON CONFLICT (id) DO UPDATE
SET nama = EXCLUDED.nama,
    harga = EXCLUDED.harga,
    stok = produk.stok + EXCLUDED.stok;

SELECT id, nama, kategori, harga, stok FROM produk ORDER BY id;

Output yang Dihasilkan:

INSERT 0 2
 id |     nama      |  kategori  |  harga   | stok 
----+---------------+------------+----------+------
  1 | Buku Tulis    | alat tulis |  5000.00 |  170
  2 | Pulpen Hitam  | alat tulis |  3000.00 |  200
  3 | Kertas HVS A4 | kertas     | 55000.00 |   40
  4 | Map Plastik   | alat tulis | 12000.00 |   25
(4 rows)

Mari kita bedah per bagian — terutama referensi EXCLUDED yang menjadi jantung upsert:

1. ON CONFLICT (id) menyatakan konflik mana yang ditangani: pelanggaran uniqueness pada kolom id. PostgreSQL menuntut kolom ini dilindungi unique index atau primary key; tanpa itu, perintah ditolak karena server tidak tahu tabrakan mana yang dimaksud.
2. DO UPDATE SET ... menentukan aksi ketika konflik terjadi: baris lama tidak dihapus, melainkan diperbarui sesuai daftar SET — seperti UPDATE kecil yang menempel pada INSERT.
3. EXCLUDED.nama, EXCLUDED.harga merujuk pada nilai dari baris yang gagal masuk — baris baru itu “dikeluarkan” (excluded) dari INSERT dan nilainya bisa dipakai untuk memperbarui baris lama. Tanpa kata EXCLUDED, nama kolom mengacu ke baris lama yang sudah ada.
4. produk.stok + EXCLUDED.stok memperlihatkan kedua dunia sekaligus: stok lama (produk.stok, dari baris yang ada) dijumlahkan dengan stok yang dibawa data baru (EXCLUDED.stok). Hasilnya di output terlihat jelas — Buku Tulis mendapat harga baru 5000.00 dan stok berubah dari 120 menjadi 170 (bukan ditimpa 50), sementara Map Plastik masuk sebagai baris baru dengan stok 25.

Tag INSERT 0 2 menghitung kedua jenis hasil sekaligus: satu baris disisipkan, satu baris diperbarui. Di aplikasi, kombinasi RETURNING (chapter sebelumnya) dan ON CONFLICT saling melengkapi — RETURNING memperlihatkan keadaan akhir baris, baik yang baru dibuat maupun yang baru diperbarui.

DO NOTHING dan Inferensi Target #

DO NOTHING adalah bentuk paling sederhana: baris yang bentrok dilewati tanpa error, sisanya tetap diproses. Bentuk ini punya satu keistimewaan sintaks — target konflik boleh tidak ditulis sama sekali:

INSERT INTO produk (id, nama, kategori, harga) VALUES
    (2, 'Pulpen Hitam', 'alat tulis', 3000)
ON CONFLICT DO NOTHING;

INSERT INTO produk (id, nama, kategori, harga) VALUES
    (3, 'Kertas HVS A4', 'kertas', 55000)
ON CONFLICT (id) DO NOTHING;

SELECT count(*) AS total_produk FROM produk;

Output yang Dihasilkan:

INSERT 0 0
INSERT 0 0
 total_produk 
--------------
            4
(1 row)

Kedua perintah sama-sama berakhir dengan tag INSERT 0 0 — nol baris masuk, nol error. Versi tanpa target (ON CONFLICT DO NOTHING) menangkap pelanggaran unique constraint mana pun pada tabel; versi dengan target (ON CONFLICT (id)) hanya menangkap pelanggaran pada primary key. Asimetri ini disebut inference: DO NOTHING boleh bekerja tanpa menebak konflik mana, sedangkan DO UPDATE wajib tahu persis kolom mana yang jadi arena — mencoba ON CONFLICT DO UPDATE tanpa target langsung ditolak sebagai syntax error, karena server tidak berani memperbarui baris tanpa tumbuhan konflik yang jelas.

Target Konflik dari Partial Index #

Bentuk target yang jarang diketahui: unique index bisa bersifat parsial — hanya melindungi baris yang memenuhi kondisi tertentu — dan ON CONFLICT harus menuliskan ulang kondisi itu agar cocok:

CREATE UNIQUE INDEX harga_unik_kertas
ON produk (nama)
WHERE kategori = 'kertas';

INSERT INTO produk (id, nama, kategori, harga, stok) VALUES
    (5, 'Kertas Folio', 'kertas', 20000, 30);

INSERT INTO produk (id, nama, kategori, harga, stok) VALUES
    (6, 'Kertas Folio', 'kertas', 18000, 10)
ON CONFLICT (nama) WHERE kategori = 'kertas'
DO UPDATE SET harga = EXCLUDED.harga;

SELECT id, nama, kategori, harga, stok FROM produk ORDER BY id;

Output yang Dihasilkan:

CREATE INDEX
INSERT 0 1
INSERT 0 1
 id |     nama      |  kategori  |  harga   | stok 
----+---------------+------------+----------+------
  1 | Buku Tulis    | alat tulis |  5000.00 |  170
  2 | Pulpen Hitam  | alat tulis |  3000.00 |  200
  3 | Kertas HVS A4 | kertas     | 55000.00 |   40
  4 | Map Plastik   | alat tulis | 12000.00 |   25
  5 | Kertas Folio  | kertas     | 18000.00 |   30
(5 rows)

Index harga_unik_kertas hanya mengindeks produk kertas, jadi nama Kertas Folio tidak boleh kembar di kategori itu — tetapi nama kembar pada kategori lain tetap sah. Perhatikan dua hal pada hasilnya: perintah kedua bentrok dengan Kertas Folio yang sudah ada (id 5) dan memperbarui harganya menjadi 18000.00, dan targetnya ditulis (nama) WHERE kategori = 'kertas' — persis meniru predikat index-nya; menulis target tanpa klausa WHERE membuat PostgreSQL gagal menemukan index yang cocok dan menolak perintahnya. Kasus pemakaian nyatanya biasanya seputar uniqueness bersyarat, misalnya “satu akun aktif per email”.

Jebakan: Dua Baris Baru Bertabrakan di Perintah yang Sama #

ON CONFLICT menangani bentrok antara baris baru dan baris lama — tetapi tidak bisa menangani dua baris baru yang saling bentrok dalam satu perintah. Batch berikut membawa dua baris dengan id = 1:

INSERT INTO produk (id, nama, kategori, harga) VALUES
    (1, 'Buku Tulis Edisi A', 'alat tulis', 4600),
    (1, 'Buku Tulis Edisi B', 'alat tulis', 4700)
ON CONFLICT (id) DO UPDATE SET harga = EXCLUDED.harga;

Output yang Dihasilkan:

psql:konflik-ganda-error.sql:4: ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Error cannot affect row a second time terjadi karena urutan kejadian internal: baris pertama bentrok dengan baris lama dan meng-UPDATE-nya; baris kedua lalu mencoba meng-UPDATE baris yang sama lagi — satu baris tidak boleh tersentuh dua kali oleh satu perintah DML, dan PostgreSQL memilih menolak seluruh perintah alih-alih menebak baris mana yang menang. Solusinya ada di hulu data: deduplikasi batch sebelum dikirim (DISTINCT ON di query sumber, atau pembersihan di sisi aplikasi). Perhatikan juga bahwa pesan errornya justru memberi petunjuk solusi langsung di baris HINT.

Kapan ON CONFLICT Dipakai #

Batas ON CONFLICT yang perlu diingat: klausul ini hidup menempel pada INSERT — satu sumber data, satu tabel tujuan, dua cabang aksi (masuk atau perbarui). Ketika kebutuhan tumbuh menjadi tiga cabang (masuk, perbarui, dan hapus), atau sumbernya bukan sekadar daftar nilai, SQL standar punya perintah yang lebih lengkap: MERGE, chapter berikutnya.

Referensi #

• INSERT Reference
• CREATE INDEX Reference

CREATE TABLE produk (
    id       integer PRIMARY KEY,
    nama     text NOT NULL,
    kategori text NOT NULL,
    harga    numeric(10, 2) NOT NULL CHECK (harga > 0),
    stok     integer NOT NULL DEFAULT 0
);

INSERT INTO produk (id, nama, kategori, harga, stok) VALUES
    (1, 'Buku Tulis', 'alat tulis', 4500, 120),
    (2, 'Pulpen Hitam', 'alat tulis', 3000, 200),
    (3, 'Kertas HVS A4', 'kertas', 55000, 40);

SELECT id, nama, kategori, harga, stok FROM produk ORDER BY id;

INSERT INTO produk (id, nama, kategori, harga) VALUES
    (2, 'Pulpen Hitam', 'alat tulis', 3000)
ON CONFLICT DO NOTHING;

INSERT INTO produk (id, nama, kategori, harga) VALUES
    (3, 'Kertas HVS A4', 'kertas', 55000)
ON CONFLICT (id) DO NOTHING;

SELECT count(*) AS total_produk FROM produk;

INSERT INTO produk (id, nama, kategori, harga, stok) VALUES
    (1, 'Buku Tulis', 'alat tulis', 5000, 50),
    (4, 'Map Plastik', 'alat tulis', 12000, 25)
ON CONFLICT (id) DO UPDATE
SET nama = EXCLUDED.nama,
    harga = EXCLUDED.harga,
    stok = produk.stok + EXCLUDED.stok;

SELECT id, nama, kategori, harga, stok FROM produk ORDER BY id;

INSERT INTO produk (id, nama, kategori, harga) VALUES
    (1, 'Buku Tulis Edisi A', 'alat tulis', 4600),
    (1, 'Buku Tulis Edisi B', 'alat tulis', 4700)
ON CONFLICT (id) DO UPDATE SET harga = EXCLUDED.harga;

CREATE UNIQUE INDEX harga_unik_kertas
ON produk (nama)
WHERE kategori = 'kertas';

INSERT INTO produk (id, nama, kategori, harga, stok) VALUES
    (5, 'Kertas Folio', 'kertas', 20000, 30);

INSERT INTO produk (id, nama, kategori, harga, stok) VALUES
    (6, 'Kertas Folio', 'kertas', 18000, 10)
ON CONFLICT (nama) WHERE kategori = 'kertas'
DO UPDATE SET harga = EXCLUDED.harga;

SELECT id, nama, kategori, harga, stok FROM produk ORDER BY id;