Jag har läst om olika UPSERT implementeringar i PostgreSQL, men alla dessa lösningar är relativt gamla eller relativt exotiska (med skrivbar CTE , till exempel).
Och jag är bara ingen psql-expert på allt för att ta reda på omedelbart, om dessa lösningar är gamla eftersom de rekommenderas eller om de är (ja, nästan alla är) bara leksaksexempel som inte är lämpliga för produktionsanvändning.
Vad är det mest trådsäkra sättet att implementera UPSERT i PostgreSQL?
Svar
PostgreSQL nu har UPSERT .
Den föredragna metoden enligt en liknande StackOverflow-fråga är för närvarande följande:
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"); Kommentarer
- I ' använder hellre en skrivbar CTE: stackoverflow.com/a/8702291/330315
- Vad ' är fördelen med en skrivbar CTE mot en funktion?
- @Fran ç ois för en sak, hastighet. Med hjälp av en CTE slår du databasen en gång. Om du gör det på detta sätt kan du slå det två eller flera gånger. Optimeraren kan också ' t optimera pl / pgsql-procedurer lika effektivt som ren SQL-kod.
- @Fran ç ois För en annan sak, samtidighet. Eftersom exemplet ovan har flera SQL-uttalanden måste du oroa dig för tävlingsförhållanden (anledningen till klugey-loop) Ett enda SQL-uttalande kommer att vara atomärt. Se den här länken
- @Fran ç oisBeausoleil se här och här för varför. I grund och botten utan en omprövningsslinga måste du antingen serienummera eller så har du möjligheten att misslyckas på grund av det inneboende rasförhållandet.
Svar
UPPDATERING (2015-08-20):
Det finns nu en officiell implementering för hantering av uppgraderingar genom användning av ON CONFLICT DO UPDATE (officiell dokumentation) . När detta skrivs finns denna funktion för närvarande i PostgreSQL 9.5 Alpha 2, som finns att ladda ner här: Postgres-källkataloger .
Här är ett exempel, förutsatt att item_id är din primära nyckel:
INSERT INTO my_table (item_id, price) VALUES (123456, 10.99) ON CONFLICT (item_id) DO UPDATE SET price = EXCLUDED.price Originalpost …
Här är en implementering som jag kom fram när jag ville få syn på huruvida en insats eller uppdatering inträffade.
Definitionen av upsert_data är att konsolidera värdena i en enda resurs, snarare än att behöva ange priset och artikel_id två gånger: En gång för uppdateringen, igen för infogningen.
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 Om du inte ”gillar användningen av upsert_data, här är en alternativ implementering:
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 Kommentarer
- Hur fungerar det?
- @jb. inte lika bra som jag skulle vilja. Du ' ska se betydande prestationsstraff mot att utföra raka insatser. Men för mindre satser (säg 1000 eller mindre) bör detta exempel fungera bra.
Svar
Detta kommer att meddela dig om insättningen eller uppdateringen hände:
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" ); Om uppdateringen sker, får du en insats 0, annars infogar du 1 eller ett fel.