几个月前，我从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文档中这是被覆盖的。
为了澄清，我想插入一些东西，如果它们已经存在，则更新它们。
当前回答
我自定义“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... $$)
重要的是放双元逗号，以避免编译错误
检查速度…
其他回答
根据INSERT语句的PostgreSQL文档，不支持处理ON DUPLICATE KEY情况。这部分语法是一个专有的MySQL扩展。
没有简单的命令可以做到这一点。
最正确的方法是使用函数，就像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');
当然，(在并发环境中)它迟早会崩溃，因为这里有明确的竞态条件，但通常它会工作。
这里有一篇关于这个主题的更长的、更全面的文章。
CREATE OR REPLACE FUNCTION save_user(_id integer, _name character varying)
RETURNS boolean AS
$BODY$
BEGIN
UPDATE users SET name = _name WHERE id = _id;
IF FOUND THEN
RETURN true;
END IF;
BEGIN
INSERT INTO users (id, name) VALUES (_id, _name);
EXCEPTION WHEN OTHERS THEN
UPDATE users SET name = _name WHERE id = _id;
END;
RETURN TRUE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT
下面是如何使用参数而不使用特殊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
也许会有帮助
编辑:这不能正常工作。与接受的答案不同，当两个进程并发重复调用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');
