几个月前，我从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文档中这是被覆盖的。
为了澄清，我想插入一些东西，如果它们已经存在，则更新它们。
当前回答
我用这个函数归并
CREATE OR REPLACE FUNCTION merge_tabla(key INT, data TEXT)
RETURNS void AS
$BODY$
BEGIN
IF EXISTS(SELECT a FROM tabla WHERE a = key)
THEN
UPDATE tabla SET b = data WHERE a = key;
RETURN;
ELSE
INSERT INTO tabla(a,b) VALUES (key, data);
RETURN;
END IF;
END;
$BODY$
LANGUAGE plpgsql
其他回答
编辑:这不能正常工作。与接受的答案不同，当两个进程并发重复调用upsert_foo时，会产生唯一键违反。
尤里卡!我想出了一个方法，在一个查询:使用UPDATE…返回测试是否有任何行受到影响:
CREATE TABLE foo (k INT PRIMARY KEY, v TEXT);
CREATE FUNCTION update_foo(k INT, v TEXT)
RETURNS SETOF INT AS $$
UPDATE foo SET v = $2 WHERE k = $1 RETURNING $1
$$ LANGUAGE sql;
CREATE FUNCTION upsert_foo(k INT, v TEXT)
RETURNS VOID AS $$
INSERT INTO foo
SELECT $1, $2
WHERE NOT EXISTS (SELECT update_foo($1, $2))
$$ LANGUAGE sql;
UPDATE必须在单独的过程中完成，因为，不幸的是，这是一个语法错误:
... WHERE NOT EXISTS (UPDATE ...)
现在它按预期工作:
SELECT upsert_foo(1, 'hi');
SELECT upsert_foo(1, 'bye');
SELECT upsert_foo(3, 'hi');
SELECT upsert_foo(3, 'bye');
我用这个函数归并
CREATE OR REPLACE FUNCTION merge_tabla(key INT, data TEXT)
RETURNS void AS
$BODY$
BEGIN
IF EXISTS(SELECT a FROM tabla WHERE a = key)
THEN
UPDATE tabla SET b = data WHERE a = key;
RETURN;
ELSE
INSERT INTO tabla(a,b) VALUES (key, data);
RETURN;
END IF;
END;
$BODY$
LANGUAGE plpgsql
在PostgreSQL 9.5及更新版本中，你可以使用INSERT…冲突更新。
请参见文档。
MySQL插入…“ON DUPLICATE KEY UPDATE”可以直接转换为“ON CONFLICT UPDATE”。它们都不是sql标准语法，它们都是特定于数据库的扩展。没有使用MERGE是有原因的，创建新语法不是为了好玩。(MySQL的语法也有问题，这意味着它没有被直接采用)。
例如，给定的设置:
CREATE TABLE tablename (a integer primary key, b integer, c integer);
INSERT INTO tablename (a, b, c) values (1, 2, 3);
MySQL查询:
INSERT INTO tablename (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
就变成:
INSERT INTO tablename (a, b, c) values (1, 2, 10)
ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;
差异:
必须指定用于惟一性检查的列名(或惟一约束名)。这是ON CONFLICT (columnname) DO 必须使用关键字SET，就好像这是一条普通的UPDATE语句一样
它也有一些不错的功能:
You can have a WHERE clause on your UPDATE (letting you effectively turn ON CONFLICT UPDATE into ON CONFLICT IGNORE for certain values) The proposed-for-insertion values are available as the row-variable EXCLUDED, which has the same structure as the target table. You can get the original values in the table by using the table name. So in this case EXCLUDED.c will be 10 (because that's what we tried to insert) and "table".c will be 3 because that's the current value in the table. You can use either or both in the SET expressions and WHERE clause.
有关upsert的背景知识，请参阅如何upsert(合并，插入…重复更新)在PostgreSQL?
下面是如何使用参数而不使用特殊sql结构进行upsert的示例 如果你有特殊的条件(有时你不能用on conflict，因为你不能创建约束)
WITH upd AS
(
update view_layer set metadata=:metadata where layer_id = :layer_id and view_id = :view_id returning id
)
insert into view_layer (layer_id, view_id, metadata)
(select :layer_id layer_id, :view_id view_id, :metadata metadata FROM view_layer l
where NOT EXISTS(select id FROM upd WHERE id IS NOT NULL) limit 1)
returning id
也许会有帮助
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子句
