几个月前，我从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_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');
其他回答
编辑:这不能正常工作。与接受的答案不同，当两个进程并发重复调用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');
下面是如何使用参数而不使用特殊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”函数上面，如果你想插入和替换:
｀
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... $$)
重要的是放双元逗号，以避免编译错误
检查速度…
就我个人而言，我已经设置了一个附加到插入语句的“规则”。假设你有一个“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进行插入，则需要使用触发器。
在PostgreSQL 9.1中，这可以使用可写的CTE(公共表表达式)来实现:
WITH new_values (id, field1, field2) as (
values
(1, 'A', 'X'),
(2, 'B', 'Y'),
(3, 'C', 'Z')
),
upsert as
(
update mytable m
set field1 = nv.field1,
field2 = nv.field2
FROM new_values nv
WHERE m.id = nv.id
RETURNING m.*
)
INSERT INTO mytable (id, field1, field2)
SELECT id, field1, field2
FROM new_values
WHERE NOT EXISTS (SELECT 1
FROM upsert up
WHERE up.id = new_values.id)
看看这些博客:
通过可写CTE上传 等待9.1 -可写cte 为什么upsert这么复杂?
请注意，此解决方案不能防止唯一键违反，但不容易丢失更新。 请在dba.stackexchange.com上查看Craig Ringer的后续报道
