Menu

Upsert PostgreSQL con INSERT ... ON CONFLICT

Upsert in PostgreSQL con INSERT ... ON CONFLICT: DO UPDATE con EXCLUDED, DO NOTHING, update condizionali, contatori e come distinguere le righe inserite da quelle aggiornate.

Questa pagina include editor eseguibili: modifica, esegui e vedi subito l'output.

PostgreSQL non ha una parola chiave UPSERT. Scrivi INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column. Quando la nuova riga violerebbe un vincolo di unicità su quella colonna, PostgreSQL aggiorna invece la riga esistente. DO NOTHING la salta.

A-100 esisteva già, quindi il suo prezzo è passato a 12.5. B-200 era nuovo ed è stato inserito. Una sola istruzione gestisce entrambi i casi, e resta corretta quando altre sessioni inseriscono le stesse chiavi nello stesso momento.

Inserire o aggiornare una riga

L'istruzione ha tre parti:

INSERT INTO table_name (columns) VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = value [, ...]
[WHERE condition];
  • Il target del conflitto indica le colonne di un indice o vincolo univoco, come la chiave primaria. Deve corrispondere esattamente a uno di essi, altrimenti PostgreSQL solleva "there is no unique or exclusion constraint matching the ON CONFLICT specification". Puoi indicare invece il nome del vincolo: ON CONFLICT ON CONSTRAINT products_pkey.
  • EXCLUDED è la riga che hai provato a inserire. EXCLUDED.price è il nuovo prezzo, e products.price è il prezzo già salvato.
  • DO UPDATE SET modifica solo le colonne che elenchi. Le altre colonne della riga esistente mantengono i loro valori.

Anche un indice univoco con una clausola WHERE (un indice parziale) funziona come target, se ne ripeti la condizione: ON CONFLICT (email) WHERE deleted_at IS NULL.

Saltare i duplicati con DO NOTHING

DO NOTHING inserisce le righe nuove e scarta quelle in conflitto, senza errore. Senza un target del conflitto salta una riga che viola qualsiasi vincolo di unicità.

RETURNING elenca solo lin@example.com. Una riga saltata non viene restituita, quindi DO NOTHING ... RETURNING mostra quali righe erano nuove.

Incrementare un contatore

Per aumentare un valore salvato invece di sostituirlo, somma EXCLUDED al valore attuale. Qualifica il valore attuale con il nome della tabella. Dentro DO UPDATE, un views da solo corrisponde sia alla tabella sia a EXCLUDED, e PostgreSQL si ferma con "column reference "views" is ambiguous".

La prima istruzione inserisce la riga con 1 e le due successive ci sommano, quindi /home arriva a 3.

Aggiornare solo quando il valore è cambiato

Un WHERE dopo DO UPDATE SET decide, riga per riga, se l'update viene eseguito. Quando è falso, la riga resta com'è e non conta né come inserita né come aggiornata. Usalo per tenere solo i dati più recenti, o per non scrivere una nuova versione della riga quando non è cambiato niente.

A-100 resta a 10 perché la riga in arrivo è più vecchia. B-200 prende 25. Per saltare gli update che scriverebbero gli stessi valori, usa WHERE prices.price IS DISTINCT FROM EXCLUDED.price, che gestisce anche NULL.

Distinguere le righe inserite da quelle aggiornate

RETURNING funziona su un upsert come su qualsiasi insert. Per distinguere i due casi, leggi la colonna di sistema xmax, che vale 0 per una riga inserita da questa istruzione:

A-100 torna con 8 e inserted falso, C-300 con 7 e vero. xmax è un dettaglio interno, non un'API documentata, quindi consideralo una comodità. La pagina su RETURNING copre la clausola per intero.

Errori che incontrerai

  • "ON CONFLICT DO UPDATE command cannot affect row a second time": la stessa chiave compare due volte nel VALUES o nella SELECT di una sola istruzione. Togli prima i duplicati, per esempio con SELECT DISTINCT ON (sku) ....
  • "column reference is ambiguous": qualifica la colonna con il nome della tabella nelle espressioni di SET e nel WHERE, come sopra.
  • Buchi negli id identity o serial: un insert che si trasforma in update ha già preso un valore dalla sequenza, quindi gli id saltano dei numeri. È normale.

Anche MERGE (da PostgreSQL 15) può inserire o aggiornare, ma non protegge da due sessioni che inseriscono la stessa chiave nello stesso momento. Per gli upsert per chiave, usa ON CONFLICT. Il riferimento ufficiale di INSERT elenca tutte le opzioni.

Domande frequenti

PostgreSQL ha un'istruzione UPSERT?

Non esiste una parola chiave UPSERT. PostgreSQL 9.5 ha aggiunto INSERT ... ON CONFLICT, che inserisce una riga oppure, quando violerebbe un vincolo di unicità, aggiorna la riga esistente (DO UPDATE) o la salta (DO NOTHING).

INSERT ... ON CONFLICT è sicuro con insert concorrenti?

Sì. Ogni riga proposta finisce in un insert o in un update, anche quando altre sessioni inseriscono la stessa chiave nello stesso momento. Una SELECT seguita da un INSERT o un UPDATE nel codice dell'applicazione non dà questa garanzia.

Per un upsert meglio MERGE o ON CONFLICT?

Usa ON CONFLICT per inserire o aggiornare per chiave. MERGE (da PostgreSQL 15) copre più casi, come eliminare le righe che corrispondono. Con insert concorrenti della stessa chiave può fallire con una violazione di unicità, cosa che ON CONFLICT evita.

Perché ricevo "there is no unique or exclusion constraint matching the ON CONFLICT specification"?

Le colonne in ON CONFLICT (...) devono corrispondere esattamente a un indice o vincolo univoco. Aggiungi un vincolo UNIQUE o un indice univoco su quelle colonne, oppure indica le colonne di uno che esiste già.

Illustrazione dei linguaggi di programmazione di Coddy

Impara a programmare con Coddy

Inizia