这里有一些插入…关于冲突……(pg 9.5+):

Insert, on conflict - do nothing. insert into dummy(id, name, size) values(1, 'new_name', 3) on conflict do nothing;` Insert, on conflict - do update, specify conflict target via column. insert into dummy(id, name, size) values(1, 'new_name', 3) on conflict(id) do update set name = 'new_name', size = 3; Insert, on conflict - do update, specify conflict target via constraint name. insert into dummy(id, name, size) values(1, 'new_name', 3) on conflict on constraint dummy_pkey do update set name = 'new_name', size = 4;

这里有一些插入…关于冲突……(pg 9.5+):

Insert, on conflict - do nothing. insert into dummy(id, name, size) values(1, 'new_name', 3) on conflict do nothing;` Insert, on conflict - do update, specify conflict target via column. insert into dummy(id, name, size) values(1, 'new_name', 3) on conflict(id) do update set name = 'new_name', size = 3; Insert, on conflict - do update, specify conflict target via constraint name. insert into dummy(id, name, size) values(1, 'new_name', 3) on conflict on constraint dummy_pkey do update set name = 'new_name', size = 4;

9.5及更新版本:

PostgreSQL 9.5及更新版本支持INSERT…当冲突(键)时执行UPDATE(和当冲突(键)时不执行任何操作)，即upsert。

与ON重复键更新的比较。

快速的解释。

有关使用方法，请参阅手册-特别是语法图中的conflict_action子句和解释性文本。

与下面给出的9.4及更老版本的解决方案不同，此特性适用于多个冲突行的情况，并且不需要排他锁定或重试循环。

添加该特性的提交在这里，围绕其开发的讨论在这里。

---

如果您使用的是9.5并且不需要向后兼容，您现在可以停止阅读。

---

9.4及以上版本:

PostgreSQL没有任何内置的UPSERT(或MERGE)功能，并且在并发使用的情况下高效地执行它是非常困难的。

本文详细讨论了这个问题。

一般来说，你有两个选择:

重试循环中的单个插入/更新操作;或 锁定表并进行批量合并

个别行重试循环

如果希望多个连接同时尝试执行插入，那么在重试循环中使用单独的行upserts是合理的选择。

PostgreSQL文档包含了一个有用的过程，可以让你在数据库内部的循环中完成这个过程。与大多数简单的解决方案不同，它防止丢失更新和插入竞赛。它只能在READ COMMITTED模式下工作，并且只有当它是您在事务中所做的唯一一件事时才安全。如果触发器或次要惟一键导致惟一违反，则该函数将无法正常工作。

这种策略效率很低。只要可行，您应该将工作排队，并按照下面描述的方式进行批量upsert。

Many attempted solutions to this problem fail to consider rollbacks, so they result in incomplete updates. Two transactions race with each other; one of them successfully INSERTs; the other gets a duplicate key error and does an UPDATE instead. The UPDATE blocks waiting for the INSERT to rollback or commit. When it rolls back, the UPDATE condition re-check matches zero rows, so even though the UPDATE commits it hasn't actually done the upsert you expected. You have to check the result row counts and re-try where necessary.

一些尝试的解决方案也没有考虑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之前。都得到0行，所以都执行INSERT。其中一个失败，出现重复键错误。

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

请不要自己动手解决这个问题。就像消息排队一样，这可能是错误的。

散装上塞带锁

有时您希望执行批量upsert，其中您有一个新数据集，希望将其合并到旧的现有数据集中。这比单独的行upserts要有效得多，应该在实际情况下优先使用。

在这种情况下，您通常遵循以下流程:

创建临时表 复制或批量插入新数据到临时表中 在EXCLUSIVE模式下锁定目标表。这允许其他事务进行SELECT，但不对表进行任何更改。 做一个更新…使用临时表中的值对现有记录进行FROM; 对目标表中不存在的行执行INSERT操作; COMMIT，释放锁。

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

BEGIN;

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

INSERT INTO newvals(id, somedata) VALUES (2, 'Joe'), (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页面 UPSERTisms在Postgres 插入，重复更新PostgreSQL? http://petereisentraut.blogspot.com/2010/05/merge-syntax.html 使用事务Upsert 函数中的SELECT或INSERT是否容易出现竞态条件? PostgreSQL wiki上的SQL MERGE 这是目前在Postgresql中实现UPSERT最常用的方法

“合并”呢?

sql标准MERGE的并发语义定义很差，不适合在不先锁定表的情况下进行上传。

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

其他星展:

插入……在MySQL中重复键更新 从MS SQL Server合并(但请参阅上面关于合并问题) 从Oracle合并(但请参阅上面关于合并问题)

我正在尝试为PostgreSQL 9.5之前版本的单次插入问题提供另一种解决方案。这个想法很简单，首先尝试执行插入，如果记录已经存在，则更新它:

do $$
begin 
  insert into testtable(id, somedata) values(2,'Joe');
exception when unique_violation then
  update testtable set somedata = 'Joe' where id = 2;
end $$;

请注意，只有在不删除表中的行时才可以应用此解决方案。

我不知道这个解决方案的效率如何，但在我看来它是合理的。

SQLAlchemy upsert for Postgres >=9.5

由于上面的大文章涵盖了Postgres版本的许多不同的SQL方法(不仅仅是问题中的非9.5)，我想补充一下如果您使用的是Postgres 9.5，如何在SQLAlchemy中做到这一点。除了实现自己的upsert，还可以使用SQLAlchemy的函数(在SQLAlchemy 1.1中添加)。就我个人而言，如果可能的话，我会推荐使用这些工具。不仅因为方便，还因为它可以让PostgreSQL处理任何可能发生的竞争条件。

我昨天给出的另一个答案(https://stackoverflow.com/a/44395983/2156909)

SQLAlchemy现在通过on_conflict_do_update()和on_conflict_do_nothing()两个方法支持ON冲突:

从文档中复制:

from sqlalchemy.dialects.postgresql import insert

stmt = insert(my_table).values(user_email='a@b.com', data='inserted data')
stmt = stmt.on_conflict_do_update(
    index_elements=[my_table.c.user_email],
    index_where=my_table.c.user_email.like('%@gmail.com'),
    set_=dict(data=stmt.excluded.data)
    )
conn.execute(stmt)

http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html?highlight=conflict#insert-on-conflict-upsert

WITH UPD AS (UPDATE TEST_TABLE SET SOME_DATA = 'Joe' WHERE ID = 2 
RETURNING ID),
INS AS (SELECT '2', 'Joe' WHERE NOT EXISTS (SELECT * FROM UPD))
INSERT INTO TEST_TABLE(ID, SOME_DATA) SELECT * FROM INS

在Postgresql 9.3上测试