几个月前，我从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
其他回答
我用这个函数归并
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”函数让我有点困扰，所以我认为你可以通过更新和插入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
最高的成本是从您的代码执行查询，在数据库方面的执行成本要小得多
PostgreSQL从9.5版本开始使用UPSERT语法，带有ON冲突子句。使用以下语法(类似于MySQL)
INSERT INTO the_table (id, column_1, column_2)
VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE
SET column_1 = excluded.column_1,
column_2 = excluded.column_2;
在postgresql的电子邮件组档案中搜索“upsert”，可以在手册中找到一个做你可能想做的事情的例子:
38-2示例。UPDATE/INSERT异常 下面的例子使用异常处理来执行UPDATE或INSERT:
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
-- first try to update the key
-- note that "a" must be unique
UPDATE db SET b = data WHERE a = key;
IF found THEN
RETURN;
END IF;
-- not there, so try to insert the key
-- if someone else inserts the same key concurrently,
-- we could get a unique-key failure
BEGIN
INSERT INTO db(a,b) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing, and loop to try the UPDATE again
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');
在黑客邮件列表中可能有一个如何批量使用9.1及以上版本的cte的示例:
WITH foos AS (SELECT (UNNEST(%foo[])).*)
updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)
INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)
WHERE updated.id IS NULL;
有关更清晰的示例，请参阅a_horis_with_no_name的答案。
就我个人而言，我已经设置了一个附加到插入语句的“规则”。假设你有一个“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');
当然，(在并发环境中)它迟早会崩溃，因为这里有明确的竞态条件，但通常它会工作。
这里有一篇关于这个主题的更长的、更全面的文章。
