Eu li sobre diferentes UPSERT implementações em PostgreSQL, mas todas essas soluções são relativamente antigas ou relativamente exóticas (usando CTE gravável , por exemplo).
E não sou um especialista em psql em tudo para descobrir imediatamente se essas soluções são antigas porque são bem recomendadas ou são (bem, quase todas são) apenas exemplos de brinquedos não apropriados para uso em produção.
Qual é a maneira mais thread-safe de implementar UPSERT no PostgreSQL?
Resposta
PostgreSQL agora tem UPSERT .
O método preferido de acordo com uma pergunta StackOverflow semelhante é atualmente o seguinte:
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"); Comentários
- I ' prefere usar um CTE gravável: stackoverflow.com/a/8702291/330315
- O que ' é a vantagem de um CTE gravável em relação a uma função?
- @Fran ç ois para uma coisa, velocidade. Usando um CTE, você acessa o banco de dados uma vez. Fazendo desta forma, você pode acertar duas ou mais vezes. Além disso, o otimizador pode ' t otimizar procedimentos pl / pgsql tão eficientemente quanto código SQL puro.
- @Fran ç ois Por outro lado, simultaneidade. Como o exemplo acima tem várias instruções SQL, você precisa se preocupar com as condições de corrida (o motivo do loop klugey). Uma única instrução SQL será atômica. Veja este link
- @Fran ç oisBeausoleil veja aqui e aqui para saber o motivo. Basicamente, sem um loop de nova tentativa, você precisa serializar ou tem a possibilidade de falhas devido à condição de corrida inerente.
Resposta
ATUALIZAÇÃO (20/08/2015):
Agora existe uma implementação oficial para lidar com upserts através do uso de ON CONFLICT DO UPDATE (documentação oficial) . No momento em que este artigo foi escrito, esse recurso residia atualmente no PostgreSQL 9.5 Alpha 2, que está disponível para download aqui: Diretórios de origem do Postgres .
Aqui está um exemplo, supondo que item_id seja sua chave primária:
INSERT INTO my_table (item_id, price) VALUES (123456, 10.99) ON CONFLICT (item_id) DO UPDATE SET price = EXCLUDED.price Postagem original …
Aqui está uma implementação que cheguei quando desejava obter visibilidade sobre se ocorreu uma inserção ou atualização.
A definição de upsert_data é consolidar os valores em um único recurso, em vez de precisar especificar o preço e o item_id duas vezes: uma vez para a atualização, novamente para a inserção.
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 Se você não “t como o uso de upsert_data, aqui está uma implementação 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 Comentários
- Qual é o desempenho?
- @jb. não tão bem quanto eu gostaria. Você ' vai ver Penalt de desempenho significativo s vs. executar inserções retas. No entanto, para lotes menores (digamos 1000 ou menos), este exemplo deve funcionar bem.
Resposta
Isso irá informá-lo se a inserção ou atualização ocorreu:
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" ); Se a atualização ocorrer, você receberá um insert 0, caso contrário, insira 1 ou um erro.