PostgreSQL non può usare ON CONFLICT DO UPDATE come operazione generica «corrispondi a una qualsiasi delle chiavi univoche». Scegli una chiave che definisca l’identità oppure controlla esplicitamente entrambe le chiavi in una transazione e rifiuta gli input che identificano due righe diverse.

Questo tutorial aiuta uno sviluppatore che lavora nel Terminale su un Mac a implementare la seconda strategia con node-postgres: inserire quando nessuna delle due chiavi corrisponde, aggiornare quando corrisponde esattamente un articolo e restituire un conflitto esplicito quando le chiavi puntano ad articoli diversi. Tutte le righe sono sintetiche.

Gli esempi SQL si riferiscono a PostgreSQL 18, la versione documentata nella documentazione corrente consultata. L’implementazione usa una tabella ordinaria, non partizionata, con due vincoli univoci immediati. Prima di adattare esempi tratti dal manuale di un’altra versione, controlla la versione principale del tuo server. Documentazione PostgreSQL 18 su INSERT

Perché due vincoli univoci non possono diventare un unico criterio di conflitto

Supponiamo che un articolo abbia un riferimento esterno e uno slug, ciascuno dei quali deve essere univoco indipendentemente dall’altro. Inizia con questa configurazione completa in un database dimostrativo vuoto; salvala come setup.sql.

CREATE TABLE articles (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_key text NOT NULL,
    slug text NOT NULL,
    title text NOT NULL,
    CONSTRAINT articles_external_key_key UNIQUE (external_key),
    CONSTRAINT articles_slug_key UNIQUE (slug)
);

INSERT INTO articles (external_key, slug, title)
VALUES
    ('feed-101', 'blue-notes', 'Blue notes'),
    ('feed-202', 'green-notes', 'Green notes');

Queste dichiarazioni applicano due regole separate. Sostituirle con UNIQUE (external_key, slug) cambierebbe il modello: dovrebbe essere univoca solo la coppia, consentendo chiavi esterne ripetute e slug ripetuti in combinazioni diverse. Mantenere entrambe le colonne NOT NULL evita anche di introdurre in questo esempio il comportamento delle chiavi con valori nulli. Vincoli PostgreSQL

Per DO UPDATE, PostgreSQL richiede un criterio di conflitto. Un elenco di colonne descrive le colonne di un indice univoco corrispondente; non significa «una qualsiasi delle colonne». Di conseguenza, ON CONFLICT (external_key, slug) non può ricavare un arbitro dai due vincoli separati qui sopra. Specificare un vincolo seleziona quel vincolo e la sintassi prevede una sola clausola ON CONFLICT, non una sequenza di gestori alternativi. INSERT di PostgreSQL: clausola ON CONFLICT

Il problema più profondo è decidere quale articolo esistente rappresenti la riga in arrivo. Considera i valori in arrivo feed-101 e green-notes: la chiave esterna appartiene all’articolo blu, mentre lo slug appartiene a quello verde. Aggiornare una delle due righe con entrambe le chiavi in arrivo entrerebbe in conflitto con l’altra. Scegliere la prima corrispondenza nasconderebbe una decisione irrisolta sull’input.

Decidi cosa fare per ogni combinazione di corrispondenze

Prima di scrivere la query, usa la seguente tabella decisionale. Si tratta di una strategia applicativa proposta in base a questo schema, non di un comportamento fornito automaticamente da PostgreSQL.

Chiave esterna in arrivo Slug in arrivo Righe corrispondenti distinte Azione in questo tutorial
Non utilizzata Non utilizzato 0 Inserire un articolo
Corrisponde a un articolo Non utilizzato 1 Aggiornare quell’articolo, incluso lo slug
Non utilizzata Corrisponde a un articolo 1 Aggiornare quell’articolo, inclusa la chiave esterna
Corrisponde all’articolo A Corrisponde all’articolo A 1 Aggiornare l’articolo A
Corrisponde all’articolo A Corrisponde all’articolo B 2 Rifiutare l’input; conservare entrambi gli articoli

Nei casi con una sola corrispondenza, si consente deliberatamente a entrambe le chiavi di identificare un articolo e si permette di modificare l’altra chiave. Verifica che sia il comportamento richiesto dalla tua importazione. Se una chiave esterna non deve mai cambiare, la corrispondenza basata solo sullo slug dovrebbe invece generare un conflitto applicativo.

Se la regola effettiva è più semplice — «l’articolo è sempre determinato dalla chiave esterna» — usa un normale upsert mirato. Questa istruzione completa viene eseguita dopo la configurazione precedente:

INSERT INTO articles (external_key, slug, title)
VALUES ('feed-101', 'blue-notes', 'Revised blue notes')
ON CONFLICT (external_key) DO UPDATE
SET slug = EXCLUDED.slug,
    title = EXCLUDED.title
RETURNING id, external_key, slug, title;

Qui EXCLUDED fornisce i valori proposti. L’altro vincolo univoco resta valido: se lo slug proposto viene cambiato in green-notes, si verifica un conflitto di unicità indipendente, invece di passare all’altro articolo. La garanzia dell’upsert mirato di eseguire atomicamente un inserimento o un aggiornamento è subordinata all’assenza di errori indipendenti. Documentazione PostgreSQL su INSERT

Mantieni stabile la decisione basata su una delle due chiavi durante le scritture simultanee

Una ricerca seguita da un inserimento crea un intervallo tra la decisione e la scrittura. In quell’intervallo, un’altra transazione potrebbe inserire una chiave corrispondente. Aggiungere FOR UPDATE alla ricerca blocca le righe recuperate; quando non corrisponde alcuna riga, non c’è una riga recuperata da bloccare. Per questo, il solo blocco delle righe non è sufficiente per questo specifico percorso senza corrispondenze. Blocco esplicito in PostgreSQL

Per una piccola importazione o un’operazione desktop occasionale, una soluzione semplice consiste nell’acquisire un blocco di tabella SHARE ROW EXCLUSIVE prima di controllare entrambe le chiavi. Questa modalità è in conflitto con il blocco ROW EXCLUSIVE acquisito dai normali inserimenti, aggiornamenti ed eliminazioni, e anche con un’altra istanza della stessa modalità. Le normali letture restano compatibili. Modalità di blocco a livello di tabella in PostgreSQL

La sequenza risultante è: avviare la transazione, acquisire il blocco di tabella, trovare le righe distinte corrispondenti, applicare la tabella decisionale e confermare la transazione. Il blocco esplicito attende i blocchi incompatibili e rimane attivo fino alla fine della transazione. LOCK di PostgreSQL

Qui imposta esplicitamente READ COMMITTED. La ricerca vede i dati confermati prima del suo inizio, incluse le scritture completate mentre l’acquisizione del blocco era in attesa. Combinare questo comportamento degli snapshot con il blocco di tabella acquisito dà a questa implementazione una base stabile per decidere e scrivere. Isolamento delle transazioni in PostgreSQL

È un blocco ampio: anche le scritture di articoli non correlate restano in attesa. Mantieni breve la transazione, senza richieste interattive né operazioni esterne tra il blocco e la conferma. Per importazioni simultanee frequenti, valuta se una sola chiave canonica possa definire l’identità prima di adottare questa soluzione.

Implementa la transazione con node-postgres

Ti servono un database dimostrativo PostgreSQL 18 in esecuzione, il comando psql e una versione di Node.js supportata dal pacchetto node-postgres installato. Nel Terminale, installa il pacchetto e carica il file di configurazione. Il primo comando crea i metadati del pacchetto se la directory dimostrativa non li contiene già. Installazione e compatibilità di node-postgres

npm init -y
npm install pg
psql -v ON_ERROR_STOP=1 -f setup.sql

Questi comandi presuppongono che le normali impostazioni di connessione locale selezionino già il database dimostrativo. Il programma usa new Pool(), che legge le stesse variabili d’ambiente per la connessione PostgreSQL usate da psql; se necessario, imposta PGDATABASE sul nome del database dimostrativo. Connessione con node-postgres

Salva questo programma completo come upsert-articles.mjs. Presuppone che la tabella di configurazione non abbia trigger o regole aggiuntivi che modifichino il comportamento di corrispondenza o aggiornamento. I valori usano parametri posizionali forniti separatamente dal testo SQL. Query con node-postgres

import pg from 'pg';
const { Pool } = pg;
const pool = new Pool();

async function upsertArticle(input) {
  const client = await pool.connect();
  let discardClient = false;

  try {
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    await client.query(
      'LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE'
    );

    const matches = await client.query(
      `SELECT id FROM articles
       WHERE external_key = $1 OR slug = $2
       ORDER BY id`,
      [input.externalKey, input.slug]
    );

    if (matches.rows.length > 1) {
      const error = new Error(
        'Incoming keys identify two different articles'
      );
      error.code = 'ARTICLE_KEY_CONFLICT';
      throw error;
    }

    const values = [input.externalKey, input.slug, input.title];
    const result = matches.rows.length === 0
      ? await client.query(
          `INSERT INTO articles (external_key, slug, title)
           VALUES ($1, $2, $3)
           RETURNING id, external_key, slug, title`,
          values
        )
      : await client.query(
          `UPDATE articles
           SET external_key = $1, slug = $2, title = $3
           WHERE id = $4
           RETURNING id, external_key, slug, title`,
          [...values, matches.rows[0].id]
        );

    if (result.rows.length !== 1) {
      throw new Error('Expected exactly one article');
    }

    await client.query('COMMIT');
    return result.rows[0];
  } catch (error) {
    try {
      await client.query('ROLLBACK');
    } catch {
      discardClient = true;
    }
    throw error;
  } finally {
    client.release(discardClient);
  }
}

try {
  console.log(await upsertArticle({
    externalKey: 'feed-101',
    slug: 'blue-notes',
    title: 'Revised blue notes'
  }));
} finally {
  await pool.end();
}

Ogni istruzione della transazione usa lo stesso client acquisito, come richiesto da node-postgres. Inviare queste istruzioni tramite chiamate pool.query() separate non conserverebbe tale requisito di connessione. Transazioni con node-postgres

Il blocco finally rilascia sempre il client acquisito. Se anche il rollback non riesce, release(true) scarta la connessione. L’pool.end() esterno chiude il pool quando termina lo script desktop. API Pool di node-postgres

Esegui il programma con node upsert-articles.mjs. Il risultato previsto è l’articolo blu con il titolo aggiornato e le chiavi invariate. La sintassi JavaScript è stata verificata con Node.js; gli esempi di database sono stati confrontati con la documentazione, ma qui non sono stati eseguiti su un server PostgreSQL.

Verifica ogni ramo e gestisci esplicitamente gli errori

Per usare le righe della configurazione, modifica l’oggetto passato a upsertArticle() per provare i seguenti casi. Esegui ogni caso partendo dallo stato iniziale della configurazione oppure tieni conto delle modifiche apportate dai casi precedenti.

externalKey slug Risultato previsto
feed-303 amber-notes Inserisce un nuovo articolo
feed-101 blue-revised Aggiorna l’articolo blu e sostituisce il suo slug
feed-303 blue-notes Aggiorna l’articolo blu e sostituisce la sua chiave esterna
feed-101 blue-notes Aggiorna il titolo dell’articolo blu
feed-101 green-notes Genera ARTICLE_KEY_CONFLICT; non modifica nessuno dei due articoli

La ricerca restituisce righe, quindi un articolo che corrisponde a entrambe le condizioni viene conteggiato una sola volta. Con queste due chiavi univoche indipendenti e non nulle, possono corrispondere al massimo due righe distinte. Il codice non risolve mai un risultato di due righe con LIMIT 1, eliminando un articolo o ignorando silenziosamente l’input.

ARTICLE_KEY_CONFLICT è un codice definito dall’applicazione. In PostgreSQL, 23505 indica unique_violation: è una condizione distinta. Se quest’ultima si verifica dopo aver adattato l’esempio, controlla il vincolo violato e ogni comportamento aggiunto alla tabella, invece di trattare tutti i duplicati allo stesso modo. PostgreSQL consiglia di controllare i codici di errore anziché analizzare il testo dei messaggi. Codici di errore PostgreSQL

L’acquisizione del blocco può richiedere un’attesa. Se per questa operazione desktop preferisci un errore immediato, sostituisci l’istruzione di blocco con LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE NOWAIT. PostgreSQL genera quindi un errore se non può acquisire subito il blocco e il programma segue il percorso di rollback. LOCK di PostgreSQL

DO NOTHING o MERGE semplificherebbero l’operazione?

ON CONFLICT DO NOTHING senza un criterio può ignorare un inserimento in conflitto con qualsiasi vincolo o indice univoco utilizzabile. Sceglilo quando il risultato previsto è ignorare l’inserimento; non implementa la strategia di aggiornamento sviluppata qui. Documentazione PostgreSQL su INSERT

MERGE è stato introdotto in PostgreSQL 15. Sebbene consenta una condizione di join che coinvolga una delle due chiavi, la join richiede comunque una strategia per una riga sorgente che corrisponde a due articoli di destinazione diversi. Le sue azioni restano soggette ai vincoli ordinari e il suo comportamento con scritture simultanee non è intercambiabile con quello dell’upsert mirato. Note di rilascio di PostgreSQL 15, MERGE in PostgreSQL

Per l’operazione basata su una delle due chiavi, rendi esplicito il conteggio delle corrispondenze: zero inserisce, una aggiorna e due interrompe l’operazione con un conflitto utile. Mantieni i vincoli univoci e scegli una strategia di concorrenza che preservi questa decisione fino al completamento della scrittura. L’implementazione con blocco di tabella è un punto di partenza concreto quando è accettabile serializzare le scritture degli articoli.

Fonti