Inserisci, su aggiornamento duplicato in PostgreSQL?

Diversi mesi fa ho imparato da una risposta su Stack Overflow come eseguire più aggiornamenti contemporaneamente in MySQL usando la seguente syntax:

INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z) ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2); 

Ora sono passato a PostgreSQL e apparentemente questo non è corretto. Si riferisce a tutte le tabelle corrette, quindi presumo che si tratti di parole chiave diverse ma non sono sicuro di dove sia inclusa la documentazione di PostgreSQL.

Per chiarire, voglio inserire diverse cose e se esistono già per aggiornarle.

PostgreSQL dalla versione 9.5 ha la syntax di UPSERT , con la clausola ON CONFLICT . con la seguente syntax (simile a MySQL)

 INSERT INTO the_table (id, column_1, column_2) VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z') ON CONFLICT (id) DO UPDATE SET column_1 = excluded.column_1, column_2 = excluded.column_2; 

La ricerca negli archivi di posta elettronica di postgresql per “upsert” porta a trovare un esempio di come fare ciò che si vorrebbe fare, nel manuale :

Esempio 38-2. Eccezioni con UPDATE / INSERT

Questo esempio utilizza la gestione delle eccezioni per eseguire UPDATE o INSERT, a seconda dei casi:

 CREATE TABLE db (a INT PRIMARY KEY, b TEXT); CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ BEGIN LOOP -- first try to update the key -- note that "a" must be unique UPDATE db SET b = data WHERE a = key; IF found THEN RETURN; END IF; -- not there, so try to insert the key -- if someone else inserts the same key concurrently, -- we could get a unique-key failure BEGIN INSERT INTO db(a,b) VALUES (key, data); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing, and loop to try the UPDATE again END; END LOOP; END; $$ LANGUAGE plpgsql; SELECT merge_db(1, 'david'); SELECT merge_db(1, 'dennis'); 

C’è probabilmente un esempio di come fare questo alla rinfusa, usando CTEs in 9.1 e sopra, nella mailing list degli hacker :

 WITH foos AS (SELECT (UNNEST(%foo[])).*) updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id) INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id) WHERE updated.id IS NULL; 

Vedi la risposta a_horse_with_no_name per un esempio più chiaro.

Attenzione: questo non è sicuro se eseguito da più sessioni contemporaneamente (vedi avvertenze di seguito).


Un altro modo intelligente di fare un “UPSERT” in postgresql è di fare due istruzioni UPDATE / INSERT sequenziali che sono progettate per avere successo o non avere alcun effetto.

 UPDATE table SET field='C', field2='Z' WHERE id=3; INSERT INTO table (id, field, field2) SELECT 3, 'C', 'Z' WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3); 

L’UPDATE avrà successo se esiste già una riga con “id = 3”, altrimenti non ha alcun effetto.

L’INSERT avrà successo solo se la riga con “id = 3” non esiste già.

È ansible combinare questi due in un’unica stringa ed eseguirli entrambi con una singola istruzione SQL eseguita dall’applicazione. Si consiglia di eseguirli insieme in una singola transazione.

Funziona molto bene quando viene eseguito in isolamento o su una tabella bloccata, ma è sobject a condizioni di competizione, il che significa che potrebbe ancora fallire con errore della chiave duplicata se una riga viene inserita contemporaneamente, oppure potrebbe terminare senza riga inserita quando una riga viene cancellata contemporaneamente . Una transazione SERIALIZABLE su PostgreSQL 9.1 o versioni successive la gestirà in modo affidabile al costo di un tasso di errore di serializzazione molto alto, il che significa che dovrai riprovare molto. Scopri perché è così complicato , che discute questo caso in modo più dettagliato.

Questo approccio è anche sobject a aggiornamenti persi nell’isolamento di read committed meno che l’applicazione non controlli i conteggi delle righe interessate e verifichi che l’ insert o l’ update interessato una riga .

Con PostgreSQL 9.1 questo può essere ottenuto utilizzando un CTE scrivibile ( espressione di tabella comune ):

 WITH new_values (id, field1, field2) as ( values (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z') ), upsert as ( update mytable m set field1 = nv.field1, field2 = nv.field2 FROM new_values nv WHERE m.id = nv.id RETURNING m.* ) INSERT INTO mytable (id, field1, field2) SELECT id, field1, field2 FROM new_values WHERE NOT EXISTS (SELECT 1 FROM upsert up WHERE up.id = new_values.id) 

Vedi questi post di blog:

  • Upserting tramite CTE scrivibile
  • ASPETTANDO 9.1 CTE SCRITTE
  • PERCHÉ UPSERT è COSÌ COMPLICATO?

Si noti che questa soluzione non impedisce una violazione della chiave univoca, ma non è vulnerabile agli aggiornamenti persi.
Vedi il seguito di Craig Ringer su dba.stackexchange.com

In PostgreSQL 9.5 e versioni successive è ansible utilizzare INSERT ... ON CONFLICT UPDATE .

Vedi la documentazione .

Un INSERT ... ON DUPLICATE KEY UPDATE MySQL INSERT ... ON DUPLICATE KEY UPDATE può essere riformulato direttamente su ON CONFLICT UPDATE . Né la syntax SQL-standard, sono entrambe estensioni specifiche del database. Ci sono buone ragioni per cui MERGE non è stato usato per questo , una nuova syntax non è stata creata solo per divertimento. (La syntax di MySQL ha anche problemi che indicano che non è stata adottata direttamente).

ad es. configurazione data:

 CREATE TABLE tablename (a integer primary key, b integer, c integer); INSERT INTO tablename (a, b, c) values (1, 2, 3); 

la query MySQL:

 INSERT INTO tablename (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; 

diventa:

 INSERT INTO tablename (a, b, c) values (1, 2, 10) ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1; 

differenze:

  • È necessario specificare il nome della colonna (o il nome del vincolo univoco) da utilizzare per il controllo di unicità. È ON CONFLICT (columnname) DO

  • È necessario utilizzare la parola chiave SET , come se si trattasse di una normale istruzione UPDATE

Ha anche alcune caratteristiche interessanti:

  • Puoi avere una clausola WHERE sul tuo UPDATE (ti permette di triggersre ON CONFLICT UPDATE in ON CONFLICT IGNORE per determinati valori)

  • I valori proposti per l’inserimento sono disponibili come variabile di riga EXCLUDED , che ha la stessa struttura della tabella di destinazione. È ansible ottenere i valori originali nella tabella utilizzando il nome della tabella. Quindi in questo caso EXCLUDED.c sarà 10 (perché è quello che abbiamo provato ad inserire) e "table".c sarà 3 perché quello è il valore corrente nella tabella. È ansible utilizzare uno o entrambi nelle espressioni SET e nella clausola WHERE .

Per lo sfondo su upsert vedi Come UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) in PostgreSQL?

stavo cercando la stessa cosa quando sono venuto qui, ma la mancanza di una funzione generica “upsert” mi ha disturbato un po ‘così ho pensato che potevi semplicemente passare l’aggiornamento e inserire sql come argomenti su quella funzione dal manuale

sarebbe simile a questo:

 CREATE FUNCTION upsert (sql_update TEXT, sql_insert TEXT) RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN LOOP -- first try to update EXECUTE sql_update; -- check if the row is found IF FOUND THEN RETURN; END IF; -- not found so insert the row BEGIN EXECUTE sql_insert; RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing and loop END; END LOOP; END; $$; 

e forse per fare ciò che inizialmente volevi fare, batch “upsert”, potresti usare Tcl per dividere sql_update e fare il loop dei singoli aggiornamenti, il risultato della preformance sarà molto piccolo vedi http://archives.postgresql.org/pgsql- prestazioni / 2006-04 / msg00557.php

il costo più alto è l’esecuzione della query dal codice, sul lato del database il costo di esecuzione è molto più piccolo

Non esiste un semplice comando per farlo.

L’approccio più corretto è utilizzare la funzione, come quella dei documenti .

Un’altra soluzione (anche se non così sicura) è quella di fare aggiornamento con la restituzione, controllare quali file sono stati gli aggiornamenti e inserire il resto di essi

Qualcosa sulla falsariga di:

 update table set column = x.column from (values (1,'aa'),(2,'bb'),(3,'cc')) as x (id, column) where table.id = x.id returning id; 

assumendo id: 2 è stato restituito:

 insert into table (id, column) values (1, 'aa'), (3, 'cc'); 

Ovviamente, prima o poi verrà rilasciato (in concomitanza con l’ambiente), poiché qui vi è una chiara condizione di competizione, ma di solito funzionerà.

Ecco un articolo più lungo e più completo sull’argomento .

Personalmente, ho impostato una “regola” allegata all’istruzione insert. Supponiamo che tu abbia una tabella “dns” che registra gli hit dns per cliente su base per volta:

 CREATE TABLE dns ( "time" timestamp without time zone NOT NULL, customer_id integer NOT NULL, hits integer ); 

Volevi essere in grado di reinserire le righe con valori aggiornati o crearle se non esistessero già. Digitato customer_id e ora. Qualcosa come questo:

 CREATE RULE replace_dns AS ON INSERT TO dns WHERE (EXISTS (SELECT 1 FROM dns WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id)))) DO INSTEAD UPDATE dns SET hits = new.hits WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id)); 

Aggiornamento: questo ha il potenziale per fallire se si verificano inserimenti simultanei, poiché genererà eccezioni unique_violation. Tuttavia, la transazione non terminata continuerà e avrà esito positivo e sarà sufficiente ripetere la transazione terminata.

Tuttavia, se ci sono tonnellate di inserimenti che si verificano continuamente, è necessario inserire un blocco di tabella attorno alle istruzioni di inserimento: Il blocco ESCLUSIVO SHARE ROW impedirà qualsiasi operazione che potrebbe inserire, eliminare o aggiornare righe nella tabella di destinazione. Tuttavia, gli aggiornamenti che non aggiornano la chiave univoca sono sicuri, quindi se non si esegue alcuna operazione, utilizzare invece i blocchi di avviso.

Inoltre, il comando COPY non usa REGOLE, quindi se stai inserendo con COPY, dovrai invece utilizzare i trigger.

Ho personalizzato la funzione “upsert” sopra, se vuoi INSERIRE E SOSTITUIRE:

`

  CREATE OR REPLACE FUNCTION upsert(sql_insert text, sql_update text) RETURNS void AS $BODY$ BEGIN -- first try to insert and after to update. Note : insert has pk and update not... EXECUTE sql_insert; RETURN; EXCEPTION WHEN unique_violation THEN EXECUTE sql_update; IF FOUND THEN RETURN; END IF; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION upsert(text, text) OWNER TO postgres;` 

E dopo l’esecuzione, fai qualcosa di simile a questo:

 SELECT upsert($$INSERT INTO ...$$,$$UPDATE... $$) 

È importante inserire una virgola doppia per evitare errori del compilatore

  • controlla la velocità …

Ho lo stesso problema per la gestione delle impostazioni dell’account come coppie di valori nominali. I criteri di progettazione prevedono che client diversi possano avere set di impostazioni differenti.

La mia soluzione, simile a JWP, è quella di eliminare e sostituire in blocco, generando il record di unione all’interno dell’applicazione.

Questo è abbastanza a prova di proiettile, indipendente dalla piattaforma e dal momento che non ci sono mai più di circa 20 impostazioni per client, questo è solo 3 chiamate db con carico piuttosto basso, probabilmente il metodo più veloce.

L’alternativa dell’aggiornamento di singole righe – il controllo delle eccezioni e l’inserimento – o una combinazione di codice odioso, lento e spesso interrotto perché (come accennato sopra) gestione delle eccezioni SQL non standard che cambia da db a db – o anche da rilasciare al rilascio.

  #This is pseudo-code - within the application: BEGIN TRANSACTION - get transaction lock SELECT all current name value pairs where id = $id into a hash record create a merge record from the current and update record (set intersection where shared keys in new win, and empty values in new are deleted). DELETE all name value pairs where id = $id COPY/INSERT merged records END TRANSACTION 

Simile alla risposta più gradita, ma funziona leggermente più veloce:

 WITH upsert AS (UPDATE spider_count SET tally=1 WHERE date='today' RETURNING *) INSERT INTO spider_count (spider, tally) SELECT 'Googlebot', 1 WHERE NOT EXISTS (SELECT * FROM upsert) 

(fonte: http://www.the-art-of-web.com/sql/upsert/ )

 CREATE OR REPLACE FUNCTION save_user(_id integer, _name character varying) RETURNS boolean AS $BODY$ BEGIN UPDATE users SET name = _name WHERE id = _id; IF FOUND THEN RETURN true; END IF; BEGIN INSERT INTO users (id, name) VALUES (_id, _name); EXCEPTION WHEN OTHERS THEN UPDATE users SET name = _name WHERE id = _id; END; RETURN TRUE; END; $BODY$ LANGUAGE plpgsql VOLATILE STRICT 

UPDATE restituirà il numero di righe modificate. Se si utilizza JDBC (Java), è ansible controllare questo valore su 0 e, se nessuna riga è stata modificata, triggersre invece INSERT. Se usi qualche altro linguaggio di programmazione, forse il numero delle righe modificate può ancora essere ottenuto, controlla la documentazione.

Questo potrebbe non essere elegante ma hai un SQL molto più semplice che è più banale da usare dal codice chiamante. Diversamente, se scrivi dieci script di riga in PL / PSQL, probabilmente dovresti fare un test unitario di uno o un altro tipo solo per questo.

Io uso questa unione di funzioni

 CREATE OR REPLACE FUNCTION merge_tabla(key INT, data TEXT) RETURNS void AS $BODY$ BEGIN IF EXISTS(SELECT a FROM tabla WHERE a = key) THEN UPDATE tabla SET b = data WHERE a = key; RETURN; ELSE INSERT INTO tabla(a,b) VALUES (key, data); RETURN; END IF; END; $BODY$ LANGUAGE plpgsql 

Secondo la documentazione di PostgreSQL dell’istruzione INSERT , la gestione del caso ON DUPLICATE KEY non è supportata. Quella parte della syntax è un’estensione MySQL proprietaria.

Modifica: questo non funziona come previsto. A differenza della risposta accettata, questo produce violazioni chiave uniche quando due processi ripetutamente chiamano upsert_foo contemporaneamente.

Eureka! Ho trovato un modo per farlo in una query: usa UPDATE ... RETURNING per verificare se alcune righe erano interessate:

 CREATE TABLE foo (k INT PRIMARY KEY, v TEXT); CREATE FUNCTION update_foo(k INT, v TEXT) RETURNS SETOF INT AS $$ UPDATE foo SET v = $2 WHERE k = $1 RETURNING $1 $$ LANGUAGE sql; CREATE FUNCTION upsert_foo(k INT, v TEXT) RETURNS VOID AS $$ INSERT INTO foo SELECT $1, $2 WHERE NOT EXISTS (SELECT update_foo($1, $2)) $$ LANGUAGE sql; 

L’ UPDATE deve essere eseguito in una procedura separata perché, sfortunatamente, si tratta di un errore di syntax:

 ... WHERE NOT EXISTS (UPDATE ...) 

Ora funziona come desiderato:

 SELECT upsert_foo(1, 'hi'); SELECT upsert_foo(1, 'bye'); SELECT upsert_foo(3, 'hi'); SELECT upsert_foo(3, 'bye'); 

Per unire piccoli set, usare la funzione sopra è ok. Tuttavia, se si stanno unendo grandi quantità di dati, suggerirei di cercare http://mbk.projects.postgresql.org

L’attuale best practice di cui sono a conoscenza è:

  1. COPIA i dati nuovi / aggiornati nella tabella temporanea (certo, o puoi fare INSERIRE se il costo è ok)
  2. Acquisisci blocco [facoltativo] (l’avviso è preferibile alle serrature da tavolo, IMO)
  3. Unire. (la parte divertente)