Jeg har lest om forskjellige UPSERT implementeringer i PostgreSQL, men alle disse løsningene er relativt gamle eller relativt eksotiske (bruker f.eks. skrivbar CTE .
Og jeg er bare ikke en psql-ekspert på alt for å finne ut med en gang, om disse løsningene er gamle fordi de anbefales, eller de er (vel, nesten alle er) bare leketøyeksempler som ikke passer til produksjonsbruk.
Hva er den mest trådsikre måten å implementere UPSERT i PostgreSQL?
Svar
PostgreSQL nå har UPSERT .
Den foretrukne metoden i henhold til et lignende StackOverflow-spørsmål er for øyeblikket 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 ' bruker heller en skrivbar CTE: stackoverflow.com/a/8702291/330315
- Hva ' er fordelen med en skrivbar CTE vs en funksjon?
- @Fran ç ois for en ting, hastighet. Ved å bruke en CTE treffer du databasen en gang. Hvis du gjør det på denne måten, kan du slå det to eller flere ganger. Optimizer kan også ' t optimalisere pl / pgsql prosedyrer så effektivt som ren SQL-kode.
- @Fran ç ois For en annen ting, samtidighet. Siden eksemplet ovenfor har flere SQL-setninger, må du bekymre deg for løpsforhold (årsaken til klugey-sløyfen). En enkelt SQL-setning vil være atomisk. Se denne lenken
- @Fran ç oisBeausoleil se her og her for hvorfor. I utgangspunktet uten en omprøvingsløyfe, må du enten serialisere eller du har muligheten for feil på grunn av den iboende løpstilstanden.
Svar
UPDATE (2015-08-20):
Det er nå en offisiell implementering for håndtering av oppsett ved bruk av ON CONFLICT DO UPDATE (offisiell dokumentasjon) . I skrivende stund ligger denne funksjonen for tiden i PostgreSQL 9.5 Alpha 2, som er tilgjengelig for nedlasting her: Postgres kildekataloger .
Her er et eksempel, forutsatt at item_id er din primære nøkkel:
INSERT INTO my_table (item_id, price) VALUES (123456, 10.99) ON CONFLICT (item_id) DO UPDATE SET price = EXCLUDED.price Originalpost …
Her er en implementering jeg kom til da jeg ønsket å få innsyn i om en innsats eller oppdatering skjedde.
Definisjonen av upsert_data er å konsolidere verdiene i en enkelt ressurs, i stedet for å måtte spesifisere prisen og item_id to ganger: En gang for oppdateringen, igjen for innsatsen.
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 «som bruken av 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å bra som jeg vil. Du ' kommer til å se betydelig ytelsesstraff mot å utføre rette innsatser. For mindre batcher (si 1000 eller færre), bør dette eksemplet imidlertid fungere fint.
Svar
Dette vil gi deg beskjed om innsettingen eller oppdateringen skjedde:
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 oppdateringen skjer, får du en innsats 0, ellers setter du inn 1 eller en feil.