PostgreSQL tidak punya kata kunci UPSERT. Kamu menulis INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column. Saat baris baru akan melanggar constraint unik pada kolom itu, PostgreSQL memperbarui baris yang ada sebagai gantinya. DO NOTHING melewatinya.
A-100 sudah ada, jadi harganya berubah menjadi 12.5. B-200 masih baru dan dimasukkan. Satu pernyataan menangani keduanya, dan tetap benar saat sesi lain memasukkan kunci yang sama secara bersamaan.
Insert atau update satu baris
Pernyataannya punya tiga bagian:
INSERT INTO table_name (columns) VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = value [, ...]
[WHERE condition];
- Target konflik menyebut kolom dari unique index atau constraint, seperti primary key. Target ini harus sama persis dengan salah satunya, atau PostgreSQL memunculkan "there is no unique or exclusion constraint matching the ON CONFLICT specification". Kamu juga bisa menyebut nama constraint-nya:
ON CONFLICT ON CONSTRAINT products_pkey. EXCLUDEDadalah baris yang coba kamu masukkan.EXCLUDED.priceadalah harga baru, danproducts.priceadalah harga yang sudah tersimpan.DO UPDATE SEThanya mengubah kolom yang kamu sebutkan. Kolom lain di baris yang ada tetap memakai nilainya.
Unique index dengan klausa WHERE (partial index) juga bisa menjadi target, asalkan kamu mengulang kondisinya: ON CONFLICT (email) WHERE deleted_at IS NULL.
Melewati duplikat dengan DO NOTHING
DO NOTHING memasukkan baris yang baru dan membuang yang bertabrakan, tanpa error. Tanpa target konflik, ia melewati baris yang melanggar constraint unik apa pun.
RETURNING hanya menampilkan lin@example.com. Baris yang dilewati tidak dikembalikan, jadi DO NOTHING ... RETURNING menunjukkan baris mana yang baru.
Menambah counter
Untuk menaikkan nilai yang tersimpan alih-alih menggantinya, tambahkan EXCLUDED ke nilai saat ini. Tulis nilai saat ini lengkap dengan nama tabelnya. Di dalam DO UPDATE, views saja cocok dengan tabel maupun EXCLUDED, dan PostgreSQL berhenti dengan "column reference "views" is ambiguous".
Pernyataan pertama memasukkan baris dengan nilai 1 dan dua pernyataan berikutnya menambahkannya, jadi /home berakhir di 3.
Update hanya saat nilainya berubah
WHERE setelah DO UPDATE SET menentukan, per baris, apakah update dijalankan. Saat bernilai false, baris dibiarkan apa adanya dan tidak dihitung sebagai dimasukkan maupun diperbarui. Pakai untuk menyimpan hanya data terbaru, atau untuk menghindari penulisan versi baris baru saat tidak ada yang berubah.
A-100 tetap 10 karena baris yang masuk lebih lama. B-200 menjadi 25. Untuk melewati update yang akan menulis nilai yang sama, pakai WHERE prices.price IS DISTINCT FROM EXCLUDED.price, yang juga menangani NULL.
Membedakan baris yang dimasukkan dan yang diperbarui
RETURNING berjalan pada upsert seperti pada insert biasa. Untuk membedakan kedua kasus, baca kolom sistem xmax, yang bernilai 0 untuk baris yang dimasukkan oleh pernyataan ini:
A-100 kembali dengan 8 dan inserted false, C-300 dengan 7 dan true. xmax adalah detail internal, bukan API yang didokumentasikan, jadi anggap saja sebagai kemudahan. Halaman RETURNING membahas klausa ini selengkapnya.
Error yang akan kamu temui
- "ON CONFLICT DO UPDATE command cannot affect row a second time": kunci yang sama muncul dua kali di
VALUESatauSELECTdalam satu pernyataan. Hapus duplikatnya dulu, misalnya denganSELECT DISTINCT ON (sku) .... - "column reference is ambiguous": tulis kolomnya lengkap dengan nama tabel di ekspresi
SETdanWHERE, seperti di atas. - Celah di id identity atau serial: insert yang berubah menjadi update sudah mengambil nilai dari sequence, jadi id melompati beberapa angka. Itu wajar.
MERGE (PostgreSQL 15 ke atas) juga bisa melakukan insert atau update, tapi tidak melindungi dari dua sesi yang memasukkan kunci yang sama secara bersamaan. Untuk upsert berdasarkan kunci, pakai ON CONFLICT. Referensi resmi INSERT memuat setiap opsinya.
Pertanyaan yang sering diajukan
Apakah PostgreSQL punya pernyataan UPSERT?
Tidak ada kata kunci UPSERT. PostgreSQL 9.5 menambahkan INSERT ... ON CONFLICT, yang memasukkan baris atau, saat insert itu akan melanggar constraint unik, memperbarui baris yang ada (DO UPDATE) atau melewatinya (DO NOTHING).
Apakah INSERT ... ON CONFLICT aman untuk insert yang bersamaan?
Aman. Setiap baris yang diajukan berakhir sebagai insert atau update, bahkan saat sesi lain memasukkan kunci yang sama di saat yang sama. SELECT yang diikuti INSERT atau UPDATE di kode aplikasi tidak memberi jaminan itu.
Sebaiknya memakai MERGE atau ON CONFLICT untuk upsert?
Pakai ON CONFLICT untuk insert-atau-update berdasarkan kunci. MERGE (PostgreSQL 15 ke atas) menangani lebih banyak kasus, seperti menghapus baris yang cocok. Saat ada insert bersamaan dengan kunci yang sama, MERGE bisa gagal dengan unique violation, yang dihindari oleh ON CONFLICT.
Kenapa muncul "there is no unique or exclusion constraint matching the ON CONFLICT specification"?
Kolom di ON CONFLICT (...) harus sama persis dengan unique index atau constraint. Tambahkan constraint UNIQUE atau unique index pada kolom itu, atau sebutkan kolom dari index yang sudah ada.