几个月前,我从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中执行“UPSERT”的另一个聪明方法是执行两个连续的UPDATE/INSERT语句,每个语句都被设计为成功或没有效果。
UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
SELECT 3, 'C', 'Z'
WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);
如果已经存在“id=3”的行,则UPDATE将成功,否则将不起作用。
只有当“id=3”的行不存在时,INSERT才会成功。
您可以将这两个组合到一个字符串中,并通过从应用程序执行的单个SQL语句同时运行它们。强烈建议在一个事务中同时运行它们。
This works very well when run in isolation or on a locked table, but is subject to race conditions that mean it might still fail with duplicate key error if a row is inserted concurrently, or might terminate with no row inserted when a row is deleted concurrently. A SERIALIZABLE transaction on PostgreSQL 9.1 or higher will handle it reliably at the cost of a very high serialization failure rate, meaning you'll have to retry a lot. See why is upsert so complicated, which discusses this case in more detail.
这种方法在读提交隔离中还可能导致更新丢失,除非应用程序检查受影响的行数,并验证插入或更新是否影响了行。
我自定义“upsert”函数上面,如果你想插入和替换:
`
CREATE OR REPLACE FUNCTION upsert(sql_insert text, sql_update text)
RETURNS void AS
$BODY$
BEGIN
-- first try to insert and after to update. Note : insert has pk and update not...
EXECUTE sql_insert;
RETURN;
EXCEPTION WHEN unique_violation THEN
EXECUTE sql_update;
IF FOUND THEN
RETURN;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION upsert(text, text)
OWNER TO postgres;`
在执行之后,像这样做:
SELECT upsert($$INSERT INTO ...$$,$$UPDATE... $$)
重要的是放双元逗号,以避免编译错误
检查速度…
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子句
警告:如果同时从多个会话执行,这是不安全的(参见下面的警告)。
在postgresql中执行“UPSERT”的另一个聪明方法是执行两个连续的UPDATE/INSERT语句,每个语句都被设计为成功或没有效果。
UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
SELECT 3, 'C', 'Z'
WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);
如果已经存在“id=3”的行,则UPDATE将成功,否则将不起作用。
只有当“id=3”的行不存在时,INSERT才会成功。
您可以将这两个组合到一个字符串中,并通过从应用程序执行的单个SQL语句同时运行它们。强烈建议在一个事务中同时运行它们。
This works very well when run in isolation or on a locked table, but is subject to race conditions that mean it might still fail with duplicate key error if a row is inserted concurrently, or might terminate with no row inserted when a row is deleted concurrently. A SERIALIZABLE transaction on PostgreSQL 9.1 or higher will handle it reliably at the cost of a very high serialization failure rate, meaning you'll have to retry a lot. See why is upsert so complicated, which discusses this case in more detail.
这种方法在读提交隔离中还可能导致更新丢失,除非应用程序检查受影响的行数,并验证插入或更新是否影响了行。