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, eproducts.priceè il prezzo già salvato.DO UPDATE SETmodifica 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
VALUESo nellaSELECTdi una sola istruzione. Togli prima i duplicati, per esempio conSELECT DISTINCT ON (sku) .... - "column reference is ambiguous": qualifica la colonna con il nome della tabella nelle espressioni di
SETe nelWHERE, 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à.