He leído acerca de diferentes UPSERT implementaciones en PostgreSQL, pero todas estas soluciones son relativamente antiguas o relativamente exóticas (por ejemplo, utilizando CTE escribible ).
Y yo «no soy un experto en psql en todo para averiguar inmediatamente si estas soluciones son antiguas porque están bien recomendadas o son (bueno, casi todas) solo ejemplos de juguetes que no son apropiados para el uso de producción.
¿Cuál es la forma más segura de implementar UPSERT en PostgreSQL?
Responder
PostgreSQL ahora tiene UPSERT .
El método preferido según una pregunta similar de StackOverflow es actualmente el siguiente:
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"); Comentarios
- I ' prefiero usar un CTE grabable: stackoverflow.com/a/8702291/330315
- Qué ' ¿es la ventaja de un CTE escribible frente a una función?
- @Fran ç o es por un lado, la velocidad. Usando un CTE, ingresa a la base de datos una vez. Si lo hace de esta manera, podría golpearlo dos o más veces. Además, el optimizador puede ' t optimizar los procedimientos pl / pgsql con la misma eficacia que el código SQL puro.
- @Fran ç ois Por otro lado, concurrencia. Dado que el ejemplo anterior tiene varias declaraciones SQL, debe preocuparse por las condiciones de carrera (el motivo del bucle klugey). Una sola declaración SQL será atómica. Vea este vínculo
- @Fran ç oisBeausoleil vea aquí y aquí por qué. Básicamente, sin un bucle de reintento, debe serializar o existe la posibilidad de fallas debido a la condición de carrera inherente.
Respuesta
ACTUALIZAR (2015-08-20):
Ahora hay una implementación oficial para manejar upserts mediante el uso de ON CONFLICT DO UPDATE (documentación oficial) . En el momento de escribir este artículo, esta función se encuentra actualmente en PostgreSQL 9.5 Alpha 2, que está disponible para descargar aquí: Directorios de origen de Postgres .
Aquí hay un ejemplo, asumiendo que item_id es su clave principal:
INSERT INTO my_table (item_id, price) VALUES (123456, 10.99) ON CONFLICT (item_id) DO UPDATE SET price = EXCLUDED.price Publicación original …
Aquí hay una implementación a la que llegué cuando deseaba obtener visibilidad sobre si se produjo una inserción o actualización.
La definición de upsert_data es consolidar los valores en un solo recurso, en lugar de tener que especificar el precio y el item_id dos veces: una vez para la actualización, nuevamente para la inserción.
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 no «No me gusta el uso de upsert_data, aquí hay una implementación alternativa:
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 Comentarios
- ¿Cómo funciona?
- @jb. no tan bien como me gustaría. Usted ' va a ver penalización significativa por desempeño ies frente a realizar inserciones rectas. Sin embargo, para lotes más pequeños (digamos 1000 o menos), este ejemplo debería funcionar bien.
Respuesta
Este le permitirá saber si la inserción o actualización ocurrió:
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 ocurre la actualización, obtendrá un inserto 0, de lo contrario inserte 1 o un error.