Jeg har læst om forskellige UPSERT implementeringer i PostgreSQL, men alle disse løsninger er relativt gamle eller relativt eksotiske (f.eks. ved hjælp af skrivbar CTE .
Og jeg er bare ikke en psql-ekspert på alt for at finde ud af med det samme, om disse løsninger er gamle, fordi de anbefales, eller om de er (godt, næsten alle er) bare legetøjseksempler, der ikke passer til produktionsbrug.
Hvad er den mest trådsikre måde at implementere UPSERT i PostgreSQL på?
Svar
PostgreSQL nu har UPSERT .
Den foretrukne metode ifølge et lignende StackOverflow-spørgsmål er i øjeblikket følgende:
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 ' bruger hellere en skrivbar CTE: stackoverflow.com/a/8702291/330315
- Hvad ' er fordelen ved en skrivbar CTE vs en funktion?
- @Fran ç ois for en ting, hastighed. Ved hjælp af en CTE rammer du databasen en gang. Hvis du gør det på denne måde, kan du måske ramme det to eller flere gange. Optimizer kan også ' t optimere pl / pgsql-procedurer så effektivt som ren SQL-kode.
- @Fran ç ois For en anden ting, samtidighed. Da ovenstående eksempel har flere SQL-udsagn, er du nødt til at bekymre dig om race-forhold (årsagen til klugey-loop). En enkelt SQL-sætning er atomær. Se dette link
- @Fran ç oisBeausoleil se her og her for hvorfor. Dybest set uden en genforsøgssløjfe skal du enten serialisere, eller du har muligheden for fejl på grund af den iboende race-tilstand.
Svar
UPDATE (2015-08-20):
Der er nu en officiel implementering til håndtering af opsætninger ved hjælp af ON CONFLICT DO UPDATE (officiel dokumentation) . På tidspunktet for denne skrivning ligger denne funktion i øjeblikket i PostgreSQL 9.5 Alpha 2, som kan downloades her: Postgres kildekataloger .
Her er et eksempel, forudsat at item_id er din primære nøgle:
INSERT INTO my_table (item_id, price) VALUES (123456, 10.99) ON CONFLICT (item_id) DO UPDATE SET price = EXCLUDED.price Originalindlæg …
Her er en implementering, jeg kom til, da jeg ønskede at få synlighed om, hvorvidt en indsættelse eller opdatering fandt sted.
Definitionen af upsert_data er at konsolidere værdierne i en enkelt ressource i stedet for at skulle angive prisen og item_id to gange: Én gang til opdateringen, igen for indsatsen.
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 Hvis du ikke “t som brugen af upsert_data, her er 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
- Hvordan fungerer det?
- @jb. ikke så godt som jeg gerne vil. Du ' vil se betydelig præstation i forhold til at udføre lige indsatser. For mindre partier (f.eks. 1000 eller derunder) skal dette eksempel fungere fint.
Svar
Dette vil fortælle dig, om indsættelsen eller opdateringen skete:
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" ); Hvis opdateringen sker, får du en indsættelse 0, ellers indsæt 1 eller en fejl.