Jai lu des informations sur différentes implémentations UPSERT dans PostgreSQL, mais toutes ces solutions sont relativement anciennes ou relativement exotiques (en utilisant Writeable CTE , par exemple).
Et je « ne suis tout simplement pas un expert psql en tous pour savoir immédiatement, si ces solutions sont anciennes parce quelles sont bien recommandées ou si elles ne sont (enfin presque toutes) que des exemples de jouets qui ne conviennent pas à une utilisation en production.
Quel est le moyen le plus sûr pour les threads dimplémenter UPSERT dans PostgreSQL?
Réponse
PostgreSQL maintenant a UPSERT .
La méthode préférée selon une question StackOverflow similaire est actuellement le suivant:
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"); Commentaires
- I ' utilisez plutôt un CTE inscriptible: stackoverflow.com/a/8702291/330315
- What ' est lavantage dun CTE inscriptible par rapport à une fonction?
- @Fran ç ois pour une chose, la vitesse. En utilisant un CTE, vous avez atteint la base de données une fois. En procédant de cette façon, vous pourriez le frapper deux ou plusieurs fois. En outre, loptimiseur peut ' optimiser les procédures pl / pgsql aussi efficacement que du code SQL pur.
- @Fran ç ois Autre chose, la concurrence. Étant donné que lexemple ci-dessus a plusieurs instructions SQL, vous devez vous soucier des conditions de concurrence (la raison de la boucle klugey). Une seule instruction SQL sera atomique. Voir ce lien
- @Fran ç oisBeausoleil voir ici et ici pour savoir pourquoi. Fondamentalement, sans boucle de réessai, vous devez soit sérialiser, soit vous risquez déchouer en raison de la condition de concurrence inhérente.
Réponse
UPDATE (20/08/2015):
Il existe maintenant une implémentation officielle pour gérer les upserts via lutilisation de ON CONFLICT DO UPDATE (documentation officielle) . Au moment décrire ces lignes, cette fonctionnalité réside actuellement dans PostgreSQL 9.5 Alpha 2, qui est disponible en téléchargement ici: Répertoires sources Postgres .
Voici un exemple, en supposant que item_id est votre clé primaire:
INSERT INTO my_table (item_id, price) VALUES (123456, 10.99) ON CONFLICT (item_id) DO UPDATE SET price = EXCLUDED.price Message original …
Voici une implémentation à laquelle je suis arrivé lorsque je souhaitais avoir une meilleure visibilité pour savoir si une insertion ou une mise à jour a eu lieu.
La définition de upsert_data est de consolider les valeurs dans une seule ressource, plutôt que davoir à spécifier le prix et litem_id deux fois: une fois pour la mise à jour, encore une fois pour linsertion.
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 Si vous ne « T comme lutilisation de upsert_data, voici une implémentation alternative:
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 Commentaires
- Comment fonctionne-t-il?
- @jb. pas aussi bien que je le souhaiterais. Vous ' allez voir pénalité de performance significative ies vs effectuer des inserts droits. Cependant, pour les lots plus petits (disons 1 000 ou moins), cet exemple devrait fonctionner correctement.
Réponse
Ceci vous fera savoir si linsertion ou la mise à jour a eu lieu:
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" ); Si la mise à jour se produit, vous « obtiendrez un insert 0, sinon insérez 1 ou une erreur.