几个月前，我从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”函数让我有点困扰，所以我认为你可以通过更新和插入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的答案。
我自定义“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... $$)
重要的是放双元逗号，以避免编译错误
检查速度…
我用这个函数归并
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
最高的成本是从您的代码执行查询，在数据库方面的执行成本要小得多
编辑:这不能正常工作。与接受的答案不同，当两个进程并发重复调用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');
