Am citit despre diferite implementări UPSERT în PostgreSQL, dar toate aceste soluții sunt relativ vechi sau relativ exotice (folosind CTE scriibil , de exemplu).
Și nu sunt doar un expert psql la toate pentru a afla imediat dacă aceste soluții sunt vechi, deoarece sunt bine recomandate sau sunt (bine, aproape toate sunt) doar exemple de jucării care nu sunt adecvate utilizării în producție.
Care este cel mai sigur mod de a implementa UPSERT în PostgreSQL?
Răspunde
PostgreSQL acum are UPSERT .
Metoda preferată conform o întrebare similară StackOverflow este în prezent următorul:
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"); Comentarii
- I ' folosiți mai degrabă un CTE care poate fi scris: stackoverflow.com/a/8702291/330315
- Ce este avantajul unui CTE scriibil față de o funcție?
- @Fran ç este pentru un singur lucru, viteza. Folosind un CTE, accesați o dată baza de date. Procedând astfel, s-ar putea să o lovești de două sau mai multe ori. De asemenea, optimizatorul nu poate ' optimiza procedurile pl / pgsql la fel de eficient ca și codul SQL pur.
- @Fran ç ois Pentru un alt lucru, concurența. Deoarece exemplul de mai sus are mai multe instrucțiuni SQL, trebuie să vă faceți griji cu privire la condițiile de cursă (motivul buclei klugey). O singură declarație SQL va fi atomică. Consultați acest link
- @Fran ç oisBeausoleil vezi aici și aici pentru ce. Practic, fără o buclă de reîncercare, fie trebuie să serializați, fie aveți posibilitatea de a eșua din cauza condiției inerente de rasă.
Răspuns
UPDATE (20-08-2015):
Există acum o implementare oficială pentru gestionarea upserts prin utilizarea ON CONFLICT DO UPDATE (documentație oficială) . La momentul scrierii acestui articol, această caracteristică se află în prezent în PostgreSQL 9.5 Alpha 2, care este disponibil pentru descărcare aici: Directoare sursă Postgres .
Iată un exemplu, presupunând că item_id este cheia dvs. principală:
INSERT INTO my_table (item_id, price) VALUES (123456, 10.99) ON CONFLICT (item_id) DO UPDATE SET price = EXCLUDED.price Postarea originală …
Iată o implementare la care am ajuns atunci când doresc să obțin vizibilitate dacă a avut loc o inserare sau o actualizare.
Definiția upsert_data este de a consolida valorile într-o singură resursă, mai degrabă decât să specificați prețul și item_id de două ori: o dată pentru actualizare, din nou pentru inserare.
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 Dacă nu „nu îmi place utilizarea upsert_data, iată o implementare alternativă:
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 Comentarii
- Cum funcționează?
- @jb. nu la fel de bine pe cât aș vrea. Vei vedea ' performanță semnificativă penalt adică vs. efectuarea inserțiilor drepte. Cu toate acestea, pentru loturile mai mici (să zicem 1000 sau mai puțin), acest exemplu ar trebui să funcționeze foarte bine.
Răspunde
Acest lucru vă va informa dacă s-a întâmplat inserarea sau actualizarea:
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" ); Dacă actualizarea are loc, veți primi un insert 0, altfel inserați 1 sau o eroare.