小开

根据PostgreSQL留档INSERT语句，不支持处理ON DUPLICATE KEY案例。这部分语法是专有的MySQL扩展。

小开

没有简单的命令可以做到这一点。

最正确的方法是使用函数，就像文档中的函数一样。

另一个解决方案（虽然不是那么安全）是通过返回进行更新，检查哪些行是更新的，并插入其余的行

大意如下：

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');

当然，它迟早会退出（在并发环境中），因为这里有明确的竞争条件，但通常它会起作用。

这是一个关于该主题的更长更全面的文章。

小开

最佳答案

PostgreSQL自9.5版以来具有UPSERT语法，ON CONFLICT子句。具有以下语法（类似于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的电子邮件组档案中搜索“upte”会找到一个做你可能想做的事情的例子，在手册中：

示例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_horse_with_no_name的回答。

小开

对于合并小集，使用上述函数就可以了。但是，如果您要合并大量数据，我建议查看http://mbk.projects.postgresql.org

目前我所知道的最佳实践是：

COPY新的/更新的数据到temp表中（当然，如果成本没问题，你可以做INSERT）
获取锁[可选]（建议比表锁更可取，IMO）
合并（有趣的部分）

小开

当我来到这里时，我正在寻找同样的事情，但是缺乏通用的“upte”函数让我有点困扰，所以我想您可以通过更新并将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;
$$;

也许要做你最初想做的事情，批量“upte”，你可以使用Tcl拆分sql_update并循环单个更新，性能影响将非常小，请参阅http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

成本最高的是从代码中执行查询，在数据库端执行成本要小得多

小开

警告：如果同时从多个会话执行，这是不安全的（见下面的警告）。

在postgresql中执行“UPSERT”的另一种聪明方法是执行两个顺序的UPDATE/INSERT语句，每个语句都旨在成功或无效。

UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
SELECT 3, 'C', 'Z'
WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

如果“id=3”的行已经存在，则UPDATE将成功，否则无效。

只有当“id=3”的行不存在时，INSERT才会成功。

您可以将这两者组合成一个字符串，并使用从应用程序执行的单个SQL语句运行它们。强烈建议在单个事务中一起运行它们。

当单独运行或在锁定的表上运行时，这工作得非常好，但受制于竞争条件，这意味着如果同时插入一行，它仍然可能会失败并出现重复的键错误，或者当同时删除一行时，它可能会在没有插入行的情况下终止。PostgreSQL 9.1或更高版本上的SERIALIZABLE事务将可靠地处理它，代价是非常高的序列化失败率，这意味着你必须重试很多次。请参阅怎么这么复杂，它更详细地讨论了这种情况。

这种方法也是除非应用程序检查受影响的行计数并验证#EYZ1或#EYZ2是否影响了一行，否则会在read committed隔离中丢失更新。

小开

我在上面自定义“up的”功能，如果你想插入和替换：

'

 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... $$)

使用双美元逗号以避免编译器错误很重要

检查速度…

小开

我在管理帐户设置时遇到了与名称值对相同的问题。设计标准是不同的客户端可以有不同的设置集。

我的解决方案，类似于JWP是批量擦除和替换，在您的应用程序中生成合并记录。

这是非常防弹的，独立于平台的，因为每个客户端的设置永远不会超过20个，所以这只是3个相当低的负载db调用-可能是最快的方法。

更新单个行的替代方案-检查异常然后插入-或者一些组合是可怕的代码，缓慢且经常中断，因为（如上所述）非标准SQL异常处理从db变为db-甚至发布到发布。

 #This is pseudo-code - within the application:
BEGIN TRANSACTION - get transaction lock
SELECT all current name value pairs where id = $id into a hash record
create a merge record from the current and update record
(set intersection where shared keys in new win, and empty values in new are deleted).
DELETE all name value pairs where id = $id
COPY/INSERT merged records
END TRANSACTION

小开

编辑：这不符合预期。与接受的答案不同，当两个进程同时重复调用upsert_foo时，这会产生唯一的密钥违规。

找到了！我想出了一种在一个查询中做到这一点的方法：使用UPDATE ... RETURNING来测试是否有任何行受到影响：

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');

小开

在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)

看看这些博客条目：

请注意，此解决方案没有确实防止了唯一密钥违规，但它不容易受到更新丢失的影响。
查看由Craig Ringer跟进dba.stackexchange.com

小开

就个人而言，我已经设置了一个附加到插入语句的“规则”。假设您有一个“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插入，则需要改用触发器。

小开

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

小开

类似于最喜欢的答案，但工作速度稍快：

WITH upsert AS (UPDATE spider_count SET tally=1 WHERE date='today' RETURNING *)
INSERT INTO spider_count (spider, tally) SELECT 'Googlebot', 1 WHERE NOT EXISTS (SELECT * FROM upsert)

（来源：http://www.the-art-of-web.com/sql/upsert/）

小开

UPDATE将返回修改后的行数。如果您使用JDBC（Java），您可以将此值与0进行校验，如果没有行受到影响，则触发INSERT。如果您使用其他编程语言，可能仍然可以获得修改后的行数，请检查留档。

这可能不那么优雅，但你有更简单的SQL，从调用代码中使用更琐碎。不同的是，如果你用PL/PSQL编写十行脚本，你可能应该有一个单独的单元测试。

小开

我用这个函数合并

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 ... ON CONFLICT UPDATE。

见留档。

MySQLINSERT ... 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语句一样

它也有一些不错的功能：

您可以在UPDATE上有一个WHERE子句（让您有效地将ON CONFLICT UPDATE转换为ON CONFLICT IGNORE用于某些值）

建议的插入值可作为行变量EXCLUDED使用，它与目标表具有相同的结构。您可以使用表名称获取表中的原始值。因此，在这种情况下，EXCLUDED.c将是10（因为这是我们尝试插入的），"table".c将是3，因为这是表中的当前值。您可以在SET表达式和WHERE子句中使用其中之一或两者。

有关更新的背景信息，请参阅如何在PostgreSQL中使用UPSERT（合并，插入…重复更新）？

小开

PostgreSQL>=v15

关于这个主题的重大新闻，就像在PostgreSQLv15中一样，可以使用MERGE命令。事实上，这个期待已久的功能被列为v15版本的第一个改进。

这类似于INSERT ... ON CONFLICT，但更面向批处理。它具有强大的WHEN MATCHED vsWHEN NOT MATCHED结构，在这种情况下可以使用INSERT、UPDATE或DELETE。

它不仅简化了批量更改，而且还增加了对传统UPSERT和INSERT ... ON CONFLICT的更多控制

看看官方页面上这个非常完整的样本：

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子句

插入，在PostgreSQL中重复更新？

PostgreSQL>=v15

PostgreSQL v9、v10、v11、v12、v13、v14