Olen lukenut erilaisia UPSERT -toteutuksia PostgreSQL: ssä, mutta kaikki nämä ratkaisut ovat suhteellisen vanhoja tai suhteellisen eksoottisia (käyttämällä esimerkiksi kirjoitettavaa CTE: tä ).
Enkä vain ole psql-asiantuntija kaikki saadaksemme heti selville, ovatko nämä ratkaisut vanhoja, koska niitä suositellaan hyvin vai ovatko ne (hyvin, melkein ne kaikki) vain leluesimerkkejä, jotka eivät sovellu tuotantokäyttöön.
Mikä on kaikkein langattomin tapa ottaa UPSERT käyttöön PostgreSQL: ssä?
Vastaa
PostgreSQL nyt on UPSERT .
vastaava StackOverflow-kysymyksen mukainen menetelmä on tällä hetkellä seuraava:
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"); Kommentit
- I ' d käytä pikemminkin kirjoitettavaa CTE: stackoverflow.com/a/8702291/330315
- mitä ' Onko kirjoitettavan CTE: n etu funktioon nähden?
- @Fran ç on yksi asia, nopeus. CTE: n avulla saavut tietokantaan kerran. Näin tekemällä saatat lyödä sitä vähintään kaksi kertaa. Lisäksi optimoija ei voi ' t optimoida pl / pgsql-menettelyjä yhtä tehokkaasti kuin puhdas SQL-koodi.
- @Fran ç ois Toiseksi, samanaikaisuus. Koska yllä olevassa esimerkissä on useita SQL-käskyjä, sinun on huolehdittava kilpailuolosuhteista (syy klugey-silmukalle). Yksi SQL-käsky on atominen. Katso tämä linkki
- @Fran ç oisBeausoleil katso täällä ja täällä miksi. Periaatteessa ilman uudelleenkäynnistyssilmukkaa joudut joko sarjaan tai sinulla on mahdollisuus epäonnistumiseen luontaisen kilpailutilanteen vuoksi.
Vastaa
PÄIVITYS (20.8.2015):
Häiriöiden käsittelyyn on nyt virallinen toteutus ON CONFLICT DO UPDATE (virallinen dokumentaatio) -toiminnon avulla. . Tämän kirjoituksen aikana tämä ominaisuus on tällä hetkellä PostgreSQL 9.5 Alpha 2: ssa, jonka voi ladata täältä: Postgres-lähdehakemistot .
Tässä on esimerkki olettaen, että item_id on ensisijainen avain:
INSERT INTO my_table (item_id, price) VALUES (123456, 10.99) ON CONFLICT (item_id) DO UPDATE SET price = EXCLUDED.price Alkuperäinen viesti …
Tässä on toteutus, jonka halusin saada näkyvämmäksi, onko lisäys vai päivitys tapahtunut.
upsert_data -määrityksen tarkoituksena on vahvistaa arvot yhdeksi resurssiksi sen sijaan, että sinun olisi määritettävä hinta ja item_id kahdesti: Kerran päivitykselle, uudestaan lisäykselle.
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 Jos et ”Tykkää upsert_data käytöstä, tässä on vaihtoehtoinen toteutus:
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 kommentit
- Kuinka se toimii?
- @jb. ei niin hyvin kuin haluaisin. Sinä ' näet merkittävä suorituskyky penaltti tai suorien inserttien suorittaminen. Pienemmissä erissä (sanotaan 1000 tai vähemmän) tämän esimerkin tulisi kuitenkin toimia hienosti.
Vastaa
Tämä ilmoittaa, tapahtuiko lisäys vai päivitys:
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" ); Jos päivitys tapahtuu, saat lisäosan 0, muuten lisää 1 tai virhe.