KAL
Menu

UPSERT

Menangani konflik kunci dengan ON CONFLICT DO NOTHING dan DO UPDATE.

Daftar isi

Topik ini membahas upsert, yaitu pola penyisipan yang tahu dirinya mungkin bertabrakan dengan data yang sudah ada, dan menyelesaikan tabrakan itu dengan satu pernyataan melalui ON CONFLICT. Pola ini menggantikan urutan cek-dulu-lalu-tulis yang rapuh terhadap balapan antar proses.

Blok penyiapan dataset#

bash 
# siapkan ulang tabel dan data contoh untuk topik ini
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS peminjaman, buku, anggota, kategori CASCADE;
CREATE TABLE kategori (id SERIAL PRIMARY KEY, nama TEXT NOT NULL UNIQUE);
CREATE TABLE anggota (id SERIAL PRIMARY KEY, nama TEXT NOT NULL, email TEXT UNIQUE, bergabung DATE NOT NULL, aktif BOOLEAN NOT NULL DEFAULT TRUE);
CREATE TABLE buku (id SERIAL PRIMARY KEY, judul TEXT NOT NULL, penulis TEXT NOT NULL, tahun INTEGER NOT NULL, kategori_id INTEGER REFERENCES kategori(id));
CREATE TABLE peminjaman (id SERIAL PRIMARY KEY, anggota_id INTEGER NOT NULL REFERENCES anggota(id), buku_id INTEGER NOT NULL REFERENCES buku(id), tanggal_pinjam DATE NOT NULL, tanggal_kembali DATE);
INSERT INTO kategori (nama) VALUES ('Pemrograman'), ('Sains'), ('Sejarah'), ('Fiksi'), ('Statistika');
INSERT INTO anggota (nama, email, bergabung, aktif) VALUES ('Ali Rahman', '[email protected]', '2024-01-15', TRUE), ('Budi Santoso', '[email protected]', '2024-03-02', TRUE), ('Citra Kirana', '[email protected]', '2024-05-20', FALSE), ('Dewi Lestari', '[email protected]', '2024-08-11', TRUE), ('Eka Pratama', NULL, '2025-01-30', TRUE);
INSERT INTO buku (judul, penulis, tahun, kategori_id) VALUES ('Dasar Basis Data', 'Sari Wulandari', 2021, 1), ('Jaringan Komputer', 'Ahmad Fauzi', 2019, 1), ('Fisika untuk Pemula', 'Rina Marlina', 2020, 2), ('Kimia Organik', 'Rina Marlina', 2022, 2), ('Sejarah Nusantara', 'Bambang Wijaya', 2018, 3), ('Cerita Kota Lama', 'Nur Aisyah', 2023, 4), ('Statistika Terapan', 'Sari Wulandari', 2022, 5), ('Algoritma dan Struktur Data', 'Ahmad Fauzi', 2021, 1);
INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, tanggal_kembali) VALUES (1, 1, '2025-03-01', '2025-03-15'), (1, 7, '2025-03-05', '2025-03-20'), (2, 3, '2025-03-10', NULL), (3, 5, '2025-02-20', '2025-03-01'), (2, 1, '2025-04-02', NULL), (4, 6, '2025-04-07', NULL), (5, 8, '2025-04-09', '2025-04-25'), (4, 2, '2025-05-01', NULL);
SQL

Tabrakan kunci dan pilihan penyelesaiannya#

Penyisipan yang melanggar keunikan gagal dengan error kunci duplikat, sebagaimana dicoba blok berikut pada email anggota yang sudah terpakai.

bash 
# INSERT biasa bertabrakan dengan email yang sudah ada
psql -U postgres -d perpustakaan -c "INSERT INTO anggota (nama, email, bergabung) VALUES ('Ali Rahman', '[email protected]', '2025-06-01');"
text Keluaran 
ERROR:  duplicate key value violates unique constraint "anggota_email_key"
DETAIL:  Key (email)=([email protected]) already exists.

Perilaku bawaan ini benar sebagai penjaga, tetapi banyak beban kerja menginginkan tindakan lain saat bertabrakan, misalnya mengabaikan baris baru atau memperbarui baris lama. Kedua tindakan itu ditulis pada klausa ON CONFLICT, yang menentukan dulu aturan unik mana yang dilihat, lalu tindakan apa yang diambil.

ON CONFLICT DO NOTHING#

Bentuk paling sederhana membiarkan baris yang bertabrakan lewat tanpa error, dan statusnya INSERT 0 0 ketika tidak ada yang masuk. Target konflik dinyatakan agar maksudnya eksplisit, yaitu aturan unik mana yang memicu pengabaian.

bash 
# daftar anggota yang aman diulang
psql -U postgres -d perpustakaan <<'SQL'
INSERT INTO anggota (nama, email, bergabung) VALUES
    ('Ali Rahman', '[email protected]', '2025-06-01'),
    ('Farah Nabila', '[email protected]', '2025-06-02')
ON CONFLICT (email) DO NOTHING;
SELECT nama, email FROM anggota ORDER BY id DESC LIMIT 2;
SQL
text Keluaran 
INSERT 0 1
     nama     |       email
--------------+-------------------
 Farah Nabila | [email protected]
 Eka Pratama  |
(2 rows)

Baris Ali dibatalkan diam-diam, baris Farah masuk, dan pernyataan berakhir sukses dengan status INSERT 0 1. Pola ini cocok untuk impor yang boleh diulang, karena menjalankannya dua kali menghasilkan data yang sama, sifat yang disebut idempoten.

ON CONFLICT DO UPDATE#

Bentuk kedua memperbarui baris yang sudah ada dengan nilai dari usulan penyisipan, yang dirujuk melalui alias khusus bernama excluded. Pola berikut mengelola tabel penghitung sesi baca, yaitu memasukkan baris pertama kali dan menambah hitungan pada kunjungan berikutnya.

bash 
# pola insert-or-increment
psql -U postgres -d perpustakaan <<'SQL'
CREATE TABLE sesi_baca (
    nama    TEXT PRIMARY KEY,
    jumlah  INTEGER NOT NULL DEFAULT 0
);
INSERT INTO sesi_baca (nama, jumlah) VALUES ('buku-1', 1)
ON CONFLICT (nama) DO UPDATE SET jumlah = sesi_baca.jumlah + EXCLUDED.jumlah;
INSERT INTO sesi_baca (nama, jumlah) VALUES ('buku-1', 1)
ON CONFLICT (nama) DO UPDATE SET jumlah = sesi_baca.jumlah + EXCLUDED.jumlah;
SELECT * FROM sesi_baca;
DROP TABLE sesi_baca;
SQL
text Keluaran 
CREATE TABLE
INSERT 0 1
INSERT 0 1
  nama  | jumlah
--------+--------
 buku-1 |      2
(1 row)

DROP TABLE

Kedua pernyataan identik, tetapi hasilnya bertambah menjadi dua, karena pernyataan kedua menabrak baris yang sudah ada dan mengeksekusi DO UPDATE dengan nilai lama ditambah nilai usulan. Kolom tanpa alias pada SET merujuk baris yang sudah ada, sedangkan EXCLUDED membawa nilai yang hendak disisipkan, dan pemisahan dua dunia ini inti dari bentuk DO UPDATE.

Target konflik yang sah#

Target konflik harus cocok dengan constraint unik yang benar-benar ada, baik kolom atau gabungan kolom dengan UNIQUE, maupun PRIMARY KEY. Target yang tidak cocok dengan aturan mana pun ditolak saat pernyataan disusun, dan bentuk ON CONFLICT ON CONSTRAINT nama_constraint merujuk langsung pada namanya, berguna untuk constraint gabungan yang panjang.

Tanpa target, yaitu ON CONFLICT DO NOTHING polos, pernyataan menangkap pelanggaran aturan unik mana pun. Kenyamanan itu membawa kerentanan, karena pelanggaran yang seharusnya diwaspadai ikut dibungkam, sehingga bentuk bertarget menjadi bawaan yang disarankan.

Kesalahan umum#

Kesalahan pertama adalah DO UPDATE tanpa EXCLUDED, misalnya SET jumlah = jumlah yang menugaskan nilai ke dirinya sendiri dan tidak mengubah apa pun, sedangkan maksud pola biasanya menggabungkan nilai lama dengan usulan. Kesalahan kedua adalah target yang tidak cocok dengan constraint, yang error-nya muncul saat penguraian dan diperbaiki dengan menyebut kolom unik yang benar.

Kesalahan ketiga adalah memakai DO NOTHING untuk membungkam seluruh keunikan tanpa target, sehingga tabrakan pada aturan lain yang semestinya memicu perbaikan data ikut lewat tanpa jejak. Kesalahan keempat adalah mengira upsert menangani konflik foreign key, padahal ON CONFLICT hanya untuk kendala unik, dan pelanggaran rujukan tetap error biasa.

Ringkasan#

  • ON CONFLICT menyelesaikan tabrakan kunci dalam satu pernyataan, menggantikan pola cek-dulu-lalu-tulis yang rapuh terhadap balapan.
  • DO NOTHING membuat penyisipan idempoten, dan status INSERT 0 0 menandai baris yang diabaikan.
  • DO UPDATE memperbarui baris lama, dengan kolom tanpa alias merujuk nilai lama dan EXCLUDED merujuk usulan penyisipan.
  • Target konflik disebutkan eksplisit sesuai constraint yang dimaksud, karena bentuk tanpa target membungkam seluruh aturan unik.

Referensi#