PostgreSQL biasanya membenarkan berbilang nilai SQL NULL dalam lajur unik. Untuk mendiagnosis ralat kunci pendua yang tidak dijangka, kenal pasti indeks yang tepat, periksa nilai yang benar-benar dihantar dan semak sama ada definisinya mengubah cara null dibandingkan. Indeks Unik

Panduan langkah demi langkah ini ditujukan kepada sesiapa yang menyahpepijat sisipan artikel daripada Mac menggunakan psql atau node-postgres. Panduan ini menggunakan baris artikel sintetik dan menyediakan urutan diagnostik: hasilkan semula tingkah laku lalai, jejaki peraturan yang menyebabkan kegagalan, kemudian pilih pembetulan yang sesuai.

Apakah yang sebenarnya dibenarkan oleh lajur unik yang boleh menerima null?

Lajur yang boleh menerima null boleh mengandungi NULL; kekangan unik menentukan gabungan yang boleh berulang. Dengan UNIQUE (slug) biasa, slug bukan null yang berulang akan ditolak, manakala null yang berulang dibenarkan. Dengan UNIQUE (section, slug) biasa, null dalam mana-mana lajur yang dikenakan kekangan membenarkan gabungan berulang di bawah kekangan itu. Peraturan unik lain pada jadual tetap terpakai. Kekangan

PostgreSQL 15 memperkenalkan NULLS NOT DISTINCT, yang menganggap null sebagai sama ketika menyemak keunikan. Untuk kunci komposit, keseluruhan gabungan mesti sepadan: ('notes', NULL) bercanggah dengan satu lagi ('notes', NULL), tetapi tidak dengan ('guides', NULL). PostgreSQL 14 tidak menyokong klausa itu. Keluaran 15, Indeks Unik

Peraturan atau input sebenar Bolehkah nilai berulang menyebabkan ralat kunci pendua?
UNIQUE (slug) biasa dengan SQL NULL Tidak, jika peraturan ini sahaja yang terpakai
UNIQUE NULLS NOT DISTINCT (slug) Ya, pada PostgreSQL 15 atau lebih baharu
UNIQUE (slug) biasa dengan '' Ya; ini ialah rentetan kosong
UNIQUE (slug) biasa dengan 'NULL' Ya; ini ialah teks
Kunci unik lain pada baris yang sama Ya, secara berasingan daripada slug yang boleh menerima null

SQL NULL, rentetan kosong dan teks 'NULL' ialah input yang berbeza. Dokumentasi perbandingan PostgreSQL secara jelas membezakan null daripada nilai teks biasa; uji sama ada nilai itu null dengan IS NULL. Fungsi dan Operator Perbandingan

Hasilkan semula tingkah laku lalai dalam Terminal Mac

Anda memerlukan sambungan PostgreSQL sedia ada dan arahan psql yang tersedia dalam Terminal. Gunakan tetapan sambungan yang biasa anda gunakan untuk membuka sesi. Contohnya, jika pangkalan data setempat anda bernama postgres, jalankan arahan berikut. Pilihan -d memilih pangkalan data. psql

psql -d postgres

Semak versi pelayan yang disambungkan sebelum memilih sintaks khusus versi. server_version melaporkan versi pelayan, iaitu sempadan versi yang berkaitan untuk NULLS NOT DISTINCT. Pilihan Pratetap

SHOW server_version;

Jalankan contoh asas lengkap ini sekali dalam sesi baharu. Kekangan unik biasanya berfungsi pada PostgreSQL 14 dan versi lebih baharu; contoh ini hanya mencipta jadual 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-empat sisipan sepatutnya berjaya dengan definisi yang ditunjukkan. Dua baris pertama mempunyai is_sql_null = true; baris ketiga mempunyai is_empty_string = true; baris keempat mengandungi teks literal. Perbandingan yang melibatkan null boleh menghasilkan null, jadi gunakan lajur null eksplisit untuk mentafsir hasilnya. Kekangan, Fungsi dan Operator Perbandingan

Asas ini memberi anda garis pemisah yang berguna. Jika aplikasi gagal sedangkan contoh ini berjaya, bandingkan definisi jadual sebenar aplikasi dan nilai yang dihantar dengan asas ini. Kehadiran lajur yang boleh menerima null sahaja tidak mengenal pasti peraturan yang menyebabkan kegagalan.

Cari peraturan tepat yang menyebabkan ralat

Mulakan dengan SQLSTATE 23505, kod PostgreSQL untuk unique_violation. Semak kod itu dan bukannya memadankan teks mesej ralat. Kemudian catat skema, jadual, kekangan dan butiran yang dilaporkan, jika tersedia. PostgreSQL menganggap indeks sebagai kekangan untuk medan nama kekangan dalam ralat, walaupun indeks itu dicipta secara langsung dengan CREATE UNIQUE INDEX. Kod Ralat PostgreSQL, Medan Mesej Ralat dan Pemberitahuan

Dalam sesi psql yang sama dengan contoh asas, periksa indeks menggunakan pertanyaan diagnostik ini. Pandangan pg_indexes mendedahkan skema, nama dan definisi yang dibina semula bagi setiap indeks. Apabila menyesuaikan pertanyaan, padankan jadual dan skema yang dilaporkan oleh ralat. pg_indexes

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

Baca setiap definisi unik, bermula dengan nama yang disebut dalam ralat. article_id yang pendua melanggar kunci utama walaupun slug ialah null. Kunci utama memerlukan nilai yang unik dan bukan null; lajur lain dalam baris yang boleh menerima null tidak melonggarkan keperluan itu. Kekangan

Cari tiga perubahan daripada asas ini:

  • Kesamaan null: NULLS NOT DISTINCT sengaja menyebabkan kunci null yang berulang bercanggah.
  • Ungkapan: keunikan mungkin dikenakan pada nilai yang dikira, bukannya lajur yang disimpan.
  • Predikat: indeks separa hanya mengenakan peraturannya pada baris yang memenuhi syarat WHERE.

Bagi indeks ungkapan, hasilkan semula ungkapan yang diindeks ketika menyiasat percanggahan. Contohnya, indeks pada lower(slug) boleh menolak dua slug bukan null yang berbeza hanya dari segi huruf besar dan kecil. Indeks pada COALESCE(slug, '') memetakan null dan rentetan kosong kepada nilai terindeks yang sama; oleh itu, input tersebut boleh bercanggah. Hal ini berpunca daripada keunikan indeks ungkapan dan tingkah laku COALESCE yang didokumenkan. Indeks pada Ungkapan, Ungkapan Bersyarat

Indeks unik separa juga perlu diteliti dengan cermat. Indeks pada section dengan WHERE slug IS NULL menguatkuasakan satu baris sepadan bagi setiap seksyen dalam kalangan baris yang slagnya null. Indeks itu tidak menguatkuasakan keunikan pada slug itu sendiri. PostgreSQL mendokumenkan indeks unik separa sebagai penguat kuasa keunikan dalam subset yang dipilih oleh predikatnya. Indeks Separa

Semak perkara yang sebenarnya dihantar oleh node-postgres

node-postgres menukar kedua-dua parameter pertanyaan JavaScript null dan undefined kepada null SQL. Rentetan kosong dan rentetan 'NULL' kekal sebagai input teks biasa. Bezakan juga parameter null yang dibekalkan daripada lajur SQL yang tidak disertakan: PostgreSQL mengisi lajur yang tidak disertakan dengan nilai lalai yang diisytiharkan, atau null jika tiada nilai lalai. Pertanyaan, INSERT

Program lengkap berikut menjelaskan perbezaan tersebut. Anda memerlukan Node.js, npm dan tetapan sambungan PostgreSQL sedia ada. Dalam folder kerja, pasang kebergantungan, simpan program sebagai diagnose-null.mjs, kemudian jalankannya. Klien menggunakan pemboleh ubah persekitaran sambungan PostgreSQL standard. Menyambung

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 sepatutnya menyimpan null SQL walaupun nilai lalai telah diisytiharkan. Rentetan kosong pertama dan rentetan 'NULL' pertama sepatutnya berjaya disisipkan; ulangan masing-masing sepatutnya menghasilkan 23505. Program ini melaporkan percanggahan yang dijangka dan melemparkan semula ralat lain. Nama sifat diagnostiknya sepadan dengan medan DatabaseError node-postgres. Pertanyaan, Kod sumber DatabaseError

Contoh pangkalan data ini disemak berdasarkan dokumentasi yang diperoleh, tetapi tidak dilaksanakan pada PostgreSQL semasa penyediaan.

Buat pertanyaan pada kunci yang boleh menerima null tanpa terlepas baris sepadan

Carian diagnostik menggunakan slug = NULL tidak akan menemui slug null. Gunakan IS NULL untuk carian null tetap, atau IS NOT DISTINCT FROM apabila nilai boleh jadi null atau bukan null. Operator yang kedua menganggap dua null sebagai sama dalam perbandingan. Fungsi dan Operator Perbandingan

Pertanyaan diagnostik ini berjalan pada jadual article_probe yang dibuat lebih awal dalam sesi yang sama. Ia sepatutnya mengembalikan dua baris yang slugnya null.

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

Untuk carian berparameter, pecahan predikat yang sepadan ialah WHERE slug IS NOT DISTINCT FROM $1::text. Padankan setiap komponen kunci bagi peraturan komposit dan hasilkan semula sebarang ungkapan terindeks atau predikat indeks separa. Menemui baris null yang sepadan mengesahkan perkara yang disimpan; sama ada baris tersebut melanggar keunikan tetap bergantung pada definisi indeks.

Pilih pembetulan yang sepadan dengan peraturan yang dimaksudkan

Jika artikel boleh mempunyai sebarang bilangan slug yang tiada, UNIQUE (slug) biasa sudah menyatakan keperluan itu. Betulkan nilai teks yang tersilap, ungkapan yang tidak disengajakan atau kunci pendua lain yang dikenal pasti oleh ralat. Mengubah pengendalian null tidak akan menyelesaikan percanggahan pada kunci yang lain.

Jika keperluannya ialah satu slug null bagi setiap seksyen, PostgreSQL 15 atau lebih baharu boleh menyatakannya secara langsung. Demonstrasi berasingan ini sengaja berakhir dengan sisipan yang dijangka menghasilkan 23505: baris pertama sudah mengisi gabungan seksyen dan null itu. Keluaran 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 separa untuk reka bentuk dua lajur yang khusus ini. Contoh berasingan berikut melaksanakan peraturan yang sama kerana section bukan null dan slug ialah satu-satunya komponen kunci yang boleh menerima null. Pembinaan ini diterbitkan daripada tingkah laku indeks separa yang didokumenkan oleh PostgreSQL. Indeks Separa

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 menghalang slug yang telah diisi daripada berulang dalam seksyen yang sama. Indeks kedua membenarkan satu slug yang tiada bagi setiap seksyen. Komponen kunci tambahan yang boleh menerima null memerlukan pertimbangan lanjut terhadap gabungan yang mungkin; binaan dua indeks ini khusus untuk skema yang dinyatakan.

Akhir sekali, pilih pengendalian konflik dengan jelas. ON CONFLICT DO NOTHING melangkau sisipan yang bercanggah; tindakan ini tidak membetulkan nilai asas. ON CONFLICT DO UPDATE memerlukan sasaran konflik, dan lajur, ungkapan serta predikat yang berkenaan padanya mesti mengenal pasti peraturan yang dimaksudkan. Kedua-dua tindakan ini tidak mewujudkan konflik null apabila keunikan biasa membenarkan null berulang. INSERT

Diagnosis selesai apabila anda dapat mengaitkan nama indeks yang dilaporkan dengan definisinya, menghasilkan semula nilai kunci yang dihantar dan menerangkan perlanggaran di bawah peraturan tersebut. Ulangi input yang sama tepat selepas pembetulan, kemudian semak hasil yang dikembalikan dan baris yang disimpan supaya artikel yang dimaksudkan dapat dipastikan.

Sumber