PostgreSQL आम तौर पर किसी यूनिक कॉलम में कई SQL NULL मानों की अनुमति देता है। अप्रत्याशित डुप्लिकेट-की त्रुटि का निदान करने के लिए, सटीक इंडेक्स पहचानें, वास्तव में भेजे गए मानों की जाँच करें और देखें कि उसकी परिभाषा null की तुलना करने का तरीका बदलती है या नहीं। यूनिक इंडेक्स

यह गाइड Mac से psql या node-postgres का उपयोग करके आर्टिकल इन्सर्ट को डीबग करने वालों के लिए है। इसमें कृत्रिम आर्टिकल पंक्तियों का उपयोग किया गया है और निदान के क्रम में बताया गया है: डिफ़ॉल्ट व्यवहार को दोहराएँ, विफल होने वाले नियम का पता लगाएँ, फिर उचित सुधार चुनें।

nullable यूनिक कॉलम वास्तव में किसकी अनुमति देता है?

किसी nullable कॉलम में NULL हो सकता है; यूनिक constraint तय करता है कि कौन-से संयोजन दोहराए जा सकते हैं। सामान्य UNIQUE (slug) के साथ, दोहराए गए non-null स्लग अस्वीकार किए जाते हैं, जबकि दोहराए गए null स्वीकार किए जाते हैं। सामान्य UNIQUE (section, slug) के साथ, किसी भी constrained कॉलम में null होने पर उस constraint के तहत अन्यथा दोहराए जाने वाले संयोजनों की अनुमति मिलती है। तालिका के अन्य यूनिक नियम फिर भी लागू होते हैं। कंस्ट्रेंट्स

PostgreSQL 15 में NULLS NOT DISTINCT आया, जो यूनिकनेस जाँच के लिए null को समान मानता है। कंपोज़िट की में, पूरा संयोजन मेल खाना चाहिए: ('notes', NULL) का दूसरे ('notes', NULL) से टकराव होता है, लेकिन ('guides', NULL) से नहीं। PostgreSQL 14 इस क्लॉज़ का समर्थन नहीं करता। रिलीज़ 15, यूनिक इंडेक्स

वास्तविक नियम या इनपुट क्या दोहराया गया मान डुप्लिकेट-की त्रुटि दे सकता है?
SQL NULL के साथ सामान्य UNIQUE (slug) नहीं, केवल इस नियम के तहत
UNIQUE NULLS NOT DISTINCT (slug) हाँ, PostgreSQL 15 या बाद के संस्करण में
'' के साथ सामान्य UNIQUE (slug) हाँ; यह एक खाली स्ट्रिंग है
'NULL' के साथ सामान्य UNIQUE (slug) हाँ; यह टेक्स्ट है
उसी पंक्ति में कोई अन्य यूनिक की हाँ, nullable स्लग से स्वतंत्र

SQL NULL, खाली स्ट्रिंग और टेक्स्ट 'NULL' अलग-अलग इनपुट हैं। PostgreSQL का तुलना संबंधी दस्तावेज़ null और सामान्य टेक्स्ट मानों के बीच स्पष्ट अंतर करता है; null होने की जाँच IS NULL से करें। तुलना फ़ंक्शन और ऑपरेटर

Mac Terminal में डिफ़ॉल्ट व्यवहार दोहराएँ

आपको मौजूदा PostgreSQL कनेक्शन और Terminal में उपलब्ध psql कमांड चाहिए। आम तौर पर इस्तेमाल होने वाली कनेक्शन सेटिंग से एक सेशन खोलें। उदाहरण के लिए, यदि आपके स्थानीय डेटाबेस का नाम postgres है, तो यह चलाएँ। -d विकल्प डेटाबेस चुनता है। psql

psql -d postgres

संस्करण-विशिष्ट सिंटैक्स चुनने से पहले जुड़े सर्वर का संस्करण जाँचें। server_version सर्वर संस्करण बताता है, जो NULLS NOT DISTINCT के लिए प्रासंगिक सीमा है। प्रीसेट विकल्प

SHOW server_version;

इस पूरी आधारभूत मिसाल को एक नए सेशन में एक बार चलाएँ। इसका सामान्य यूनिक constraint PostgreSQL 14 और बाद के संस्करणों में काम करता है; यह केवल एक अस्थायी प्रदर्शन तालिका बनाता है।

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;

दिखाई गई परिभाषा के तहत चारों इन्सर्ट सफल होने चाहिए। पहली दो पंक्तियों में is_sql_null = true है; तीसरी में is_empty_string = true है; चौथी में लिटरल टेक्स्ट है। null से जुड़ी तुलना स्वयं null दे सकती है, इसलिए परिणाम समझने के लिए स्पष्ट nullness कॉलम का उपयोग करें। कंस्ट्रेंट्स, तुलना फ़ंक्शन और ऑपरेटर

यह आधारभूत उदाहरण एक उपयोगी विभाजन रेखा देता है। यदि यह उदाहरण सफल होता है, लेकिन एप्लिकेशन विफल होता है, तो एप्लिकेशन की वास्तविक तालिका परिभाषा और भेजे गए मानों की तुलना आधारभूत उदाहरण से करें। केवल nullable कॉलम की मौजूदगी से विफल होने वाले नियम की पहचान नहीं होती।

त्रुटि के पीछे का सटीक नियम खोजें

SQLSTATE 23505 से शुरुआत करें, जो PostgreSQL का unique_violation के लिए कोड है। त्रुटि संदेश के शब्दों से मिलान करने के बजाय कोड जाँचें। फिर उपलब्ध होने पर रिपोर्ट किए गए स्कीमा, तालिका, constraint और विवरण को दर्ज करें। PostgreSQL त्रुटि के constraint-name फ़ील्ड में इंडेक्स को constraint मानता है, भले ही उसे सीधे CREATE UNIQUE INDEX से बनाया गया हो। PostgreSQL त्रुटि कोड, त्रुटि और सूचना संदेश फ़ील्ड

आधारभूत उदाहरण वाले उसी psql सेशन में इस निदान क्वेरी से इंडेक्स देखें। pg_indexes दृश्य हर इंडेक्स का स्कीमा, नाम और पुनर्निर्मित परिभाषा दिखाता है। क्वेरी को अनुकूलित करते समय त्रुटि में बताए गए तालिका और स्कीमा, दोनों से मिलान करें। pg_indexes

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

हर यूनिक परिभाषा पढ़ें, और शुरुआत त्रुटि में दिए नाम से करें। डुप्लिकेट article_id प्राथमिक कुंजी का उल्लंघन करता है, भले ही slug null हो। प्राथमिक कुंजियों के मान यूनिक और non-null होने चाहिए; पंक्ति में कहीं और nullable कॉलम होने से यह शर्त ढीली नहीं होती। कंस्ट्रेंट्स

आधारभूत उदाहरण से ये तीन बदलाव देखें:

  • Null समानता: NULLS NOT DISTINCT जानबूझकर दोहराई गई null कुंजियों को टकराव का कारण बनाता है।
  • एक्सप्रेशन: यूनिकनेस लागू नियम संग्रहित कॉलम के बजाय गणना किए गए मान पर हो सकता है।
  • प्रीडिकेट: आंशिक इंडेक्स अपना नियम केवल उन पंक्तियों पर लागू करता है जो उसकी WHERE शर्त पूरी करती हैं।

एक्सप्रेशन इंडेक्स के लिए, टकराव की जाँच करते समय इंडेक्स किए गए एक्सप्रेशन को दोहराएँ। उदाहरण के लिए, lower(slug) पर बना इंडेक्स उन दो non-null स्लग को अस्वीकार कर सकता है जिनमें केवल बड़े-छोटे अक्षरों का अंतर हो। COALESCE(slug, '') पर बना इंडेक्स null और खाली स्ट्रिंग, दोनों को एक ही इंडेक्स किए गए मान में बदलता है; इसलिए ये इनपुट टकरा सकते हैं। यह एक्सप्रेशन-इंडेक्स यूनिकनेस और COALESCE के दस्तावेज़ित व्यवहार से निकलता है। एक्सप्रेशन पर इंडेक्स, कंडीशनल एक्सप्रेशन

आंशिक यूनिक इंडेक्स को भी ध्यान से पढ़ना ज़रूरी है। WHERE slug IS NULL के साथ section पर बना इंडेक्स null स्लग वाली पंक्तियों के उपसमूह में, प्रति सेक्शन एक मेल खाती पंक्ति लागू करता है। यह स्वयं slug पर यूनिकनेस लागू नहीं करता। PostgreSQL के दस्तावेज़ के अनुसार, आंशिक यूनिक इंडेक्स अपने प्रीडिकेट से चुने गए उपसमूह के भीतर यूनिकनेस लागू करते हैं। आंशिक इंडेक्स

जाँचें कि node-postgres वास्तव में क्या भेजता है

node-postgres, JavaScript null और undefined क्वेरी पैरामीटर, दोनों को SQL null में बदलता है। खाली स्ट्रिंग और स्ट्रिंग 'NULL' सामान्य टेक्स्ट इनपुट बने रहते हैं। दिए गए null पैरामीटर और छोड़े गए SQL कॉलम में भी अंतर करें: छोड़े गए कॉलम में घोषित डिफ़ॉल्ट मान आता है, या डिफ़ॉल्ट न होने पर null। क्वेरी, INSERT

निम्नलिखित पूरा प्रोग्राम ये अंतर स्पष्ट करता है। इसके लिए Node.js, npm और आपकी मौजूदा PostgreSQL कनेक्शन सेटिंग चाहिए। किसी कार्यशील फ़ोल्डर में निर्भरता इंस्टॉल करें, प्रोग्राम को diagnose-null.mjs नाम से सहेजें, फिर चलाएँ। क्लाइंट PostgreSQL के मानक कनेक्शन एनवायरनमेंट वेरिएबल इस्तेमाल करता है। कनेक्ट करना

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

इस प्रोग्राम की स्कीमा के तहत, घोषित डिफ़ॉल्ट के बावजूद पहली दो पंक्तियों में SQL null संग्रहित होना चाहिए। पहली खाली स्ट्रिंग और पहला 'NULL' स्ट्रिंग इन्सर्ट होना चाहिए; इनके दोहराए गए मानों से 23505 आना चाहिए। प्रोग्राम इन अपेक्षित टकरावों की रिपोर्ट करता है और अन्य त्रुटियों को फिर से फेंकता है। इसकी निदान प्रॉपर्टी के नाम node-postgres के DatabaseError फ़ील्ड से मेल खाते हैं। क्वेरी, DatabaseError स्रोत

तैयारी के दौरान डेटाबेस के उदाहरणों की प्राप्त दस्तावेज़ों से जाँच की गई थी, लेकिन उन्हें PostgreSQL पर चलाया नहीं गया।

nullable कुंजियों की क्वेरी करें और मेल खाती पंक्तियाँ न चूकें

slug = NULL का उपयोग करने वाली निदान खोज null स्लग नहीं खोजेगी। तय null खोज के लिए IS NULL का उपयोग करें, या जब कोई मान null या non-null हो सकता हो, तब IS NOT DISTINCT FROM का उपयोग करें। तुलना में यह बाद वाला दो null को समान मानता है। तुलना फ़ंक्शन और ऑपरेटर

यह निदान क्वेरी उसी सेशन में पहले बनाई गई article_probe तालिका पर चलती है। इससे null स्लग वाली दो पंक्तियाँ लौटनी चाहिए।

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

पैरामीटर वाली खोज के लिए, संबंधित प्रीडिकेट अंश WHERE slug IS NOT DISTINCT FROM $1::text है। कंपोज़िट नियम के हर की घटक का मिलान करें और इंडेक्स किए गए किसी भी एक्सप्रेशन या आंशिक-इंडेक्स प्रीडिकेट को दोहराएँ। मेल खाती null पंक्तियाँ मिलने से पता चलता है कि क्या संग्रहित है; वे यूनिकनेस का उल्लंघन करती हैं या नहीं, यह इंडेक्स की परिभाषा पर निर्भर करता है।

अपेक्षित नियम के अनुसार सुधार चुनें

यदि आर्टिकल में कितने भी स्लग गायब हो सकते हैं, तो सामान्य UNIQUE (slug) पहले से ही उस आवश्यकता को व्यक्त करता है। त्रुटि से पहचाने गए गलत टेक्स्ट मान, अनचाहे एक्सप्रेशन या अन्य डुप्लिकेट की को ठीक करें। Null को संभालने का तरीका बदलने से किसी दूसरी की का टकराव हल नहीं होगा।

यदि आवश्यकता प्रति सेक्शन एक null स्लग की है, तो PostgreSQL 15 या बाद का संस्करण इसे सीधे व्यक्त कर सकता है। यह स्वतंत्र प्रदर्शन जानबूझकर ऐसे इन्सर्ट पर समाप्त होता है जिससे 23505 आने की अपेक्षा है: पहली पंक्ति पहले से ही उस सेक्शन और null के संयोजन पर मौजूद है। रिलीज़ 15, यूनिक इंडेक्स

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');

PostgreSQL 14 में, इस खास दो-कॉलम डिज़ाइन के लिए आंशिक यूनिक इंडेक्स का उपयोग करें। निम्नलिखित स्वतंत्र उदाहरण वही नियम लागू करता है, क्योंकि section non-null है और slug ही एकमात्र nullable की घटक है। यह PostgreSQL के दस्तावेज़ित आंशिक-इंडेक्स व्यवहार से निकली संरचना है। आंशिक इंडेक्स

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');

पहला इंडेक्स किसी सेक्शन के भीतर भरे हुए स्लग को दोहरने से रोकता है। दूसरा प्रति सेक्शन एक गायब स्लग की अनुमति देता है। अधिक nullable की घटकों के लिए उनके संभावित संयोजनों पर अतिरिक्त विचार करना होगा; दो इंडेक्स वाली यह संरचना बताई गई स्कीमा के लिए विशिष्ट है।

अंत में, conflict handling स्पष्ट रूप से चुनें। ON CONFLICT DO NOTHING टकराने वाले इन्सर्ट को छोड़ देता है; यह मूल मानों को ठीक नहीं करता। ON CONFLICT DO UPDATE के लिए conflict target ज़रूरी है, जिसके कॉलम, एक्सप्रेशन और लागू प्रीडिकेट को इच्छित नियम की पहचान करनी चाहिए। इनमें से कोई भी क्रिया वहाँ null टकराव पैदा नहीं करती जहाँ सामान्य यूनिकनेस दोहराए गए null की अनुमति देती है। INSERT

निदान तब पूरा होता है जब आप रिपोर्ट किए गए इंडेक्स नाम को उसकी परिभाषा से जोड़ सकें, भेजे गए की मानों को दोहरा सकें और उस नियम के तहत टकराव समझा सकें। सुधार के बाद वही सटीक इनपुट फिर से जाँचें और लौटे हुए परिणाम तथा संग्रहित पंक्तियों, दोनों की पुष्टि करें, ताकि इच्छित आर्टिकल का हिसाब मिल सके।

स्रोत