PostgreSQL standardowo dopuszcza wiele wartości SQL NULL w kolumnie z unikatowym ograniczeniem. Aby zdiagnozować nieoczekiwany błąd dotyczący duplikatu klucza, ustal dokładny indeks, sprawdź faktycznie przesłane wartości i zweryfikuj, czy jego definicja zmienia sposób porównywania wartości null. Indeksy unikatowe

Ten przewodnik jest przeznaczony dla osób debugujących wstawianie artykułów z komputera Mac przy użyciu psql lub node-postgres. Wykorzystuje przykładowe wiersze artykułów i przedstawia sekwencję diagnostyczną: odtwórz domyślne zachowanie, prześledź regułę powodującą błąd, a następnie wybierz odpowiednią poprawkę.

Co faktycznie dopuszcza kolumna z unikatowym ograniczeniem, która zezwala na null?

Kolumna dopuszczająca null może zawierać NULL; ograniczenie unikatowości określa, które kombinacje mogą się powtarzać. Przy zwykłym UNIQUE (slug) powtórzone niepuste wartości slug są odrzucane, natomiast powtórzone wartości null są dozwolone. Przy zwykłym UNIQUE (section, slug) wartość null w dowolnej z objętych ograniczeniem kolumn pozwala na powtarzanie kombinacji w ramach tego ograniczenia. Nadal obowiązują inne reguły unikatowości zdefiniowane dla tabeli. Ograniczenia

PostgreSQL 15 wprowadził NULLS NOT DISTINCT, które traktuje wartości null jako równe podczas sprawdzania unikatowości. W przypadku klucza złożonego cała kombinacja musi się zgadzać: ('notes', NULL) powoduje konflikt z innym ('notes', NULL), ale nie z ('guides', NULL). PostgreSQL 14 nie obsługuje tej klauzuli. Wydanie 15, Indeksy unikatowe

Faktyczna reguła lub wartość wejściowa Czy powtórzona wartość może spowodować błąd dotyczący duplikatu klucza?
Zwykły UNIQUE (slug) z SQL NULL Nie, jeśli obowiązuje tylko ta reguła
UNIQUE NULLS NOT DISTINCT (slug) Tak, w PostgreSQL 15 lub nowszym
Zwykły UNIQUE (slug) z '' Tak; to pusty ciąg znaków
Zwykły UNIQUE (slug) z 'NULL' Tak; to tekst
Inny unikatowy klucz w tym samym wierszu Tak, niezależnie od wartości null w kolumnie slug

SQL NULL, pusty ciąg znaków i tekst 'NULL' to różne wartości wejściowe. Dokumentacja porównań PostgreSQL wyraźnie odróżnia null od zwykłych wartości tekstowych; null sprawdzaj za pomocą IS NULL. Funkcje i operatory porównania

Odtwórz domyślne zachowanie w Terminalu na Macu

Potrzebujesz istniejącego połączenia z PostgreSQL oraz dostępnego w Terminal polecenia psql. Użyj standardowych ustawień połączenia, aby otworzyć sesję. Na przykład, jeśli lokalna baza danych nazywa się postgres, uruchom poniższe polecenie. Opcja -d wskazuje bazę danych. psql

psql -d postgres

Zanim wybierzesz składnię zależną od wersji, sprawdź wersję podłączonego serwera. server_version podaje wersję serwera, która wyznacza właściwą granicę dla NULLS NOT DISTINCT. Opcje ustawień wstępnych

SHOW server_version;

Uruchom ten kompletny przykład bazowy raz w nowej sesji. Zwykłe ograniczenie unikatowości w tym przykładzie działa w PostgreSQL 14 i nowszym; tworzona jest wyłącznie tymczasowa tabela demonstracyjna.

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;

Wszystkie cztery operacje wstawiania powinny się powieść przy pokazanej definicji. Pierwsze dwa wiersze mają wartość is_sql_null = true; trzeci ma wartość is_empty_string = true; czwarty zawiera tekst dosłowny. Porównanie obejmujące null może samo zwrócić null, dlatego wyniki interpretuj na podstawie jawnej kolumny określającej null. Ograniczenia, Funkcje i operatory porównania

Ten przykład bazowy wyznacza przydatną granicę diagnostyczną. Jeśli aplikacja zgłasza błąd, a ten przykład działa, porównaj faktyczną definicję tabeli w aplikacji i przesłane wartości z przykładem bazowym. Sama obecność kolumny dopuszczającej null nie wskazuje, która reguła powoduje błąd.

Znajdź dokładną regułę powodującą błąd

Zacznij od SQLSTATE 23505, kodu PostgreSQL oznaczającego unique_violation. Sprawdź kod zamiast dopasowywać treść komunikatu o błędzie. Następnie zanotuj zgłoszony schemat, tabelę, ograniczenie oraz szczegóły, jeśli są dostępne. PostgreSQL używa nazwy indeksu jako nazwy ograniczenia w polu komunikatu o błędzie, nawet gdy indeks utworzono bezpośrednio poleceniem CREATE UNIQUE INDEX. Kody błędów PostgreSQL, Pola komunikatów o błędach i powiadomieniach

W tej samej sesji psql, w której uruchomiono przykład bazowy, sprawdź indeksy za pomocą tego zapytania diagnostycznego. Widok pg_indexes udostępnia schemat i nazwę każdego indeksu oraz jego odtworzoną definicję. Dostosowując zapytanie, dopasuj zarówno tabelę, jak i schemat zgłoszone w błędzie. pg_indexes

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

Sprawdź wszystkie definicje unikatowości, zaczynając od nazwy podanej w błędzie. Duplikat article_id narusza klucz główny, nawet gdy slug ma wartość null. Klucze główne wymagają unikatowych wartości innych niż null; kolumna dopuszczająca null w innym miejscu wiersza nie znosi tego wymogu. Ograniczenia

Szukaj trzech różnic względem przykładu bazowego:

  • Równość wartości null: NULLS NOT DISTINCT celowo powoduje konflikt powtarzających się kluczy null.
  • Wyrażenia: unikatowość może dotyczyć obliczonej wartości, a nie zapisanej kolumny.
  • Predykaty: indeks częściowy stosuje regułę tylko do wierszy spełniających warunek WHERE.

W przypadku indeksu opartego na wyrażeniu odtwórz indeksowane wyrażenie podczas analizowania konfliktu. Na przykład indeks na lower(slug) może odrzucić dwa niepuste slugi różniące się wyłącznie wielkością liter. Indeks na COALESCE(slug, '') przekształca zarówno null, jak i pusty ciąg znaków w tę samą indeksowaną wartość; w konsekwencji te wartości wejściowe mogą kolidować. Wynika to z unikatowości indeksów opartych na wyrażeniach oraz udokumentowanego zachowania COALESCE. Indeksy oparte na wyrażeniach, Wyrażenia warunkowe

Indeks częściowy z unikatowością również wymaga uważnej analizy. Indeks na section z warunkiem WHERE slug IS NULL wymusza, aby dla każdej sekcji istniał najwyżej jeden pasujący wiersz z null w kolumnie slug. Nie wymusza unikatowości samej wartości slug. Dokumentacja PostgreSQL opisuje indeksy częściowe z unikatowością jako wymuszające unikatowość w podzbiorze wybranym przez predykat. Indeksy częściowe

Sprawdź, co faktycznie wysyła node-postgres

node-postgres zamienia zarówno parametry zapytania JavaScript null, jak i undefined na wartości SQL null. Puste ciągi znaków i ciąg 'NULL' pozostają zwykłymi wartościami tekstowymi. Odróżnij też podany parametr null od pominiętej kolumny SQL: PostgreSQL przypisuje pominiętej kolumnie zadeklarowaną wartość domyślną, a jeśli jej nie ma — null. Zapytania, INSERT

Poniższy kompletny program pokazuje te różnice. Wymaga Node.js, npm i używanych przez Ciebie ustawień połączenia z PostgreSQL. W katalogu roboczym zainstaluj zależność, zapisz program jako diagnose-null.mjs, a następnie go uruchom. Klient korzysta ze standardowych zmiennych środowiskowych połączenia z PostgreSQL. Łączenie

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

Zgodnie ze schematem używanym przez ten program pierwsze dwa wiersze powinny zapisać wartość SQL null mimo zadeklarowanej wartości domyślnej. Wstawienie pierwszego pustego ciągu i pierwszego ciągu 'NULL' powinno się powieść; ich powtórzenia powinny spowodować 23505. Program zgłasza te oczekiwane konflikty i ponownie wyrzuca inne błędy. Nazwy właściwości diagnostycznych odpowiadają polom DatabaseError w node-postgres. Zapytania, Kod źródłowy DatabaseError

Przykłady baz danych sprawdzono na podstawie pobranej dokumentacji, ale podczas przygotowywania materiału nie uruchomiono ich w PostgreSQL.

Wykonuj zapytania o klucze dopuszczające null, nie pomijając pasujących wierszy

Zapytanie diagnostyczne używające slug = NULL nie znajdzie wierszy z wartością null w kolumnie slug. Przy wyszukiwaniu konkretnej wartości null użyj IS NULL, a gdy wartością może być null lub wartość nie-null, użyj IS NOT DISTINCT FROM. Ten drugi operator traktuje dwie wartości null jako równe w porównaniu. Funkcje i operatory porównania

To zapytanie diagnostyczne działa na wcześniej utworzonej tabeli article_probe w tej samej sesji. Powinno zwrócić dwa wiersze z wartością null w kolumnie slug.

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

W zapytaniu parametryzowanym odpowiedni fragment predykatu to WHERE slug IS NOT DISTINCT FROM $1::text. W przypadku reguły dotyczącej klucza złożonego dopasuj wszystkie jego składowe i odtwórz każde indeksowane wyrażenie lub predykat indeksu częściowego. Znalezienie pasujących wierszy z wartością null potwierdza, co jest zapisane w bazie; to, czy narusza to unikatowość, nadal zależy od definicji indeksu.

Wybierz poprawkę zgodną z zamierzoną regułą

Jeśli artykuły mogą mieć dowolną liczbę brakujących wartości slug, zwykłe UNIQUE (slug) już wyraża to wymaganie. Popraw przypadkową wartość tekstową, niezamierzone wyrażenie lub inny zduplikowany klucz wskazany w błędzie. Zmiana obsługi wartości null nie rozwiąże kolizji dotyczącej innego klucza.

Jeśli wymaganie stanowi, że dla każdej sekcji ma istnieć najwyżej jeden null w kolumnie slug, PostgreSQL 15 lub nowszy pozwala wyrazić je bezpośrednio. Ta niezależna demonstracja celowo kończy się operacją wstawiania, która powinna spowodować 23505: pierwszy wiersz już zajmuje tę kombinację sekcji i wartości null. Wydanie 15, Indeksy unikatowe

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

W PostgreSQL 14 w tej konkretnej konstrukcji obejmującej dwie kolumny użyj unikatowych indeksów częściowych. Poniższy niezależny przykład wprowadza tę samą regułę, ponieważ section nie dopuszcza wartości null, a slug jest jedynym składnikiem klucza, który ją dopuszcza. To rozwiązanie wynika z udokumentowanego zachowania indeksów częściowych w PostgreSQL. Indeksy częściowe

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

Pierwszy indeks zapobiega powtarzaniu się niepustych wartości slug w obrębie jednej sekcji. Drugi dopuszcza jeden brakujący slug w każdej sekcji. Przy większej liczbie składników klucza dopuszczających null trzeba rozważyć ich możliwe kombinacje; ta konstrukcja z dwoma indeksami dotyczy wyłącznie opisanego schematu.

Na koniec jawnie wybierz sposób obsługi konfliktu. ON CONFLICT DO NOTHING pomija konfliktujące wstawienie; nie naprawia wartości źródłowych. ON CONFLICT DO UPDATE wymaga określenia celu konfliktu, którego kolumny, wyrażenia i obowiązujący predykat muszą wskazywać zamierzoną regułę. Żadne z tych działań nie tworzy konfliktu wartości null, jeśli zwykła unikatowość dopuszcza ich powtórzenia. INSERT

Diagnoza jest zakończona, gdy potrafisz powiązać zgłoszoną nazwę indeksu z jego definicją, odtworzyć przesłane wartości klucza i wyjaśnić kolizję wynikającą z tej reguły. Po poprawce ponów próbę z dokładnie tymi samymi wartościami wejściowymi, sprawdzając zarówno zwrócony wynik, jak i zapisane wiersze, aby upewnić się, że zamierzony artykuł został uwzględniony.

Źródła