PostgreSQL biasanya mengizinkan beberapa nilai SQL NULL dalam kolom unik. Untuk mendiagnosis kesalahan kunci duplikat yang tidak terduga, identifikasi indeks yang tepat, periksa nilai yang benar-benar dikirim, dan pastikan apakah definisinya mengubah cara null dibandingkan. Indeks Unik

Panduan ini ditujukan bagi seseorang yang men-debug penyisipan artikel dari Mac menggunakan psql atau node-postgres. Panduan ini menggunakan baris artikel sintetis dan menyediakan urutan diagnosis: reproduksi perilaku default, telusuri aturan yang menyebabkan kegagalan, lalu pilih perbaikan yang sesuai.

Apa yang sebenarnya diizinkan oleh kolom unik yang dapat bernilai null?

Kolom yang dapat bernilai null dapat berisi NULL; batasan unik menentukan kombinasi mana yang boleh berulang. Dengan UNIQUE (slug) biasa, slug non-null yang berulang akan ditolak, sedangkan null yang berulang diizinkan. Dengan UNIQUE (section, slug) biasa, null pada salah satu kolom yang dibatasi memungkinkan kombinasi yang sama berulang berdasarkan batasan tersebut. Aturan unik lain pada tabel tetap berlaku. Batasan

PostgreSQL 15 memperkenalkan NULLS NOT DISTINCT, yang memperlakukan null sebagai nilai yang sama saat memeriksa keunikan. Untuk kunci komposit, seluruh kombinasi harus cocok: ('notes', NULL) bertentangan dengan ('notes', NULL) lainnya, tetapi tidak dengan ('guides', NULL). PostgreSQL 14 tidak mendukung klausa tersebut. Rilis 15, Indeks Unik

Aturan atau masukan aktual Bisakah nilai berulang menyebabkan kesalahan kunci duplikat?
UNIQUE (slug) biasa dengan SQL NULL Tidak, jika hanya aturan ini yang berlaku
UNIQUE NULLS NOT DISTINCT (slug) Ya, pada PostgreSQL 15 atau yang lebih baru
UNIQUE (slug) biasa dengan '' Ya; ini adalah string kosong
UNIQUE (slug) biasa dengan 'NULL' Ya; ini adalah teks
Kunci unik lain pada baris yang sama Ya, terlepas dari slug yang dapat bernilai null

SQL NULL, string kosong, dan teks 'NULL' adalah masukan yang berbeda. Dokumentasi perbandingan PostgreSQL secara eksplisit membedakan null dari nilai teks biasa; uji apakah suatu nilai null dengan IS NULL. Fungsi dan Operator Perbandingan

Reproduksi perilaku default di Terminal Mac

Anda memerlukan koneksi PostgreSQL yang sudah ada dan perintah psql yang tersedia di Terminal. Buka sesi menggunakan pengaturan koneksi yang biasa Anda pakai. Misalnya, jika nama basis data lokal Anda postgres, jalankan perintah berikut. Opsi -d memilih basis data. psql

psql -d postgres

Periksa versi server yang terhubung sebelum memilih sintaks yang bergantung pada versi. server_version melaporkan versi server, yang menjadi batas versi relevan untuk NULLS NOT DISTINCT. Opsi Preset

SHOW server_version;

Jalankan contoh dasar lengkap ini sekali dalam sesi baru. Batasan unik biasa di dalamnya berfungsi pada PostgreSQL 14 dan yang lebih baru; contoh ini hanya membuat tabel demonstrasi sementara.

CREATE TEMP TABLE article_probe (
    article_id integer PRIMARY KEY,
    title text NOT NULL,
    slug text,
    CONSTRAINT article_probe_slug_key UNIQUE (slug)
);

INSERT INTO article_probe (article_id, title, slug)
VALUES
    (1, 'Paper shapes', NULL),
    (2, 'Color arrangements', NULL),
    (3, 'Desk sketches', ''),
    (4, 'Pattern notes', 'NULL');

SELECT article_id, title, slug,
       slug IS NULL AS is_sql_null,
       slug = '' AS is_empty_string
FROM article_probe
ORDER BY article_id;

Keempat operasi penyisipan seharusnya berhasil berdasarkan definisi yang ditampilkan. Dua baris pertama memiliki is_sql_null = true; baris ketiga memiliki is_empty_string = true; baris keempat berisi teks literal. Perbandingan yang melibatkan null dapat menghasilkan null, jadi gunakan kolom null eksplisit untuk menafsirkan hasilnya. Batasan, Fungsi dan Operator Perbandingan

Contoh dasar ini memberi Anda acuan yang berguna. Jika aplikasi gagal sementara contoh ini berhasil, bandingkan definisi tabel aktual aplikasi dan nilai yang dikirim dengan contoh dasar. Keberadaan kolom yang dapat bernilai null saja tidak mengidentifikasi aturan yang menyebabkan kegagalan.

Temukan aturan pasti yang menyebabkan kesalahan

Mulailah dengan SQLSTATE 23505, kode PostgreSQL untuk unique_violation. Periksa kodenya, bukan dengan mencocokkan redaksi pesan kesalahan. Kemudian catat skema, tabel, batasan, dan detail yang dilaporkan jika tersedia. PostgreSQL memperlakukan indeks sebagai batasan untuk kolom nama batasan pada kesalahan, meskipun indeks tersebut dibuat langsung dengan CREATE UNIQUE INDEX. Kode Kesalahan PostgreSQL, Kolom Pesan Kesalahan dan Pemberitahuan

Di sesi psql yang sama dengan contoh dasar, periksa indeks menggunakan kueri diagnostik ini. Tampilan pg_indexes menampilkan skema, nama, dan definisi hasil rekonstruksi untuk setiap indeks. Saat menyesuaikan kueri, cocokkan tabel dan skema yang dilaporkan oleh kesalahan. pg_indexes

SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'article_probe'
ORDER BY schemaname, indexname;

Baca setiap definisi unik, dimulai dari nama yang tercantum pada kesalahan. Duplikasi article_id melanggar kunci primer meskipun slug bernilai null. Kunci primer mengharuskan nilai yang unik dan non-null; kolom lain pada baris yang dapat bernilai null tidak melonggarkan persyaratan tersebut. Batasan

Periksa tiga perubahan berikut dari contoh dasar:

  • Kesetaraan null: NULLS NOT DISTINCT sengaja membuat kunci null yang berulang saling bertentangan.
  • Ekspresi: keunikan mungkin berlaku pada nilai hasil perhitungan, bukan kolom yang disimpan.
  • Predikat: indeks parsial menerapkan aturannya hanya pada baris yang memenuhi kondisi WHERE.

Untuk indeks ekspresi, reproduksi ekspresi yang diindeks saat menyelidiki konflik. Misalnya, indeks pada lower(slug) dapat menolak dua slug non-null yang hanya berbeda dalam huruf besar-kecil. Indeks pada COALESCE(slug, '') memetakan null dan string kosong ke nilai terindeks yang sama; akibatnya, masukan tersebut dapat berbenturan. Hal ini mengikuti keunikan indeks ekspresi dan perilaku COALESCE yang didokumentasikan. Indeks pada Ekspresi, Ekspresi Kondisional

Indeks unik parsial juga perlu dibaca dengan cermat. Indeks pada section dengan WHERE slug IS NULL menerapkan satu baris yang cocok per bagian di antara baris yang slug-nya null. Indeks itu tidak menerapkan keunikan pada slug itu sendiri. PostgreSQL menjelaskan bahwa indeks unik parsial menerapkan keunikan di dalam subset yang dipilih oleh predikatnya. Indeks Parsial

Periksa apa yang sebenarnya dikirim node-postgres

node-postgres mengonversi parameter kueri JavaScript null dan undefined menjadi null SQL. String kosong dan string 'NULL' tetap menjadi masukan teks biasa. Bedakan juga parameter null yang diberikan dari kolom SQL yang tidak disertakan: PostgreSQL mengisi kolom yang tidak disertakan dengan nilai default yang dideklarasikan, atau null jika tidak ada nilai default. Kueri, INSERT

Program lengkap berikut memperlihatkan perbedaan tersebut. Program ini memerlukan Node.js, npm, dan pengaturan koneksi PostgreSQL yang sudah ada. Di folder kerja, instal dependensinya, simpan program sebagai diagnose-null.mjs, lalu jalankan. Klien menggunakan variabel lingkungan koneksi PostgreSQL standar. Menghubungkan

npm install pg

import pg from 'pg';

const { Client } = pg;
const client = new Client();

async function main() {
  await client.connect();
  try {
    await client.query(`
      CREATE TEMP TABLE article_node_probe (
        title text NOT NULL,
        slug text DEFAULT 'untitled',
        CONSTRAINT article_node_probe_slug_key UNIQUE (slug)
      )
    `);

    const samples = [
      { title: 'Paper shapes', slug: null },
      { title: 'Color arrangements', slug: undefined },
      { title: 'Desk sketches', slug: '' },
      { title: 'More sketches', slug: '' },
      { title: 'Pattern notes', slug: 'NULL' },
      { title: 'More patterns', slug: 'NULL' },
    ];

    for (const sample of samples) {
      console.log('Input:', {
        title: sample.title,
        type: typeof sample.slug,
        value: sample.slug,
      });

      try {
        const result = await client.query(
          `INSERT INTO article_node_probe (title, slug)
           VALUES ($1, $2)
           RETURNING title, slug, slug IS NULL AS is_sql_null`,
          [sample.title, sample.slug],
        );
        console.log('Inserted:', result.rows[0]);
      } catch (error) {
        if (error.code !== '23505') throw error;
        console.log('Duplicate:', {
          code: error.code,
          schema: error.schema,
          table: error.table,
          constraint: error.constraint,
          detail: error.detail,
        });
      }
    }
  } finally {
    await client.end();
  }
}

main().catch((error) => {
  console.error(error);
  process.exitCode = 1;
});

PGDATABASE=postgres node diagnose-null.mjs

Dengan skema program ini, dua baris pertama seharusnya menyimpan null SQL meskipun nilai default telah dideklarasikan. String kosong pertama dan string 'NULL' pertama seharusnya berhasil disisipkan; pengulangannya seharusnya menghasilkan 23505. Program ini melaporkan konflik yang diperkirakan tersebut dan melemparkan kembali kesalahan lainnya. Nama properti diagnostiknya sesuai dengan kolom DatabaseError milik node-postgres. Kueri, Kode sumber DatabaseError

Contoh basis data telah diperiksa berdasarkan dokumentasi yang diperoleh, tetapi tidak dijalankan pada PostgreSQL selama penyusunan.

Kueri kunci yang dapat bernilai null tanpa melewatkan baris yang cocok

Pencarian diagnostik menggunakan slug = NULL tidak akan menemukan slug null. Gunakan IS NULL untuk pencarian null yang tetap, atau IS NOT DISTINCT FROM jika nilai bisa null maupun non-null. Operator kedua memperlakukan dua nilai null sebagai sama dalam perbandingan. Fungsi dan Operator Perbandingan

Kueri diagnostik ini dijalankan pada tabel article_probe sebelumnya dalam sesi yang sama. Kueri ini seharusnya mengembalikan dua baris dengan slug null.

SELECT article_id, title, slug
FROM article_probe
WHERE slug IS NOT DISTINCT FROM NULL::text
ORDER BY article_id;

Untuk pencarian berparameter, bagian predikat yang sesuai adalah WHERE slug IS NOT DISTINCT FROM $1::text. Cocokkan setiap komponen kunci untuk aturan komposit, dan reproduksi ekspresi yang diindeks atau predikat indeks parsial apa pun. Menemukan baris null yang cocok memastikan apa yang disimpan; apakah baris tersebut melanggar keunikan tetap bergantung pada definisi indeks.

Pilih perbaikan yang sesuai dengan aturan yang dimaksud

Jika artikel boleh memiliki slug yang hilang dalam jumlah berapa pun, UNIQUE (slug) biasa sudah menyatakan persyaratan tersebut. Perbaiki nilai teks yang tidak sengaja, ekspresi yang tidak dimaksudkan, atau kunci duplikat lain yang diidentifikasi oleh kesalahan. Mengubah penanganan null tidak akan menyelesaikan benturan pada kunci yang berbeda.

Jika persyaratannya adalah satu slug null per bagian, PostgreSQL 15 atau yang lebih baru dapat menyatakannya secara langsung. Demonstrasi terpisah ini sengaja diakhiri dengan penyisipan yang diperkirakan menghasilkan 23505: baris pertama sudah menggunakan kombinasi bagian dan null tersebut. Rilis 15, Indeks Unik

CREATE TEMP TABLE article_one_missing (
    section text NOT NULL,
    slug text,
    title text NOT NULL,
    CONSTRAINT article_one_missing_key
        UNIQUE NULLS NOT DISTINCT (section, slug)
);

INSERT INTO article_one_missing (section, slug, title)
VALUES ('notes', NULL, 'Paper shapes');

-- Expected: 23505 on article_one_missing_key.
INSERT INTO article_one_missing (section, slug, title)
VALUES ('notes', NULL, 'Color arrangements');

Pada PostgreSQL 14, gunakan indeks unik parsial untuk desain dua kolom khusus ini. Contoh terpisah berikut menerapkan aturan yang sama karena section bernilai non-null dan slug adalah satu-satunya komponen kunci yang dapat bernilai null. Ini adalah konstruksi yang diturunkan dari perilaku indeks parsial yang didokumentasikan PostgreSQL. Indeks Parsial

CREATE TEMP TABLE article_legacy (
    section text NOT NULL,
    slug text,
    title text NOT NULL
);

CREATE UNIQUE INDEX article_legacy_present_key
ON article_legacy (section, slug)
WHERE slug IS NOT NULL;

CREATE UNIQUE INDEX article_legacy_missing_key
ON article_legacy (section)
WHERE slug IS NULL;

INSERT INTO article_legacy (section, slug, title)
VALUES ('notes', NULL, 'Paper shapes');

-- Expected: 23505 on article_legacy_missing_key.
INSERT INTO article_legacy (section, slug, title)
VALUES ('notes', NULL, 'Color arrangements');

Indeks pertama mencegah slug berisi nilai yang sama berulang dalam satu bagian. Indeks kedua mengizinkan satu slug yang hilang per bagian. Komponen kunci lain yang dapat bernilai null memerlukan pertimbangan tambahan atas berbagai kemungkinan kombinasinya; konstruksi dua indeks ini khusus untuk skema yang dinyatakan.

Terakhir, tentukan penanganan konflik secara eksplisit. ON CONFLICT DO NOTHING melewati penyisipan yang bertentangan; perintah ini tidak memperbaiki nilai yang mendasarinya. ON CONFLICT DO UPDATE memerlukan target konflik, yang kolom, ekspresi, dan predikatnya yang berlaku harus mengidentifikasi aturan yang dimaksud. Kedua tindakan tersebut tidak menciptakan konflik null saat keunikan biasa mengizinkan null berulang. INSERT

Diagnosis selesai ketika Anda dapat menghubungkan nama indeks yang dilaporkan dengan definisinya, mereproduksi nilai kunci yang dikirim, dan menjelaskan benturan berdasarkan aturan tersebut. Ulangi masukan yang sama persis setelah perbaikan, periksa hasil yang dikembalikan dan baris yang tersimpan, agar artikel yang dimaksud dapat dipastikan tercatat.

Sumber