Inserisci, oppure aggiorna se esiste già
Un'esigenza comune: inserire una riga ma, se esiste già una riga con la stessa chiave, aggiornarla. Senza UPSERT dovresti scrivere prima una SELECT e poi scegliere tra INSERT e UPDATE: due viaggi al database e una race condition nel mezzo.
L'UPSERT di SQLite lo fa in una sola istruzione:
La prima volta che lo esegui, la riga viene inserita. Eseguilo di nuovo con un prezzo diverso e lo stesso sku, e la riga esistente viene aggiornata sul posto. Nessun duplicato, nessun errore.
Anatomia di ON CONFLICT
La forma completa:
INSERT INTO table (...) VALUES (...)
ON CONFLICT(conflict_target) DO UPDATE SET col = expr, ...
WHERE condition;
Contano tre pezzi:
conflict_target: la colonna o le colonne con un vincoloUNIQUEoPRIMARY KEYsu cui ti aspetti una collisione. SQLite lo usa per scegliere quale indice sorvegliare.DO UPDATE SET ...: cosa cambiare nella riga esistente quando avviene una collisione. (OppureDO NOTHINGper saltare in silenzio.)WHEREfacoltativa: una condizione in più che deve essere vera perché l'aggiornamento venga davvero eseguito.
Il target del conflitto deve corrispondere a un vero vincolo di unicità. ON CONFLICT(price) non compila se price non è unico: SQLite non ha niente rispetto a cui rilevare un conflitto.
DO NOTHING: inserisci se manca, altrimenti salta
La variante più semplice. Utile quando popoli dati iniziali o registri eventi e i duplicati devono solo essere ignorati in silenzio:
Il secondo insert trova lo stesso event_id e normalmente solleverebbe UNIQUE constraint failed. Con DO NOTHING, SQLite lo salta e basta. Nessuna eccezione, nessuna riga toccata.
È l'"insert idempotente" per cui spesso si usa INSERT OR IGNORE. Il DO NOTHING dell'UPSERT fa lo stesso lavoro e si combina meglio con le clausole WHERE e RETURNING.
La pseudo tabella excluded
Quando scatta un conflitto, all'improvviso hai due righe in gioco: quella esistente nella tabella e quella nuova che hai provato a inserire. SQLite ti dà un modo per parlare di entrambe.
- I nomi di colonna semplici (
price,name) si riferiscono alla riga esistente. excluded.columnsi riferisce alla riga in arrivo che è stata rifiutata.
quantity = quantity + excluded.quantity si legge "la quantità esistente più quella nuova". Dopo due insert, A-100 ha quantità 8. Questo schema, accumulare in una riga esistente, è uno dei trucchi più utili dell'UPSERT.
Un UPSERT condizionale con WHERE
La WHERE finale ti permette di saltare l'aggiornamento a meno che non valga una certa condizione. Viene valutata sulla riga esistente (e può fare riferimento a excluded.* per quella in arrivo):
La nuova riga ha un updated_at più vecchio, quindi la WHERE è falsa e l'aggiornamento viene saltato. La riga esistente mantiene il suo prezzo più recente. Scambia le date e l'aggiornamento viene eseguito. È lo schema classico "sovrascrivi solo con dati più freschi".
Upsert di più righe
VALUES può contenere molte righe, e ON CONFLICT si applica a ciascuna in modo indipendente:
A-100 collide e viene aggiornata. A-200 e A-300 sono nuove e vengono inserite. Un'istruzione, un risultato misto di inserimenti e aggiornamenti. È un modo pulito per sincronizzare un gruppo di record da una fonte esterna.
UPSERT vs INSERT OR REPLACE
INSERT OR REPLACE sembra fare la stessa cosa. Non è così.
notes è sparito. INSERT OR REPLACE ha eliminato completamente la riga 1 e ne ha inserita una nuova: ogni colonna che non hai elencato è tornata a NULL o al suo valore predefinito. In più attiva i trigger DELETE e si propaga attraverso le chiavi esterne con ON DELETE.
L'UPSERT conserva la riga:
notes è ancora lì. Sono cambiate solo le colonne nominate in SET. Usa l'UPSERT come scelta predefinita; ricorri a INSERT OR REPLACE solo quando vuoi davvero la semantica elimina e reinserisci.
Più target di conflitto
Se una riga può collidere su più di un vincolo, puoi concatenare più clausole ON CONFLICT:
Vince il vincolo che scatta per primo, e viene eseguito il DO UPDATE di quel ramo. In pratica la maggior parte delle tabelle ha un target di conflitto ovvio (la chiave primaria o una singola colonna univoca) e raramente ti servirà più di una clausola.
Errori comuni
Alcune cose che fanno male:
- Senza un indice univoco corrispondente, niente UPSERT.
ON CONFLICT(col)richiede checolsia unaPRIMARY KEYo abbia un vincoloUNIQUE. Altrimenti SQLite dà errore con "no such constraint". DO UPDATEnon scatta se non c'è conflitto. È un'alternativa all'insert, non un comportamento aggiuntivo. La prima volta che una chiave compare, viene eseguito solo l'insert.excludedè in sola lettura. Puoi leggerla ma non scriverci. Il bersaglio diSETè sempre la riga esistente.- I rowid generati di
INTEGER PRIMARY KEY. Se non fornisci l'id, ogni insert ne riceve uno nuovo: non c'è niente con cui andare in conflitto. L'UPSERT ha senso solo quando la colonna in conflitto ha un valore deterministico fornito da chi chiama.
Prossimo passo: RETURNING
L'UPSERT non ti dice quali righe sono state inserite e quali aggiornate, né come sono i loro valori finali. Per quello ti serve la clausola RETURNING: restituisce le righe coinvolte nella stessa istruzione, senza bisogno di una SELECT successiva. È il prossimo argomento.
Domande frequenti
Cos'è l'UPSERT in SQLite?
Un UPSERT è un INSERT che diventa un UPDATE (o un'operazione nulla) quando altrimenti violerebbe un vincolo UNIQUE o PRIMARY KEY. Si scrive INSERT ... ON CONFLICT(column) DO UPDATE SET ... oppure DO NOTHING. SQLite lo supporta dalla versione 3.24.0 (2018).
Cos'è la tabella excluded nell'UPSERT di SQLite?
excluded è una speciale pseudo tabella che contiene la riga che hai provato a inserire. Dentro DO UPDATE SET ... fai riferimento alla riga esistente con il nome della colonna e alla riga rifiutata con excluded.column. Quindi SET price = excluded.price significa 'sovrascrivi il prezzo con quello che portava il nuovo INSERT'.
Che differenza c'è tra INSERT OR REPLACE e UPSERT?
INSERT OR REPLACE elimina la riga in conflitto e ne inserisce una nuova: questo attiva i trigger DELETE, rompe le chiavi esterne con ON DELETE CASCADE e riporta ogni colonna ai valori predefiniti. L'UPSERT aggiorna la riga esistente sul posto, quindi cambiano solo le colonne che nomini in SET. Preferisci l'UPSERT, a meno che tu non voglia davvero eliminare e reinserire.
Posso fare l'upsert di più righe insieme in SQLite?
Sì. INSERT INTO t(...) VALUES (...), (...), (...) ON CONFLICT(col) DO UPDATE SET ... funziona senza problemi. Ogni riga viene controllata singolarmente rispetto al target del conflitto, e la riga excluded dentro DO UPDATE si riferisce alla riga in arrivo che ha causato il conflitto.
