PostgreSQL staat normaal gesproken meerdere SQL-waarden NULL toe in een unieke kolom. Om een onverwachte fout door een dubbele sleutel te diagnosticeren, identificeert u de exacte index, inspecteert u de daadwerkelijk verzonden waarden en controleert u of de definitie ervan de manier waarop nullen worden vergeleken verandert. Unieke indexen

Deze handleiding is bedoeld voor iemand die artikelinvoegingen debugt op een Mac met psql of node-postgres. De handleiding gebruikt synthetische artikelrijen en biedt een diagnostische volgorde: reproduceer het standaardgedrag, traceer de falende regel en kies vervolgens de juiste correctie.

Wat staat een nullable unieke kolom eigenlijk toe?

Een nullable kolom kan NULL bevatten; een unieke beperking bepaalt welke combinaties mogen worden herhaald. Met gewone UNIQUE (slug) worden herhaalde niet-null-slugs geweigerd, terwijl herhaalde nullen zijn toegestaan. Met gewone UNIQUE (section, slug) staat een null in een van beide beperkte kolommen toe dat combinaties onder die beperking anderszins worden herhaald. Andere unieke regels voor de tabel blijven van toepassing. Beperkingen

PostgreSQL 15 introduceerde NULLS NOT DISTINCT, waarmee nullen bij de controle op uniciteit als gelijk worden behandeld. Bij een samengestelde sleutel moet de volledige combinatie overeenkomen: ('notes', NULL) botst met een andere ('notes', NULL), maar niet met ('guides', NULL). PostgreSQL 14 ondersteunt deze clausule niet. Release 15, Unieke indexen

Daadwerkelijke regel of invoer Kan een herhaalde waarde een fout door een dubbele sleutel veroorzaken?
Gewone UNIQUE (slug) met SQL NULL Nee, alleen onder deze regel
UNIQUE NULLS NOT DISTINCT (slug) Ja, op PostgreSQL 15 of hoger
Gewone UNIQUE (slug) met '' Ja; dit is een lege tekenreeks
Gewone UNIQUE (slug) met 'NULL' Ja; dit is tekst
Een andere unieke sleutel op dezelfde rij Ja, onafhankelijk van de nullable slug

SQL NULL, de lege tekenreeks en de tekst 'NULL' zijn verschillende invoerwaarden. De documentatie van PostgreSQL over vergelijkingen maakt expliciet onderscheid tussen null en gewone tekstwaarden; test op nullheid met IS NULL. Vergelijkingsfuncties en -operatoren

Reproduceer het standaardgedrag in Mac Terminal

U hebt een bestaande PostgreSQL-verbinding nodig en de opdracht psql moet beschikbaar zijn in Terminal. Open een sessie met de verbindingsinstellingen die u normaal gebruikt. Als uw lokale database bijvoorbeeld postgres heet, voert u het volgende uit. Met de optie -d selecteert u de database. psql

psql -d postgres

Controleer de versie van de verbonden server voordat u versiespecifieke syntaxis kiest. server_version geeft de serverversie weer, de relevante grens voor NULLS NOT DISTINCT. Vooraf ingestelde opties

SHOW server_version;

Voer dit volledige basisvoorbeeld eenmaal uit in een nieuwe sessie. De gewone unieke beperking werkt op PostgreSQL 14 en hoger; er wordt alleen een tijdelijke demonstratietabel aangemaakt.

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;

Alle vier de invoegingen zouden onder de getoonde definitie moeten slagen. De eerste twee rijen hebben is_sql_null = true; de derde heeft is_empty_string = true; de vierde bevat letterlijke tekst. Een vergelijking waarbij null betrokken is, kan zelf null opleveren. Gebruik daarom de expliciete kolom voor nullheid om de resultaten te interpreteren. Beperkingen, Vergelijkingsfuncties en -operatoren

Deze basislijn geeft u een nuttig onderscheidingspunt. Als de toepassing faalt terwijl dit voorbeeld slaagt, vergelijk dan de daadwerkelijke tabeldefinitie en de ingediende waarden van de toepassing met de basislijn. Alleen de aanwezigheid van een nullable kolom wijst de falende regel niet aan.

Zoek de exacte regel achter de fout

Begin met SQLSTATE 23505, de PostgreSQL-code voor unique_violation. Controleer de code in plaats van de formulering van het foutbericht te vergelijken. Noteer vervolgens het gerapporteerde schema, de tabel, de beperking en de details, indien beschikbaar. PostgreSQL behandelt een index als een beperking voor het veld met de beperkingsnaam in de foutmelding, ook wanneer deze rechtstreeks met CREATE UNIQUE INDEX is aangemaakt. PostgreSQL-foutcodes, Velden voor fout- en meldingsberichten

Inspecteer de indexen met deze diagnostische query in dezelfde psql-sessie als de basislijn. De weergave pg_indexes toont het schema en de naam van elke index, plus de gereconstrueerde definitie. Stem bij het aanpassen van de query zowel de tabel als het schema af op wat in de fout is gemeld. pg_indexes

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

Lees elke unieke definitie, te beginnen met de naam uit de foutmelding. Een dubbele article_id schendt de primaire sleutel, zelfs wanneer slug null is. Primaire sleutels vereisen unieke, niet-null-waarden; een nullable kolom elders in de rij maakt die vereiste niet minder streng. Beperkingen

Let op drie verschillen met de basislijn:

  • Gelijkheid van nullen: NULLS NOT DISTINCT zorgt er bewust voor dat herhaalde null-sleutels met elkaar botsen.
  • Expressies: uniciteit kan betrekking hebben op een berekende waarde in plaats van de opgeslagen kolom.
  • Predicaten: een gedeeltelijke index past de regel alleen toe op rijen die voldoen aan de voorwaarde WHERE.

Reproduceer bij een expressie-index de geïndexeerde expressie wanneer u het conflict onderzoekt. Een index op lower(slug) kan bijvoorbeeld twee niet-null-slugs weigeren die alleen verschillen in hoofdlettergebruik. Een index op COALESCE(slug, '') zet zowel null als de lege tekenreeks om in dezelfde geïndexeerde waarde; daardoor kunnen deze invoerwaarden met elkaar botsen. Dit volgt uit de uniciteit van expressie-indexen en het gedocumenteerde gedrag van COALESCE. Indexen op expressies, Voorwaardelijke expressies

Een gedeeltelijke unieke index moet ook zorgvuldig worden gelezen. Een index op section met WHERE slug IS NULL dwingt af dat er per sectie één overeenkomende rij is onder de rijen met null-slugs. De index dwingt geen uniciteit af voor slug zelf. PostgreSQL documenteert gedeeltelijke unieke indexen als indexen die uniciteit afdwingen binnen de subset die door hun predicaat wordt geselecteerd. Gedeeltelijke indexen

Controleer wat node-postgres daadwerkelijk verzendt

node-postgres converteert zowel JavaScript-null als undefined-queryparameters naar SQL-null. Lege tekenreeksen en de tekenreeks 'NULL' blijven gewone tekstwaarden. Maak ook onderscheid tussen een meegegeven null-parameter en een weggelaten SQL-kolom: PostgreSQL vult een weggelaten kolom met de gedeclareerde standaardwaarde, of met null als er geen standaardwaarde is. Query's, INSERT

Het volgende volledige programma maakt die verschillen zichtbaar. U hebt Node.js, npm en uw bestaande PostgreSQL-verbindingsinstellingen nodig. Installeer de afhankelijkheid in een werkmap, sla het programma op als diagnose-null.mjs en voer het daarna uit. De client gebruikt standaardomgevingsvariabelen voor PostgreSQL-verbindingen. Verbinding maken

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

Volgens het schema van dit programma zouden de eerste twee rijen SQL-null moeten opslaan, ondanks de gedeclareerde standaardwaarde. De eerste lege tekenreeks en de eerste tekenreeks 'NULL' zouden moeten worden ingevoegd; herhalingen ervan zouden 23505 moeten opleveren. Het programma meldt deze verwachte conflicten en gooit andere fouten opnieuw. De diagnostische eigenschapsnamen komen overeen met de velden van node-postgres’ DatabaseError. Query's, DatabaseError-broncode

De databasevoorbeelden zijn gecontroleerd aan de hand van de opgehaalde documentatie, maar zijn tijdens de voorbereiding niet uitgevoerd op PostgreSQL.

Doorzoek nullable sleutels zonder overeenkomende rijen te missen

Een diagnostische zoekopdracht met slug = NULL vindt geen null-slugs. Gebruik IS NULL voor een vaste null-zoekopdracht, of IS NOT DISTINCT FROM wanneer een waarde null of niet-null kan zijn. De laatste behandelt twee nullen als gelijk bij de vergelijking. Vergelijkingsfuncties en -operatoren

Deze diagnostische query wordt uitgevoerd op de eerder aangemaakte tabel article_probe in dezelfde sessie. De query zou de twee rijen met een null-slug moeten opleveren.

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

Voor een geparametriseerde zoekopdracht is het overeenkomstige predicaatfragment WHERE slug IS NOT DISTINCT FROM $1::text. Stem voor een samengestelde regel elk sleutelonderdeel af en reproduceer alle geïndexeerde expressies of predicaten van gedeeltelijke indexen. Als u overeenkomende null-rijen vindt, weet u wat er is opgeslagen; of ze de uniciteitsregel schenden, hangt nog steeds af van de indexdefinitie.

Kies de correctie die bij de bedoelde regel past

Als artikelen een willekeurig aantal ontbrekende slugs mogen hebben, drukt gewone UNIQUE (slug) die vereiste al uit. Corrigeer een onbedoelde tekstwaarde, een onbedoelde expressie of de andere dubbele sleutel die in de fout is genoemd. Het wijzigen van de nullafhandeling lost een botsing op een andere sleutel niet op.

Als de vereiste is om één null-slug per sectie toe te staan, kan PostgreSQL 15 of hoger dat rechtstreeks uitdrukken. Deze onafhankelijke demonstratie eindigt bewust met een invoeging die naar verwachting 23505 oplevert: de eerste rij gebruikt die combinatie van sectie en null al. Release 15, Unieke indexen

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

Gebruik op PostgreSQL 14 gedeeltelijke unieke indexen voor dit specifieke ontwerp met twee kolommen. Het volgende onafhankelijke voorbeeld implementeert dezelfde regel omdat section niet-null is en slug het enige nullable sleutelonderdeel is. Dit is een constructie die is afgeleid van het gedocumenteerde gedrag van gedeeltelijke indexen in PostgreSQL. Gedeeltelijke indexen

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

De eerste index voorkomt herhaalde ingevulde slugs binnen een sectie. De tweede staat één ontbrekende slug per sectie toe. Bij meer nullable sleutelonderdelen moet u aanvullende aandacht besteden aan de mogelijke combinaties; deze constructie met twee indexen is specifiek voor het genoemde schema.

Kies tot slot expliciet hoe conflicten worden afgehandeld. ON CONFLICT DO NOTHING slaat een conflicterende invoeging over; het corrigeert de onderliggende waarden niet. ON CONFLICT DO UPDATE vereist een conflictdoel waarvan de kolommen, expressies en toepasselijke predicaten de bedoelde regel moeten aanwijzen. Geen van beide acties creëert een null-conflict wanneer gewone uniciteit herhaalde nullen toestaat. INSERT

Een diagnose is compleet wanneer u de gemelde indexnaam kunt koppelen aan de definitie ervan, de ingediende sleutelwaarden kunt reproduceren en het conflict onder die regel kunt verklaren. Herhaal na de correctie exact die invoer en controleer zowel het geretourneerde resultaat als de opgeslagen rijen, zodat duidelijk is dat het bedoelde artikel is verwerkt.

Bronnen