يسمح PostgreSQL عادةً بتعدد قيم SQL NULL في عمود فريد. لتشخيص خطأ مفتاح مكرر غير متوقع، حدّد الفهرس بعينه، وافحص القيم التي أُرسلت فعلًا، وتحقّق مما إذا كان تعريفه يغيّر طريقة مقارنة القيم الخالية. الفهارس الفريدة

هذا الدليل مخصص لمن يشخّص أخطاء إدراج المقالات من جهاز Mac باستخدام psql أو node-postgres. ويستخدم صفوف مقالات اصطناعية، ويعرض تسلسلًا تشخيصيًا: أعد إنتاج السلوك الافتراضي، ثم تتبّع القاعدة المسببة للفشل، وبعدها اختر التصحيح المناسب.

ما الذي يسمح به عمود فريد يقبل القيم الخالية؟

يمكن أن يحتوي العمود الذي يقبل القيم الخالية على NULL؛ ويحدّد القيد الفريد التركيبات التي يجوز تكرارها. مع UNIQUE (slug) العادي، تُرفض القيم المتكررة غير الخالية للمعرّفات النصية، بينما يُسمح بتكرار القيم الخالية. ومع UNIQUE (section, slug) العادي، تسمح القيمة الخالية في أي من العمودين الخاضعين للقيد بتكرار التركيبات، وفقًا لذلك القيد. وتظل قواعد التفرد الأخرى على الجدول سارية. القيود

قدّم PostgreSQL 15 العبارة NULLS NOT DISTINCT، التي تعامل القيم الخالية على أنها متساوية عند التحقق من التفرد. وبالنسبة إلى مفتاح مركب، يجب أن تتطابق التركيبة كاملةً: يتعارض ('notes', NULL) مع ('notes', NULL) آخر، لكنه لا يتعارض مع ('guides', NULL). لا يدعم PostgreSQL 14 هذه العبارة. الإصدار 15، الفهارس الفريدة

القاعدة الفعلية أو الإدخال هل يمكن أن تؤدي قيمة مكررة إلى خطأ مفتاح مكرر؟
UNIQUE (slug) عادي مع SQL NULL لا، بموجب هذه القاعدة وحدها
UNIQUE NULLS NOT DISTINCT (slug) نعم، في PostgreSQL 15 أو الإصدارات الأحدث
UNIQUE (slug) عادي مع '' نعم؛ هذه سلسلة فارغة
UNIQUE (slug) عادي مع 'NULL' نعم؛ هذا نص حرفي
مفتاح فريد آخر في الصف نفسه نعم، بصرف النظر عن المعرّف النصي الذي يقبل القيم الخالية

تختلف SQL NULL والسلسلة الفارغة والنص 'NULL' بوصفها مدخلات. وتميّز وثائق المقارنة في PostgreSQL صراحةً بين القيم الخالية والقيم النصية العادية؛ اختبر كون القيمة خالية باستخدام IS NULL. دوال وعوامل المقارنة

أعد إنتاج السلوك الافتراضي في Terminal على Mac

تحتاج إلى اتصال PostgreSQL قائم وإتاحة الأمر psql في Terminal. استخدم إعدادات الاتصال المعتادة لديك لفتح جلسة. مثلًا، إذا كانت قاعدة البيانات المحلية لديك تُسمى postgres، فنفّذ الأمر التالي. يحدّد الخيار -d قاعدة البيانات. psql

psql -d postgres

تحقّق من إصدار الخادم المتصل قبل اختيار صياغة خاصة بإصدار معين. يعرض server_version إصدار الخادم، وهو الحد الفاصل ذي الصلة بعبارة NULLS NOT DISTINCT. الخيارات المعدّة مسبقًا

SHOW server_version;

نفّذ مثال خط الأساس الكامل هذا مرة واحدة في جلسة جديدة. يعمل قيده الفريد العادي على 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؛ أما الرابع فيحتوي على نص حرفي. وقد تؤدي المقارنة التي تتضمن قيمة خالية إلى نتيجة خالية بحد ذاتها، لذا استخدم عمود التحقق الصريح من الخلو لتفسير النتائج. القيود، دوال وعوامل المقارنة

يوفر خط الأساس هذا حدًا فاصلًا مفيدًا. إذا فشل التطبيق بينما نجح هذا المثال، فقارن تعريف الجدول الفعلي في التطبيق والقيم التي يرسلها بخط الأساس. وجود عمود يقبل القيم الخالية وحده لا يحدد القاعدة المسببة للفشل.

اعثر على القاعدة المحددة وراء الخطأ

ابدأ برمز SQLSTATE 23505، وهو رمز PostgreSQL لـ unique_violation. تحقّق من الرمز بدلًا من مطابقة صياغة رسالة الخطأ. ثم سجّل المخطط والجدول والقيد المبلّغ عنها والتفاصيل إن توفرت. يتعامل PostgreSQL مع الفهرس على أنه قيد في حقل اسم القيد ضمن الخطأ، حتى عندما أُنشئ مباشرةً باستخدام 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 خالية. تشترط المفاتيح الأساسية قيمًا فريدة وغير خالية؛ ولا يؤدي وجود عمود آخر يقبل القيم الخالية في الصف إلى تخفيف هذا الشرط. القيود

ابحث عن ثلاثة تغييرات مقارنة بخط الأساس:

  • مساواة القيم الخالية: تجعل NULLS NOT DISTINCT تعارض مفاتيح القيم الخالية المكررة عمدًا.
  • التعبيرات: قد ينطبق التفرد على قيمة محسوبة بدلًا من العمود المخزّن.
  • الشروط: لا يطبّق الفهرس الجزئي قاعدته إلا على الصفوف التي تستوفي شرط WHERE.

بالنسبة إلى فهرس التعبير، أعد إنتاج التعبير المفهرس عند التحقق من التعارض. مثلًا، يمكن لفهرس على lower(slug) أن يرفض معرّفين نصيين غير خاليين يختلفان في حالة الأحرف فقط. أما الفهرس على COALESCE(slug, '') فيحوّل كلًا من القيمة الخالية والسلسلة الفارغة إلى القيمة المفهرسة نفسها؛ وبالتالي قد يتعارض هذان الإدخالان. وينتج ذلك عن التفرد في فهرس التعبير والسلوك الموثق لـ COALESCE. الفهارس على التعبيرات، التعبيرات الشرطية

ويتطلب الفهرس الفريد الجزئي أيضًا قراءة متأنية. يفرض الفهرس على section مع WHERE slug IS NULL وجود صف مطابق واحد لكل قسم ضمن الصفوف ذات المعرّفات النصية الخالية. لكنه لا يفرض التفرد على slug نفسه. توثّق PostgreSQL أن الفهارس الفريدة الجزئية تفرض التفرد ضمن المجموعة الفرعية التي يحددها شرطها. الفهارس الجزئية

تحقّق مما يرسله node-postgres فعليًا

يحوّل node-postgres كلاً من معاملي استعلام JavaScript null وundefined إلى SQL null. وتظل السلاسل الفارغة والسلسلة 'NULL' مدخلات نصية عادية. وميّز أيضًا بين معامل خالٍ مُرسل وبين عمود SQL محذوف من الأمر: يملأ PostgreSQL العمود المحذوف بالقيمة الافتراضية المعلنة له، أو بقيمة خالية إذا لم تكن له قيمة افتراضية. الاستعلامات، 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. يبلّغ البرنامج عن هذه التعارضات المتوقعة ويعيد طرح الأخطاء الأخرى. وتتطابق أسماء خصائص التشخيص فيه مع حقول DatabaseError في node-postgres. الاستعلامات، مصدر DatabaseError

رُوجعت أمثلة قاعدة البيانات بالاستناد إلى الوثائق المسترجعة، لكنها لم تُنفّذ على PostgreSQL أثناء الإعداد.

استعلم عن المفاتيح التي تقبل القيم الخالية من دون إغفال الصفوف المطابقة

لن يعثر استعلام تشخيصي يستخدم slug = NULL على المعرّفات النصية الخالية. استخدم IS NULL للبحث عن قيمة خالية محددة، أو IS NOT DISTINCT FROM عندما يمكن أن تكون القيمة خالية أو غير خالية. وتتعامل العبارة الأخيرة مع قيمتين خاليتين على أنهما متساويتان في المقارنة. دوال وعوامل المقارنة

يُنفّذ استعلام التشخيص هذا على جدول article_probe السابق في الجلسة نفسها. وينبغي أن يعيد الصفين ذوي المعرّفات النصية الخالية.

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. طابق كل مكوّن من مكونات المفتاح في القاعدة المركبة، وأعد إنتاج أي تعبير مفهرس أو شرط فهرس جزئي. يثبت العثور على صفوف مطابقة ذات قيم خالية ما هو مخزّن؛ لكن ما إذا كانت تخالف التفرد يظل معتمدًا على تعريف الفهرس.

اختر التصحيح الذي يطابق القاعدة المطلوبة

إذا كان مسموحًا للمقالات أن تحتوي على أي عدد من المعرّفات النصية المفقودة، فإن UNIQUE (slug) العادي يعبّر عن هذا المتطلب بالفعل. صحّح قيمة نصية أُدخلت بالخطأ، أو تعبيرًا غير مقصود، أو مفتاحًا مكررًا آخر حدّده الخطأ. لن يحل تغيير طريقة التعامل مع القيم الخالية تعارضًا على مفتاح مختلف.

إذا كان المطلوب وجود معرّف نصي خالٍ واحد لكل قسم، فيمكن التعبير عن ذلك مباشرةً في PostgreSQL 15 أو الإصدارات الأحدث. ينتهي هذا المثال التوضيحي المستقل عمدًا بعملية إدراج يُتوقع أن تنتج 23505: فالصف الأول يشغل بالفعل تركيبة القسم والقيمة الخالية هذه. الإصدار 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 غير خالٍ، وslug هو مكوّن المفتاح الوحيد الذي يقبل القيم الخالية. وهذا إنشاء مستنبط من سلوك الفهارس الجزئية الموثق في 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');

يمنع الفهرس الأول تكرار المعرّفات النصية المعبأة ضمن القسم الواحد. ويسمح الثاني بمعرّف نصي مفقود واحد لكل قسم. وتتطلب مكونات المفتاح الإضافية التي تقبل القيم الخالية مزيدًا من النظر في التركيبات الممكنة؛ فهذا الإنشاء المؤلف من فهرسين خاص بالمخطط المذكور.

وأخيرًا، اختر معالجة التعارضات صراحةً. يتخطى ON CONFLICT DO NOTHING الإدراج المتعارض؛ ولا يصحح القيم الأساسية. ويتطلب ON CONFLICT DO UPDATE هدف تعارض، ويجب أن تحدد أعمدته وتعبيراته وشرطه المنطبق القاعدة المقصودة. ولا ينشئ أي من الإجراءين تعارضًا بين القيم الخالية حين يسمح التفرد العادي بتكرارها. INSERT

يكتمل التشخيص عندما تتمكن من ربط اسم الفهرس الوارد في الخطأ بتعريفه، وإعادة إنتاج قيم المفتاح المُرسلة، وشرح سبب التعارض وفق تلك القاعدة. أعد إدخال تلك المدخلات نفسها بعد التصحيح، وتحقق من النتيجة المعادة ومن الصفوف المخزنة، للتأكد من احتساب المقالة المقصودة.

المصادر