几个月前,我从Stack Overflow上的一个回答中学到了如何在MySQL中使用以下语法一次执行多个更新:
INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z)
ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2);
我现在已经切换到PostgreSQL,显然这是不正确的。它引用了所有正确的表,所以我假设这是使用不同关键字的问题,但我不确定在PostgreSQL文档中这是被覆盖的。
为了澄清,我想插入一些东西,如果它们已经存在,则更新它们。
PostgreSQL >= v15
关于这个话题的大新闻是在PostgreSQL v15中,可以使用MERGE命令。事实上,这个期待已久的特性被列为v15版本的第一个改进。
这类似于INSERT…ON冲突,但更面向批处理。它具有强大的WHEN MATCHED vs WHEN NOT MATCHED结构,可以在这种情况下执行INSERT、UPDATE或DELETE操作。
它不仅简化了批量更改,而且甚至比传统的UPSERT和INSERT增加了更多的控制…在冲突
看看这个非常完整的样本从官方页面:
MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
DELETE;
PostgreSQL v9, v10, v11, v12, v13, v14
如果版本低于v15而高于v9.5,可能最好的选择是使用UPSERT语法,带有ON CONFLICT子句
当我来这里的时候,我也在寻找同样的东西,但缺乏通用的“upsert”函数让我有点困扰,所以我认为你可以通过更新和插入sql作为该函数的参数形式手册
它看起来是这样的:
CREATE FUNCTION upsert (sql_update TEXT, sql_insert TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
LOOP
-- first try to update
EXECUTE sql_update;
-- check if the row is found
IF FOUND THEN
RETURN;
END IF;
-- not found so insert the row
BEGIN
EXECUTE sql_insert;
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing and loop
END;
END LOOP;
END;
$$;
也许要做你最初想做的事情,批处理“upsert”,你可以使用Tcl分割sql_update并循环各个更新,性能的影响将非常小,参见http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php
最高的成本是从您的代码执行查询,在数据库方面的执行成本要小得多
就我个人而言,我已经设置了一个附加到插入语句的“规则”。假设你有一个“dns”表,记录每个客户每次的dns点击量:
CREATE TABLE dns (
"time" timestamp without time zone NOT NULL,
customer_id integer NOT NULL,
hits integer
);
您希望能够重新插入具有更新值的行,或者在这些行不存在时创建它们。输入customer_id和时间。就像这样:
CREATE RULE replace_dns AS
ON INSERT TO dns
WHERE (EXISTS (SELECT 1 FROM dns WHERE ((dns."time" = new."time")
AND (dns.customer_id = new.customer_id))))
DO INSTEAD UPDATE dns
SET hits = new.hits
WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id));
更新:如果同时进行插入,则有可能失败,因为它将生成unique_violation异常。但是,未终止的事务将继续并成功,您只需要重复已终止的事务。
然而,如果一直都有大量的插入发生,你会想在插入语句周围放一个表锁:SHARE ROW EXCLUSIVE锁将阻止任何可能在目标表中插入、删除或更新行的操作。但是,不更新唯一密钥的更新是安全的,因此如果没有操作将这样做,则使用建议锁。
此外,COPY命令不使用RULES,因此如果使用COPY进行插入,则需要使用触发器。
没有简单的命令可以做到这一点。
最正确的方法是使用函数,就像docs中的函数一样。
另一种解决方案(尽管不是那么安全)是在执行update的同时返回,检查哪些行是更新的,然后插入其余的行
大致如下:
update table
set column = x.column
from (values (1,'aa'),(2,'bb'),(3,'cc')) as x (id, column)
where table.id = x.id
returning id;
假设返回id:2:
insert into table (id, column) values (1, 'aa'), (3, 'cc');
当然,(在并发环境中)它迟早会崩溃,因为这里有明确的竞态条件,但通常它会工作。
这里有一篇关于这个主题的更长的、更全面的文章。