如何插入（合并，插入。。。在PostgreSQL中？

Question

这里有一个非常常见的问题是如何进行upsert，MySQL称之为INSERT ... ON DUPLICATE UPDATE，标准支持它作为MERGE操作的一部分。

既然PostgreSQL不直接支持它（在pg 9.5之前），你怎么做呢？请考虑以下几点：

CREATE TABLE testtable (
    id integer PRIMARY KEY,
    somedata text NOT NULL
);

INSERT INTO testtable (id, somedata) VALUES
(1, 'fred'),
(2, 'bob');

现在假设您想要“upsert”元组(2, 'Joe')，(3, 'Alan')，那么新的表内容将是：

(1, 'fred'),
(2, 'Joe'),    -- Changed value of existing tuple
(3, 'Alan')    -- Added new tuple

这就是人们在讨论upsert时所说的。关键的是，在同一个表上工作的多个事务存在时，任何方法都必须是安全的，要么使用显式锁定，要么以其他方式防御由此产生的竞争条件。

这个主题在Insert, on duplicate update in PostgreSQL?上进行了广泛的讨论，但那是关于MySQL语法的替代方案，而且随着时间的推移，它增加了一些不相关的细节。我正在研究确切的答案。

这些技术对于“如果不存在，则插入，否则不做任何事”也很有用，即“插入。。。在“忽略重复键”上。

Answer 1

9.5及更新版本：

PostgreSQL 9.5及更新版本支持INSERT ... ON CONFLICT UPDATE（和ON CONFLICT DO NOTHING），即upsert。

Comparison with ^{}。

Quick explanation。

有关用法，请参见the manual-特别是语法图中的冲突操作子句和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。

通常，您必须在两个选项中进行选择：

• 重试循环中的单个插入/更新操作；或
• 锁定表并执行批合并

单行重试循环

如果希望多个连接同时尝试执行插入，则在重试循环中使用单个行upsert是合理的选项。

The PostgreSQL documentation contains a useful procedure that'll let you do this in a loop inside the database。与大多数天真的解决方案不同，它可以防止丢失更新和插入竞赛。它只能在READ COMMITTED模式下工作，并且只有在事务中只做这件事时才是安全的。如果触发器或辅助唯一键导致唯一冲突，则函数将无法正常工作。

这种策略效率很低。只要可行，您就应该按下面的描述排队工作并进行批量追加插入。

许多试图解决此问题的解决方案都没有考虑回滚，因此导致更新不完整。两个事务相互竞争；其中一个事务成功地INSERTs；另一个事务得到一个重复的密钥错误并执行UPDATE。UPDATE块等待INSERT回滚或提交。当它回滚时，UPDATE条件重新检查与零行匹配，因此即使UPDATE提交，它实际上并没有完成预期的upsert。您必须检查结果行计数，并在必要时重新尝试。

一些尝试的解决方案也未能考虑选择比赛。如果你尝试显而易见的简单方法：

-- 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防止重复键错误或丢失更新，但您不能。您需要检查行数或处理重复键错误（取决于所选方法），然后重试。

请不要对此提出自己的解决方案。就像消息队列一样，这可能是错误的。

带锁的散装upsert

有时，您希望执行大容量upsert，其中您有一个新的数据集，您希望将其合并到旧的现有数据集中。这比单个行提升器的效率要高得多，在实际应用时应首选。

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

• CREATE表格TEMPORARY

• COPY或批量将新数据插入临时表

• LOCK目标表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, '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 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 ^{} on the PostgreSQL wiki
• Most idiomatic way to implement UPSERT in Postgresql nowadays

那MERGE呢？

SQL标准MERGE实际上具有定义不好的并发语义，不适合在不首先锁定表的情况下进行加插。

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

其他数据库：

• ^{} in MySQL
• ^{} from MS SQL Server（但请参阅上面关于MERGE问题的内容）
• ^{} from Oracle（但请参阅上面关于MERGE问题的内容）

Answer 2

我试图为9.5之前版本的PostgreSQL的单插入问题提供另一种解决方案。这样做的目的只是尝试先执行插入，如果记录已经存在，则更新它：

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

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

我不知道这个解决方案的有效性，但在我看来，它是足够合理的。

Answer 3

以下是insert ... on conflict ...（pg 9.5+）的一些示例：

• 冲突时插入-不执行任何操作。

  insert into dummy(id, name, size) values(1, 'new_name', 3)
  on conflict do nothing;`  
  

• 插入，发生冲突时-执行更新，通过列指定冲突目标。

  insert into dummy(id, name, size) values(1, 'new_name', 3)
  on conflict(id)
  do update set name = 'new_name', size = 3;  
  

• 插入，在冲突时-执行更新，通过约束名称指定冲突目标。

  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;