PostgreSQL, benzersiz bir sütunda normalde birden fazla SQL NULL değerine izin verir. Beklenmedik bir yinelenen anahtar hatasını teşhis etmek için tam olarak hangi dizinin kullanıldığını belirleyin, gerçekten gönderilen değerleri inceleyin ve tanımının NULL değerlerin karşılaştırılma biçimini değiştirip değiştirmediğini kontrol edin. Benzersiz Dizinler

Bu kılavuz, Mac’te psql veya node-postgres kullanarak makale eklemelerinde hata ayıklayan kişiler içindir. Örneklerde yapay makale satırları kullanılır ve şu tanılama adımları izlenir: varsayılan davranışı yeniden üretin, hataya neden olan kuralı izleyin, ardından uygun düzeltmeyi seçin.

NULL değer alabilen benzersiz bir sütun aslında neye izin verir?

NULL değer alabilen bir sütunda NULL bulunabilir; benzersiz kısıtlama ise hangi değer birleşimlerinin yinelenebileceğini belirler. Normal bir UNIQUE (slug) ile yinelenen NULL olmayan slug değerleri reddedilirken, yinelenen NULL değerlerine izin verilir. Normal bir UNIQUE (section, slug) kullanıldığında, kısıtlanan sütunlardan herhangi birindeki NULL değeri, bu kısıtlama kapsamında başka türlü yinelenebilecek birleşimlere izin verir. Tablodaki diğer benzersiz kurallar geçerliliğini korur. Kısıtlamalar

PostgreSQL 15, NULL değerlerini benzersizlik denetiminde eşit kabul eden NULLS NOT DISTINCT özelliğini kullanıma sundu. Bileşik bir anahtarda birleşimin tamamı eşleşmelidir: ('notes', NULL) başka bir ('notes', NULL) ile çakışır, ancak ('guides', NULL) ile çakışmaz. PostgreSQL 14 bu yan tümceyi desteklemez. Sürüm 15, Benzersiz Dizinler

Gerçek kural veya girdi Yinelenen bir değer yinelenen anahtar hatasına neden olabilir mi?
SQL NULL ile normal UNIQUE (slug) Yalnızca bu kural kapsamında hayır
UNIQUE NULLS NOT DISTINCT (slug) PostgreSQL 15 veya sonraki sürümlerde evet
'' ile normal UNIQUE (slug) Evet; bu boş bir dizgedir
'NULL' ile normal UNIQUE (slug) Evet; bu metindir
Aynı satırdaki başka bir benzersiz anahtar Evet; NULL değer alabilen slug’dan bağımsız olarak

SQL NULL, boş dizge ve 'NULL' metni farklı girdilerdir. PostgreSQL’in karşılaştırma belgeleri NULL ile normal metin değerleri arasındaki farkı açıkça belirtir; NULL olup olmadığını IS NULL ile sınayın. Karşılaştırma İşlevleri ve İşleçleri

Mac Terminal’de varsayılan davranışı yeniden üretin

Mevcut bir PostgreSQL bağlantısına ve Terminal içinde kullanılabilir psql komutuna ihtiyacınız var. Normalde kullandığınız bağlantı ayarlarıyla bir oturum açın. Örneğin, yerel veritabanınızın adı postgres ise aşağıdakini çalıştırın. -d seçeneği veritabanını seçer. psql

psql -d postgres

Sürüme özgü sözdizimini seçmeden önce bağlı sunucunun sürümünü kontrol edin. server_version sunucu sürümünü bildirir; NULLS NOT DISTINCT için gereken sürüm sınırı budur. Önceden Ayarlanmış Seçenekler

SHOW server_version;

Bu temel örneğin tamamını yeni bir oturumda bir kez çalıştırın. Normal benzersiz kısıtlaması PostgreSQL 14 ve sonraki sürümlerde çalışır; yalnızca geçici bir gösterim tablosu oluşturur.

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;

Görüntülenen tanım altında dört eklemenin tümü başarılı olmalıdır. İlk iki satırda is_sql_null = true, üçüncü satırda is_empty_string = true bulunur; dördüncü satır ise düz metin içerir. NULL içeren bir karşılaştırmanın sonucu da NULL olabilir; bu nedenle sonuçları yorumlamak için açık NULL olma sütununu kullanın. Kısıtlamalar, Karşılaştırma İşlevleri ve İşleçleri

Bu temel örnek, işe yarar bir ayraç sağlar. Uygulama başarısız olurken bu örnek başarılıysa, uygulamanın gerçek tablo tanımını ve gönderdiği değerleri temel örnekle karşılaştırın. Yalnızca bir sütunun NULL değer alabilmesi, hataya neden olan kuralı belirlemez.

Hatanın arkasındaki kuralı bulun

PostgreSQL’in unique_violation kodu olan SQLSTATE 23505 ile başlayın. Hata mesajının ifadesini eşleştirmek yerine kodu denetleyin. Ardından varsa bildirilen şemayı, tabloyu, kısıtlamayı ve ayrıntıyı kaydedin. PostgreSQL, CREATE UNIQUE INDEX ile doğrudan oluşturulmuş olsa bile, hata mesajındaki kısıtlama adı alanında dizini bir kısıtlama olarak ele alır. PostgreSQL Hata Kodları, Hata ve Bildirim Mesajı Alanları

Tanılama sorgusuyla dizinleri incelemek için temel örnekle aynı psql oturumunda kalın. pg_indexes görünümü her dizinin şemasını, adını ve yeniden oluşturulmuş tanımını sunar. Sorguyu uyarlarken hata tarafından bildirilen tabloyu ve şemayı eşleştirin. pg_indexes

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

Hata mesajında belirtilen adla başlayarak her benzersiz tanımı okuyun. Yinelenen bir article_id, slug NULL olsa bile birincil anahtarı ihlal eder. Birincil anahtarlar benzersiz ve NULL olmayan değerler gerektirir; satırın başka bir yerindeki NULL değer alabilen sütun bu gerekliliği esnetmez. Kısıtlamalar

Temel örnekten şu üç farkı arayın:

  • NULL eşitliği: NULLS NOT DISTINCT, yinelenen NULL anahtarların çakışmasına bilerek neden olur.
  • İfadeler: Benzersizlik, saklanan sütun yerine hesaplanmış bir değere uygulanabilir.
  • Koşullar: Kısmi dizin kuralını yalnızca WHERE koşulunu karşılayan satırlara uygular.

Bir ifade dizinini incelerken çakışmayı araştırmak için dizine eklenen ifadeyi yeniden oluşturun. Örneğin lower(slug) üzerindeki bir dizin, yalnızca büyük/küçük harfleri farklı olan NULL olmayan iki slug değerini reddedebilir. COALESCE(slug, '') üzerindeki bir dizin hem NULL hem de boş dizgeyi aynı dizin değerine dönüştürür; bunun sonucunda bu girdiler çakışabilir. Bu, ifade dizinlerindeki benzersizlikten ve COALESCE için belgelenen davranıştan kaynaklanır. İfadeler Üzerindeki Dizinler, Koşullu İfadeler

Kısmi benzersiz dizinler de dikkatle okunmalıdır. section üzerindeki ve WHERE slug IS NULL koşullu bir dizin, slug’ı NULL olan satırlar arasında bölüm başına bir eşleşen satıra izin verir. slug sütununun kendisinde benzersizliği sağlamaz. PostgreSQL belgelerine göre kısmi benzersiz dizinler, koşullarının seçtiği alt kümede benzersizliği sağlar. Kısmi Dizinler

node-postgres’in gerçekte ne gönderdiğini denetleyin

node-postgres, hem JavaScript null hem de undefined sorgu parametrelerini SQL NULL değerine dönüştürür. Boş dizgeler ve 'NULL' dizgesi normal metin girdileri olarak kalır. Ayrıca NULL parametresi gönderilmesiyle bir SQL sütununun atlanmasını birbirinden ayırın: PostgreSQL, atlanan sütunu tanımlı varsayılanıyla, varsayılanı yoksa NULL ile doldurur. Sorgular, INSERT

Aşağıdaki program bu farkları görünür kılar. Node.js, npm ve mevcut PostgreSQL bağlantı ayarlarınız gerekir. Çalışma klasörünüzde bağımlılığı yükleyin, programı diagnose-null.mjs adıyla kaydedin, ardından çalıştırın. İstemci standart PostgreSQL bağlantı ortam değişkenlerini kullanır. Bağlanma

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

Bu programın şeması altında, tanımlı varsayılana rağmen ilk iki satır SQL NULL saklamalıdır. İlk boş dizge ve ilk 'NULL' dizgesi eklenmelidir; bunların yinelenenleri 23505 hatasına neden olmalıdır. Program bu beklenen çakışmaları bildirir ve diğer hataları yeniden fırlatır. Tanılama özelliği adları node-postgres’in DatabaseError alanlarıyla eşleşir. Sorgular, DatabaseError kaynak kodu

Veritabanı örnekleri, hazırlık sırasında erişilen belgelere göre incelendi; ancak PostgreSQL’de çalıştırılmadı.

Eşleşen satırları kaçırmadan NULL değer alabilen anahtarları sorgulayın

slug = NULL kullanan bir tanılama sorgusu NULL slug değerlerini bulamaz. Belirli bir NULL araması için IS NULL kullanın veya değerin NULL ya da NULL olmayan bir değer olabileceği durumlarda IS NOT DISTINCT FROM kullanın. İkincisi, karşılaştırmada iki NULL değerini eşit kabul eder. Karşılaştırma İşlevleri ve İşleçleri

Bu tanılama sorgusu aynı oturumdaki önceki article_probe tablosunda çalışır. NULL slug içeren iki satırı döndürmelidir.

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

Parametreli bir arama için karşılık gelen koşul parçası WHERE slug IS NOT DISTINCT FROM $1::text şeklindedir. Bileşik bir kural için her anahtar bileşenini eşleştirin ve dizine eklenmiş ifadeyi veya kısmi dizin koşulunu yeniden oluşturun. Eşleşen NULL satırlarını bulmak, nelerin saklandığını gösterir; benzersizliği ihlal edip etmedikleri yine de dizin tanımına bağlıdır.

Amaçlanan kurala uygun düzeltmeyi seçin

Makalelerde istendiği kadar eksik slug bulunabiliyorsa, normal UNIQUE (slug) bu gereksinimi zaten ifade eder. Yanlışlıkla metin olarak girilmiş bir değeri, istenmeyen bir ifadeyi veya hatanın belirlediği diğer yinelenen anahtarı düzeltin. NULL işleme biçimini değiştirmek, başka bir anahtardaki çakışmayı çözmez.

Gereksinim, bölüm başına bir NULL slug ise PostgreSQL 15 veya sonrası bunu doğrudan ifade edebilir. Bu bağımsız gösterim, ilk satır bölüm-NULL birleşimini zaten kullandığından, 23505 hatası üretmesi beklenen bir eklemeyle bilerek sona erer. Sürüm 15, Benzersiz Dizinler

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’te bu iki sütunlu tasarım için kısmi benzersiz dizinler kullanın. Aşağıdaki bağımsız örnek aynı kuralı uygular; çünkü section NULL değildir ve slug NULL olabilen tek anahtar bileşenidir. Bu yaklaşım, PostgreSQL’in belgelenmiş kısmi dizin davranışından türetilmiştir. Kısmi Dizinler

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

İlk dizin, bir bölüm içinde dolu slug değerlerinin yinelenmesini önler. İkinci dizin, bölüm başına tek bir eksik slug değerine izin verir. NULL olabilen daha fazla anahtar bileşeni, olası birleşimlerinin ayrıca ele alınmasını gerektirir; bu iki dizinli yapı belirtilen şemaya özgüdür.

Son olarak çakışma işlemesini açıkça seçin. ON CONFLICT DO NOTHING çakışan eklemeyi atlar; temel değerleri düzeltmez. ON CONFLICT DO UPDATE bir çakışma hedefi gerektirir; bu hedefin sütunları, ifadeleri ve geçerli koşulu amaçlanan kuralı tanımlamalıdır. Bu işlemlerin hiçbiri, normal benzersizlik yinelenen NULL değerlerine izin verirken NULL çakışması oluşturmaz. INSERT

Bildirilen dizin adını tanımına bağlayabildiğinizde, gönderilen anahtar değerlerini yeniden oluşturabildiğinizde ve çakışmayı o kurala göre açıklayabildiğinizde tanılama tamamlanmış olur. Düzeltmeden sonra aynı girdiyi yeniden deneyin; amaçlanan makalenin hesaba katıldığından emin olmak için hem döndürülen sonucu hem de saklanan satırları denetleyin.

Kaynaklar