Idiomatický způsob implementace UPSERT v PostgreSQL

PostgreSQL nyní má UPSERT .

Upřednostňovaná metoda podle podobné otázky StackOverflow je v současné době následující:

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"); 

Komentáře

• I ' raději použijte zapisovatelný CTE: stackoverflow.com/a/8702291/330315
• co má výhodu zapisovatelného CTE oproti funkci?
• @Fran ç o je jedna věc, rychlost. Pomocí CTE narazíte do databáze jednou. Když to uděláte tímto způsobem, můžete do něj zasáhnout dvakrát nebo vícekrát. Optimalizátor také nemůže ' t optimalizovat postupy pl / pgsql stejně efektivně jako čistý kód SQL.
• @Fran ç ois Pro další věc, souběžnost. Vzhledem k tomu, že výše uvedený příklad obsahuje více příkazů SQL, musíte si dělat starosti s rasovými podmínkami (důvod smyčky klugey). Jeden příkaz SQL bude atomový. Viz tento odkaz
• @Fran ç oisBeausoleil viz zde a zde , proč. V zásadě bez smyčky pro opakovaný pokus musíte buď serializovat, nebo máte možnost selhání kvůli inherentní podmínce závodu.

UPDATE (2015-08-20):

Nyní existuje oficiální implementace pro zpracování upserts pomocí ON CONFLICT DO UPDATE (oficiální dokumentace) . V době psaní tohoto článku je tato funkce aktuálně umístěna v PostgreSQL 9.5 Alpha 2, který je k dispozici ke stažení zde: zdrojové adresáře Postgres .

Zde je příklad za předpokladu, že item_id je váš primární klíč:

INSERT INTO my_table (item_id, price) VALUES (123456, 10.99) ON CONFLICT (item_id) DO UPDATE SET price = EXCLUDED.price 

Původní příspěvek …

Zde je implementace, na kterou jsem přišel, když jsem chtěl získat přehled o tom, zda došlo k vložení nebo aktualizaci.

Definice upsert_data je konsolidovat hodnoty do jednoho zdroje, místo toho, abyste museli dvakrát uvádět cenu a item_id: jednou pro aktualizaci, znovu pro vložku.

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 

Pokud ne „Nelze použít upsert_data, zde je alternativní implementace:

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 

Komentáře

• Jak to funguje?
• @jb. ne tak dobře, jak bych chtěl. ' se znovu podíváte výrazný trest za výkon ies vs. provádění přímých vložek. U menších dávek (například 1 000 nebo méně) by však tento příklad měl fungovat dobře.

Toto vám sdělí, zda došlo k vložení nebo aktualizaci:

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" ); 

Pokud dojde k aktualizaci, zobrazí se vložka 0, jinak vložka 1 nebo chyba.