Postgresql 9.5和更新的支持INSERT … ON CONFLICT UPDATE(和ON CONFLICT DO NOTHING)，即upsert。

Comparison with ON DUPLICATE KEY UPDATE。

有关用法，请参见the manual – 具体来说，语法图中的conflict_action子句和the explanatory text。

与下面给出的9.4和更旧版本的解决方案不同，此功能适用于多个冲突行，并且不需要独占锁定或重试循环。

The commit adding the feature is here和the discussion around its development is here。

如果你是9.5，不需要向后兼容，你可以立即停止阅读。

9.4及以上：

Postgresql没有任何内置的UPSERT(或MERGE)设施，并且在面对并发使用时有效率是非常困难的。

This article discusses the problem in useful detail。

一般来说，您必须在两个选项之间进行选择：

>重试循环中的各个插入/更新操作;要么
>锁定表并执行批量合并

单个行重试循环

如果想要许多连接同时尝试执行插入，在重试循环中使用单独的行上行是合理的选择。

The PostgreSQL documentation contains a useful procedure that’ll let you do this in a loop inside the database.它保护丢失的更新和插入比赛，不像大多数天真的解决方案。它只能在READ COMMITTED模式下工作，只有在事务中做的唯一事情才是安全的。如果触发器或辅助唯一键导致唯一违反，则该函数将无法正常工作。

这个策略是非常低效的。每当实用，你应该排队工作，并做一个批量upsert如下所述。

许多尝试解决此问题的解决方案无法考虑回滚，因此它们导致不完整的更新。两个交易彼此竞争;其中一个成功插入;另一个获取重复的键错误，并做一个UPDATE。 UPDATE阻塞等待INSERT回滚或提交。当它回滚时，UPDATE条件重新检查匹配零行，所以即使UPDATE提交它没有实际完成你期望的upsert。您必须检查结果行计数，并在必要时重试。

一些尝试的解决方案也无法考虑SELECT比赛。如果你尝试明显和简单：

-- THIS IS WRONG. DO NOT COPY IT. It's an EXAMPLE.

BEGIN;

UPDATE testtable
SET somedata = 'blah'
WHERE id = 2;

-- Remember,this is WRONG. Do NOT COPY IT.

INSERT INTO testtable (id,somedata)
SELECT 2,'blah'
WHERE NOT EXISTS (SELECT 1 FROM testtable WHERE testtable.id = 2);

COMMIT;

那么当两个运行时同时有几种故障模式。一个是已经讨论的与更新重新检查的问题。另一个是同时UPDATE，匹配零行并继续。然后他们都做了EXISTS测试，这发生在INSERT之前。两者都获得零行，因此两者都做INSERT。一个失败，重复键错误。

这就是为什么你需要一个重试循环。你可能认为你可以防止重复的键错误或用聪明的sql丢失更新，但你不能。您需要检查行计数或处理重复的键错误(取决于所选的方法)，然后重试。

请不要为此自己解决方案。就像消息队列，这可能是错的。

带锁的批量上载

有时你想做一个批量上升，你有一个新的数据集，你想合并到一个较旧的现有数据集。这比单独的行上行文件效率​​高得多，并且在实际应用时应该是优选的。

在这种情况下，您通常遵循以下过程：

> CREATE a TEMPORARY表
> COPY或批量插入新数据到临时表中
>锁定目标表IN EXCLUSIVE MODE。这允许其他事务SELECT，但不对表进行任何更改。
>使用临时表中的值从现有记录中执行UPDATE … FROM;
>对目标表中不存在的行执行INSERT;
> COMMIT，释放锁定。

例如，对于问题中给出的示例，使用多值INSERT来填充临时表：

BEGIN;

CREATE TEMPORARY TABLE newvals(id integer,somedata text);

INSERT INTO newvals(id,somedata) VALUES (2,(3,'Alan');

LOCK TABLE testtable IN EXCLUSIVE MODE;

UPDATE testtable
SET somedata = newvals.somedata
FROM newvals
WHERE newvals.id = testtable.id;

INSERT INTO testtable
SELECT newvals.id,newvals.somedata
FROM newvals
LEFT OUTER JOIN testtable ON (testtable.id = newvals.id)
WHERE testtable.id IS NULL;

COMMIT;

相关阅读

> UPSERT wiki page
> UPSERTisms in Postgres
> Insert,on duplicate update in PostgreSQL?
> http://petereisentraut.blogspot.com/2010/05/merge-syntax.html
> Upsert with a transaction
> Is SELECT or INSERT in a function prone to race conditions?
> SQL MERGE on the PostgreSQL wiki
> Most idiomatic way to implement UPSERT in Postgresql nowadays

MERGE怎么样？

sql标准MERGE实际上具有较差的并发语义，并且不适合不首先锁定表的上升。

这是一个非常有用的OLAP语句，用于数据合并，但它并不是一个有用的并发安全上升的解决方案。有很多建议，使用其他DBMS的人使用MERGE的upserts，但它实际上是错误的。

其他DB：

> INSERT ... ON DUPLICATE KEY UPDATE in MySQL
> MERGE from MS SQL Server(但参见上面的MERGE问题)
> MERGE from Oracle(但参见上面的MERGE问题)