PostgreSQL tidak dapat menggunakan ON CONFLICT DO UPDATE sebagai operasi umum untuk “mencocokkan salah satu kunci unik”. Pilih satu kunci penentu, atau periksa kedua kunci secara eksplisit dalam transaksi dan tolak masukan yang menunjuk ke dua baris berbeda.

Tutorial ini membantu pengembang yang bekerja di Terminal pada Mac menerapkan kebijakan kedua tersebut dengan node-postgres: sisipkan saat tidak ada kunci yang cocok, perbarui saat tepat satu artikel cocok, dan kembalikan konflik eksplisit saat kunci-kuncinya menunjuk ke artikel yang berbeda. Semua baris bersifat sintetis.

Contoh SQL menargetkan PostgreSQL 18, versi yang disediakan oleh dokumentasi terkini yang diambil. Implementasinya menggunakan tabel biasa yang tidak dipartisi dengan dua batasan unik langsung. Periksa versi mayor server Anda sebelum mengadaptasi contoh dari manual versi lain. Dokumentasi INSERT PostgreSQL 18

Mengapa dua batasan unik tidak dapat menjadi satu target konflik

Anggap sebuah artikel memiliki referensi eksternal dan slug. Masing-masing harus unik secara terpisah. Mulailah dengan penyiapan lengkap ini di basis data demonstrasi yang kosong; simpan sebagai setup.sql.

CREATE TABLE articles (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_key text NOT NULL,
    slug text NOT NULL,
    title text NOT NULL,
    CONSTRAINT articles_external_key_key UNIQUE (external_key),
    CONSTRAINT articles_slug_key UNIQUE (slug)
);

INSERT INTO articles (external_key, slug, title)
VALUES
    ('feed-101', 'blue-notes', 'Blue notes'),
    ('feed-202', 'green-notes', 'Green notes');

Deklarasi ini menerapkan dua aturan terpisah. Menggantinya dengan UNIQUE (external_key, slug) akan mengubah model: hanya pasangan tersebut yang harus unik, sehingga kunci eksternal dan slug yang sama dapat digunakan berulang kali dalam kombinasi berbeda. Mempertahankan NOT NULL pada kedua kolom juga menghindari perilaku kunci nullable dalam contoh ini. Batasan PostgreSQL

Untuk DO UPDATE, PostgreSQL mewajibkan target konflik. Daftar kolom menjelaskan kolom-kolom dari indeks unik yang cocok; daftar itu tidak berarti “salah satu kolom”. Karena itu, ON CONFLICT (external_key, slug) tidak dapat menyimpulkan arbiter dari dua batasan terpisah di atas. Menyebutkan nama batasan akan memilih batasan tersebut, dan sintaksnya menyediakan satu klausa ON CONFLICT, bukan rangkaian penangan alternatif. INSERT PostgreSQL: Klausa ON CONFLICT

Masalah yang lebih mendasar adalah menentukan artikel lama mana yang diwakili oleh baris yang masuk. Pertimbangkan nilai masukan feed-101 dan green-notes: kunci eksternal milik artikel biru, sedangkan slug milik artikel hijau. Memperbarui salah satu baris dengan kedua kunci masukan akan berbenturan dengan baris lainnya. Memilih kecocokan pertama akan menyembunyikan keputusan masukan yang belum terselesaikan.

Tentukan tindakan untuk setiap kombinasi kecocokan

Gunakan tabel keputusan berikut sebelum menulis kueri. Ini adalah kebijakan aplikasi yang diusulkan berdasarkan skema ini, bukan perilaku yang otomatis disediakan PostgreSQL.

Kunci eksternal masukan Slug masukan Baris berbeda yang cocok Tindakan dalam tutorial ini
Belum digunakan Belum digunakan 0 Sisipkan artikel
Cocok dengan sebuah artikel Belum digunakan 1 Perbarui artikel tersebut, termasuk slug-nya
Belum digunakan Cocok dengan sebuah artikel 1 Perbarui artikel tersebut, termasuk kunci eksternalnya
Cocok dengan artikel A Cocok dengan artikel A 1 Perbarui artikel A
Cocok dengan artikel A Cocok dengan artikel B 2 Tolak masukan; pertahankan kedua artikel

Kasus satu kecocokan sengaja memungkinkan salah satu kunci mengidentifikasi artikel dan kunci lainnya berubah. Pastikan perilaku ini sesuai dengan kebutuhan impor Anda. Jika kunci eksternal tidak boleh berubah, kecocokan yang hanya berdasarkan slug seharusnya menjadi konflik aplikasi.

Jika aturan sebenarnya lebih sederhana—“kunci eksternal selalu menentukan artikel”—gunakan upsert biasa dengan target. Pernyataan lengkap ini dijalankan setelah penyiapan di atas:

INSERT INTO articles (external_key, slug, title)
VALUES ('feed-101', 'blue-notes', 'Revised blue notes')
ON CONFLICT (external_key) DO UPDATE
SET slug = EXCLUDED.slug,
    title = EXCLUDED.title
RETURNING id, external_key, slug, title;

Di sini EXCLUDED menyediakan nilai yang diusulkan. Batasan unik lainnya tetap berlaku: mengubah slug yang diusulkan menjadi green-notes akan menghasilkan konflik keunikan independen, bukan beralih ke artikel lain. Jaminan atomik upsert bertarget untuk menyisipkan atau memperbarui berlaku dengan syarat tidak ada kesalahan independen. Dokumentasi INSERT PostgreSQL

Jaga kestabilan keputusan berdasarkan salah satu kunci selama penulisan serentak

Pencarian yang diikuti penyisipan menciptakan jeda antara pengambilan keputusan dan penulisan. Transaksi lain dapat menyisipkan kunci yang cocok selama jeda itu. Menambahkan FOR UPDATE pada pencarian akan mengunci baris yang ditemukan; jika tidak ada baris yang cocok, tidak ada baris hasil pencarian untuk dikunci. Karena itu, penguncian baris saja tidak cukup untuk jalur tanpa kecocokan ini. Penguncian Eksplisit PostgreSQL

Untuk impor kecil atau operasi desktop sesekali, solusi sederhana adalah mengambil kunci tabel SHARE ROW EXCLUSIVE sebelum memeriksa salah satu kunci. Mode ini berkonflik dengan kunci ROW EXCLUSIVE yang diperoleh oleh penyisipan, pembaruan, dan penghapusan biasa, serta berkonflik dengan instans mode yang sama. Pembacaan biasa tetap kompatibel. Mode kunci tingkat tabel PostgreSQL

Urutannya menjadi: mulai transaksi, ambil kunci tabel, cari baris berbeda yang cocok, terapkan tabel keputusan, lalu commit. Kunci eksplisit akan menunggu kunci yang tidak kompatibel dan tetap dipegang sampai transaksi berakhir. LOCK PostgreSQL

Gunakan READ COMMITTED secara eksplisit di sini. Pencariannya akan melihat data yang sudah di-commit sebelum pencarian dimulai, termasuk penulisan yang selesai saat proses pengambilan kunci sedang menunggu. Menggabungkan perilaku snapshot tersebut dengan kunci tabel yang diperoleh memberikan dasar yang stabil bagi implementasi ini untuk mengambil keputusan dan menulis. Isolasi Transaksi PostgreSQL

Ini adalah kunci yang cakupannya luas: penulisan artikel yang tidak terkait juga akan menunggu. Jaga transaksi tetap singkat, tanpa perintah interaktif atau pekerjaan eksternal antara penguncian dan commit. Untuk impor serentak yang sering dilakukan, pertimbangkan kembali apakah satu kunci kanonis dapat menentukan identitas sebelum menggunakan desain ini.

Implementasikan transaksi dengan node-postgres

Anda memerlukan basis data demonstrasi PostgreSQL 18 yang berjalan, perintah psql, dan versi Node.js yang didukung oleh paket node-postgres yang Anda instal. Di Terminal, instal paket dan muat berkas penyiapan. Perintah pertama akan membuat metadata paket jika direktori demonstrasi Anda belum memilikinya. Instalasi dan kompatibilitas node-postgres

npm init -y
npm install pg
psql -v ON_ERROR_STOP=1 -f setup.sql

Perintah ini mengasumsikan pengaturan koneksi lokal biasa Anda sudah memilih basis data demonstrasi. Program menggunakan new Pool(), yang membaca variabel lingkungan koneksi PostgreSQL yang sama dengan psql; atur PGDATABASE ke nama basis data demonstrasi Anda jika perlu. Menghubungkan dengan node-postgres

Simpan program lengkap ini sebagai upsert-articles.mjs. Program ini mengasumsikan tabel penyiapan tidak memiliki pemicu atau aturan tambahan yang mengubah perilaku pencocokan atau pembaruannya. Nilai menggunakan parameter posisional yang disediakan secara terpisah dari teks SQL. Kueri node-postgres

import pg from 'pg';
const { Pool } = pg;
const pool = new Pool();

async function upsertArticle(input) {
  const client = await pool.connect();
  let discardClient = false;

  try {
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    await client.query(
      'LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE'
    );

    const matches = await client.query(
      `SELECT id FROM articles
       WHERE external_key = $1 OR slug = $2
       ORDER BY id`,
      [input.externalKey, input.slug]
    );

    if (matches.rows.length > 1) {
      const error = new Error(
        'Incoming keys identify two different articles'
      );
      error.code = 'ARTICLE_KEY_CONFLICT';
      throw error;
    }

    const values = [input.externalKey, input.slug, input.title];
    const result = matches.rows.length === 0
      ? await client.query(
          `INSERT INTO articles (external_key, slug, title)
           VALUES ($1, $2, $3)
           RETURNING id, external_key, slug, title`,
          values
        )
      : await client.query(
          `UPDATE articles
           SET external_key = $1, slug = $2, title = $3
           WHERE id = $4
           RETURNING id, external_key, slug, title`,
          [...values, matches.rows[0].id]
        );

    if (result.rows.length !== 1) {
      throw new Error('Expected exactly one article');
    }

    await client.query('COMMIT');
    return result.rows[0];
  } catch (error) {
    try {
      await client.query('ROLLBACK');
    } catch {
      discardClient = true;
    }
    throw error;
  } finally {
    client.release(discardClient);
  }
}

try {
  console.log(await upsertArticle({
    externalKey: 'feed-101',
    slug: 'blue-notes',
    title: 'Revised blue notes'
  }));
} finally {
  await pool.end();
}

Setiap pernyataan transaksi menggunakan klien yang sama dari pool, sebagaimana disyaratkan node-postgres. Mengirimkan pernyataan-pernyataan ini melalui pemanggilan pool.query() yang terpisah tidak akan mempertahankan persyaratan koneksi tersebut. Transaksi node-postgres

Blok finally selalu melepaskan klien yang telah diperoleh. Jika rollback itu sendiri gagal, release(true) akan membuang koneksi. Bagian luar pool.end() menutup pool saat skrip desktop selesai. API Pool node-postgres

Jalankan program dengan node upsert-articles.mjs. Hasil yang diharapkan adalah artikel biru dengan judul yang telah direvisi dan kunci yang tidak berubah. Sintaks JavaScript telah diperiksa dengan Node.js; contoh basis data telah dicocokkan dengan dokumentasi, tetapi tidak dijalankan pada server PostgreSQL di sini.

Periksa setiap cabang dan tangani kegagalan secara eksplisit

Dengan menggunakan baris penyiapan, ubah objek yang diteruskan ke upsertArticle() untuk mencoba kasus-kasus berikut. Jalankan setiap kasus pada kondisi penyiapan awal, atau perhitungkan perubahan dari kasus sebelumnya.

externalKey slug Hasil yang diharapkan
feed-303 amber-notes Sisipkan artikel baru
feed-101 blue-revised Perbarui artikel biru dan ganti slug-nya
feed-303 blue-notes Perbarui artikel biru dan ganti kunci eksternalnya
feed-101 blue-notes Perbarui judul artikel biru
feed-101 green-notes Lempar ARTICLE_KEY_CONFLICT; jangan ubah kedua artikel

Pencarian mengembalikan baris, sehingga artikel yang cocok dengan kedua kondisi dihitung satu kali. Dengan dua kunci unik independen yang tidak bernilai null ini, paling banyak dua baris berbeda yang dapat cocok. Kode ini tidak pernah menyelesaikan hasil dua baris dengan menggunakan LIMIT 1, menghapus artikel, atau diam-diam melewatkan masukan.

ARTICLE_KEY_CONFLICT adalah kode yang ditentukan aplikasi. 23505 milik PostgreSQL berarti unique_violation; ini adalah kondisi terpisah. Jika kondisi terakhir muncul setelah contoh ini diadaptasi, periksa batasan yang dilanggar dan perilaku tabel tambahan, alih-alih menganggap semua duplikat menghasilkan hal yang sama. PostgreSQL menyarankan pemeriksaan kode kesalahan, bukan penguraian teks pesan. Kode Kesalahan PostgreSQL

Pengambilan kunci dapat menunggu. Jika Anda menginginkan kegagalan langsung untuk tugas desktop ini, ganti pernyataan penguncian dengan LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE NOWAIT. PostgreSQL kemudian akan memunculkan kesalahan jika kunci tidak dapat segera diperoleh, dan program akan mengikuti jalur rollback. LOCK PostgreSQL

Apakah DO NOTHING atau MERGE akan menyederhanakan tugas ini?

ON CONFLICT DO NOTHING tanpa target dapat melewatkan penyisipan yang berbenturan dengan batasan unik atau indeks unik apa pun yang dapat digunakan. Pilih ini jika melewatkan penyisipan memang hasil yang diinginkan; cara ini tidak menerapkan kebijakan pembaruan yang dikembangkan di sini. Dokumentasi INSERT PostgreSQL

MERGE diperkenalkan di PostgreSQL 15. Meskipun memungkinkan kondisi join yang melibatkan salah satu kunci, join tersebut tetap membutuhkan kebijakan untuk baris sumber yang cocok dengan dua artikel target berbeda. Tindakannya tetap tunduk pada batasan biasa, dan perilaku serentaknya tidak dapat dipertukarkan dengan upsert bertarget. Catatan rilis PostgreSQL 15, MERGE PostgreSQL

Untuk tugas yang mencocokkan berdasarkan salah satu kunci, jadikan jumlah kecocokan sebagai keputusan eksplisit: nol berarti menyisipkan, satu berarti memperbarui, dan dua berarti berhenti dengan konflik yang jelas. Pertahankan batasan unik dan pilih strategi konkurensi yang menjaga keputusan tersebut hingga penulisan selesai. Implementasi dengan kunci tabel ini menjadi titik awal konkret ketika serialisasi penulisan artikel dapat diterima.

Sumber