Di norma PostgreSQL consente più valori SQL NULL in una colonna univoca. Per diagnosticare un errore imprevisto di chiave duplicata, individua l’indice esatto, esamina i valori effettivamente inviati e verifica se la sua definizione modifica il confronto dei null. Indici univoci

Questa guida è rivolta a chi sta eseguendo il debug di inserimenti di articoli da un Mac usando psql o node-postgres. Usa righe di articoli sintetiche e propone una sequenza diagnostica: riprodurre il comportamento predefinito, individuare la regola che causa l’errore e poi scegliere la correzione appropriata.

Cosa consente davvero una colonna nullable univoca?

Una colonna nullable può contenere NULL; un vincolo di unicità determina quali combinazioni possono ripetersi. Con un normale UNIQUE (slug), gli slug non null ripetuti vengono rifiutati, mentre i null ripetuti sono consentiti. Con un normale UNIQUE (section, slug), un null in una delle colonne soggette al vincolo consente che combinazioni altrimenti ripetute siano ammesse da quel vincolo. Restano validi gli altri vincoli di unicità della tabella. Vincoli

PostgreSQL 15 ha introdotto NULLS NOT DISTINCT, che considera uguali i null durante il controllo dell’unicità. Per una chiave composta, deve corrispondere l’intera combinazione: ('notes', NULL) è in conflitto con un altro ('notes', NULL), ma non con ('guides', NULL). PostgreSQL 14 non supporta questa clausola. Versione 15, Indici univoci

Regola o input effettivo Un valore ripetuto può causare un errore di chiave duplicata?
Normale UNIQUE (slug) con SQL NULL No, se si considera solo questa regola
UNIQUE NULLS NOT DISTINCT (slug) Sì, con PostgreSQL 15 o versioni successive
Normale UNIQUE (slug) con '' Sì; è una stringa vuota
Normale UNIQUE (slug) con 'NULL' Sì; è testo
Un’altra chiave univoca nella stessa riga Sì, indipendentemente dallo slug nullable

SQL NULL, la stringa vuota e il testo 'NULL' sono input diversi. La documentazione di PostgreSQL sui confronti distingue esplicitamente il null dai normali valori testuali; per verificare la nullità usa IS NULL. Funzioni e operatori di confronto

Riprodurre il comportamento predefinito nel Terminale del Mac

Ti serve una connessione PostgreSQL esistente e il comando psql disponibile in Terminal. Apri una sessione usando le impostazioni di connessione che usi normalmente. Per esempio, se il tuo database locale si chiama postgres, esegui il comando seguente. L’opzione -d seleziona il database. psql

psql -d postgres

Controlla la versione del server connesso prima di scegliere una sintassi specifica per una versione. server_version restituisce la versione del server, il requisito rilevante per NULLS NOT DISTINCT. Opzioni predefinite

SHOW server_version;

Esegui una volta questo esempio completo di riferimento in una sessione nuova. Il suo normale vincolo di unicità funziona con PostgreSQL 14 e versioni successive; crea solo una tabella dimostrativa temporanea.

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;

Tutti e quattro gli inserimenti dovrebbero riuscire con la definizione mostrata. Le prime due righe hanno is_sql_null = true; la terza ha is_empty_string = true; la quarta contiene testo letterale. Un confronto che coinvolge un null può restituire a sua volta null, quindi usa la colonna esplicita di nullità per interpretare i risultati. Vincoli, Funzioni e operatori di confronto

Questo esempio di riferimento fornisce un utile punto di distinzione. Se l’applicazione fallisce mentre questo esempio riesce, confronta la definizione effettiva della tabella nell’applicazione e i valori inviati con l’esempio. La sola presenza di una colonna nullable non identifica la regola che causa l’errore.

Individuare la regola esatta che causa l’errore

Inizia con SQLSTATE 23505, il codice PostgreSQL per unique_violation. Controlla il codice invece di cercare corrispondenze nella formulazione del messaggio di errore. Poi annota lo schema, la tabella, il vincolo e i dettagli riportati, se disponibili. PostgreSQL usa il nome del vincolo come campo relativo al vincolo anche per un indice, persino quando è stato creato direttamente con CREATE UNIQUE INDEX. Codici di errore PostgreSQL, Campi dei messaggi di errore e avviso

Nella stessa sessione psql usata per l’esempio di riferimento, esamina gli indici con questa query diagnostica. La vista pg_indexes mostra lo schema, il nome e la definizione ricostruita di ciascun indice. Quando adatti la query, specifica sia la tabella sia lo schema riportati nell’errore. pg_indexes

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

Esamina tutte le definizioni univoche, a partire dal nome riportato nell’errore. Un valore article_id duplicato viola la chiave primaria anche quando slug è null. Le chiavi primarie richiedono valori univoci e non null; una colonna nullable in un’altra parte della riga non attenua questo requisito. Vincoli

Cerca tre differenze rispetto all’esempio di riferimento:

  • Uguaglianza dei null: NULLS NOT DISTINCT fa entrare deliberatamente in conflitto le chiavi null ripetute.
  • Espressioni: l’unicità può applicarsi a un valore calcolato anziché alla colonna memorizzata.
  • Predicati: un indice parziale applica la propria regola solo alle righe che soddisfano la condizione WHERE.

Per un indice basato su un’espressione, riproduci l’espressione indicizzata mentre indaghi sul conflitto. Per esempio, un indice su lower(slug) può rifiutare due slug non null che differiscono solo per le maiuscole. Un indice su COALESCE(slug, '') converte sia il null sia la stringa vuota nello stesso valore indicizzato; di conseguenza, questi input possono entrare in conflitto. Ciò deriva dall’unicità degli indici basati su espressioni e dal comportamento documentato di COALESCE. Indici su espressioni, Espressioni condizionali

Anche un indice univoco parziale richiede un’attenta lettura. Un indice su section con WHERE slug IS NULL impone una sola riga corrispondente per sezione tra le righe con slug null. Non impone invece l’unicità di slug in sé. PostgreSQL documenta che gli indici univoci parziali impongono l’unicità all’interno del sottoinsieme selezionato dal loro predicato. Indici parziali

Verificare cosa invia davvero node-postgres

node-postgres converte in null SQL sia i parametri di query JavaScript null sia undefined. Le stringhe vuote e la stringa 'NULL' restano normali input testuali. Distingui inoltre un parametro null fornito da una colonna SQL omessa: PostgreSQL assegna alla colonna omessa il valore predefinito dichiarato, oppure null se non esiste un valore predefinito. Query, INSERT

Il programma completo seguente rende visibili queste differenze. Richiede Node.js, npm e le impostazioni di connessione PostgreSQL già in uso. In una cartella di lavoro, installa la dipendenza, salva il programma come diagnose-null.mjs e poi eseguilo. Il client usa le normali variabili d’ambiente per la connessione a PostgreSQL. Connessione

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

Con lo schema di questo programma, le prime due righe dovrebbero memorizzare null SQL nonostante il valore predefinito dichiarato. La prima stringa vuota e la prima stringa 'NULL' dovrebbero essere inserite; le ripetizioni dovrebbero produrre 23505. Il programma segnala questi conflitti previsti e rilancia gli altri errori. I nomi delle proprietà diagnostiche corrispondono ai campi DatabaseError di node-postgres. Query, Sorgente di DatabaseError

Gli esempi di database sono stati confrontati con la documentazione recuperata, ma non sono stati eseguiti su PostgreSQL durante la preparazione.

Interrogare le chiavi nullable senza perdere le righe corrispondenti

Una ricerca diagnostica con slug = NULL non troverà gli slug null. Usa IS NULL per una ricerca specifica di null, oppure IS NOT DISTINCT FROM quando il valore può essere null o non null. Quest’ultimo considera uguali due null nel confronto. Funzioni e operatori di confronto

Questa query diagnostica viene eseguita sulla tabella article_probe creata in precedenza nella stessa sessione. Dovrebbe restituire le due righe con slug null.

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

Per una ricerca parametrizzata, il frammento di predicato corrispondente è WHERE slug IS NOT DISTINCT FROM $1::text. Per una regola composta, specifica ogni componente della chiave e riproduci qualsiasi espressione indicizzata o predicato di indice parziale. Trovare le righe null corrispondenti conferma cosa è memorizzato; stabilire se violano l’unicità dipende comunque dalla definizione dell’indice.

Scegliere la correzione che corrisponde alla regola prevista

Se gli articoli possono avere un numero qualsiasi di slug mancanti, il normale UNIQUE (slug) esprime già questo requisito. Correggi un valore testuale accidentale, un’espressione non prevista o l’altra chiave duplicata identificata dall’errore. Modificare la gestione dei null non risolverà un conflitto su una chiave diversa.

Se il requisito è consentire un solo slug null per sezione, PostgreSQL 15 o versioni successive possono esprimerlo direttamente. Questo esempio indipendente termina intenzionalmente con un inserimento che dovrebbe produrre 23505: la prima riga occupa già la combinazione sezione e null. Versione 15, Indici univoci

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

Con PostgreSQL 14, usa indici univoci parziali per questo particolare schema a due colonne. Il seguente esempio indipendente implementa la stessa regola perché section non è null e slug è l’unico componente nullable della chiave. Si tratta di una soluzione derivata dal comportamento documentato degli indici parziali in PostgreSQL. Indici parziali

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

Il primo indice impedisce di ripetere gli slug valorizzati all’interno di una sezione. Il secondo consente uno slug mancante per sezione. Se la chiave contiene altri componenti nullable, occorre valutare con maggiore attenzione le possibili combinazioni; questa soluzione con due indici è specifica dello schema descritto.

Infine, scegli esplicitamente come gestire i conflitti. ON CONFLICT DO NOTHING salta un inserimento in conflitto; non corregge i valori sottostanti. ON CONFLICT DO UPDATE richiede un obiettivo di conflitto, le cui colonne, espressioni e il cui eventuale predicato devono identificare la regola prevista. Nessuna delle due azioni crea un conflitto sui null dove l’unicità ordinaria consente null ripetuti. INSERT

La diagnosi è completa quando sai associare il nome dell’indice riportato alla sua definizione, riprodurre i valori della chiave inviati e spiegare il conflitto in base a quella regola. Dopo la correzione, ripeti esattamente lo stesso input e controlla sia il risultato restituito sia le righe memorizzate, per verificare che l’articolo previsto sia stato registrato.

Fonti