Ho letto di diverse UPSERT implementazioni in PostgreSQL, ma tutte queste soluzioni sono relativamente vecchie o relativamente esotiche (utilizzando CTE scrivibile , per esempio).
E io “non sono un esperto di psql in tutto per scoprire subito se queste soluzioni sono vecchie perché ben consigliate o (beh, quasi tutte lo sono) solo esempi di giocattoli non consoni alluso di produzione.
Qual è il modo più thread-safe per implementare UPSERT in PostgreSQL?
Rispondi
PostgreSQL ora ha UPSERT .
Il metodo preferito secondo una domanda StackOverflow simile è attualmente il seguente:
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 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"); Commenti
- I ' d piuttosto utilizzare un CTE scrivibile: stackoverflow.com/a/8702291/330315
- Cosa ' è il vantaggio di un CTE scrivibile rispetto a una funzione?
- @Fran ç è innanzitutto la velocità. Usando un CTE hai colpito il database una volta. In questo modo potresti colpirlo due o più volte. Inoltre, lottimizzatore può ' t ottimizzare le procedure pl / pgsql con la stessa efficienza del codice SQL puro.
- @Fran ç ois Per unaltra cosa, la concorrenza. Poiché lesempio precedente ha più istruzioni SQL, devi preoccuparti delle condizioni di competizione (il motivo del ciclo klugey). Una singola istruzione SQL sarà atomica. Vedi questo link
- @Fran ç oisBeausoleil vedi qui e qui per il motivo. Fondamentalmente senza un ciclo di riprova, devi serializzare o hai la possibilità di errori a causa della condizione di competizione intrinseca.
Risposta
AGGIORNAMENTO (2015-08-20):
Ora è disponibile unimplementazione ufficiale per la gestione degli upsert tramite luso di ON CONFLICT DO UPDATE (documentazione ufficiale) . Al momento della stesura di questo documento, questa funzionalità risiede attualmente in PostgreSQL 9.5 Alpha 2, che è disponibile per il download qui: Directory dei sorgenti di Postgres .
Ecco un esempio, supponendo che item_id sia la tua chiave primaria:
INSERT INTO my_table (item_id, price) VALUES (123456, 10.99) ON CONFLICT (item_id) DO UPDATE SET price = EXCLUDED.price Post originale …
Ecco unimplementazione a cui sono arrivato quando desideravo ottenere visibilità sul fatto che si sia verificato un inserimento o un aggiornamento.
La definizione di upsert_data è consolidare i valori in una singola risorsa, anziché dover specificare due volte il prezzo e item_id: una volta per laggiornamento, di nuovo per linserimento.
WITH upsert_data AS ( SELECT "19.99"::numeric(10,2) AS price, "abcdefg"::character varying AS item_id ), update_outcome AS ( UPDATE pricing_tbl SET price = upsert_data.price FROM upsert_data WHERE pricing_tbl.item_id = upsert_data.item_id RETURNING "update"::text AS action, item_id ), insert_outcome AS ( INSERT INTO pricing_tbl (price, item_id) SELECT upsert_data.price AS price, upsert_data.item_id AS item_id FROM upsert_data WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1) RETURNING "insert"::text AS action, item_id ) SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome Se non lo fai “Come luso di upsert_data, ecco unimplementazione alternativa:
WITH update_outcome AS ( UPDATE pricing_tbl SET price = "19.99" WHERE pricing_tbl.item_id = "abcdefg" RETURNING "update"::text AS action, item_id ), insert_outcome AS ( INSERT INTO pricing_tbl (price, item_id) SELECT "19.99" AS price, "abcdefg" AS item_id WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1) RETURNING "insert"::text AS action, item_id ) SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome Commenti
- Come si comporta?
- @jb. non come vorrei. ' vedrai penalt prestazioni significative contro lesecuzione di inserti diritti. Tuttavia, per batch più piccoli (diciamo 1000 o meno), questo esempio dovrebbe funzionare correttamente.
Answer
Questo ti farà sapere se linserimento o laggiornamento è avvenuto:
with "update_items" as ( -- Update statement here update items set price = 3499, name = "Uncle Bob" where id = 1 returning * ) -- Insert statement here insert into items (price, name) -- But make sure you put your values like so select 3499, "Uncle Bob" where not exists ( select * from "update_items" ); Se laggiornamento si verifica, otterrai un inserimento 0, altrimenti inserisci 1 o un errore.