如何在PostgreSQL中进行UPSERT（MERGE，INSERT…ON DUPLICATE UPDATE）？

慈悲心

这里一个非常常见的问题是如何进行upsert，这是MySQL调用内容，INSERT ... ON DUPLICATE UPDATE该标准支持该标准MERGE操作。
鉴于PostgreSQL9..5页前)，你是怎么做到的？考虑以下几点:
CREATE TABLE testtable ( id integer PRIMARY KEY, somedata text NOT NULL);INSERT INTO testtable (id,somedata) VALUES(1,'fred(二)，bob');假设你现在想UPSERT元组(2，‘Joe’)，(3,‘Alan’)，因此，新表的内容是：
(1,'fred(二)，Joe  -- Changed value of existing tuple(3,'Alan -- Added new tuple也就是说，人们在讨论论的话题upsert。至关重要的是，在多个事务处理相同表格时，任何方法都必须安全-通过使用显式锁定或以其他方式抵此产生的竞争条件。
关于插入PostgreSQL重复更新，对主题进行了广泛的讨论。，但这是关于MySQL随着时间的推移，语法的替代方法增加了许多无关的细节。我正在努力确定答案。
这些技术也可用于如果没有，则插入，否则不执行任何操作…”。

解决方案:
                                                            9.5及更高版本：PostgreSQL 9.支持更高版本INSERT ... ON CONFLICT (key) DO UPDATE（和ON CONFLICT (key) DO NOTHING），即upsert。
与的比较ON DUPLICATE KEY UPDATE。
快速解释。
请参阅手册，特别是语法图中的相关用法conflict_action以及解释性文字。
以下9.4.早期版本的解决方案不同。该功能可用于多个冲突行，无需排他锁定或重试循环。
这里提交添加功能，这里讨论功能开发。
假如你用9.5.如果不需要向后兼容，可以立即停止阅读。
9.4及更高版本：
PostgreSQL没有内置UPSERT（或MERGE）很难有效地面对并发使用。
本文详细讨论了这个问题。
通常，您必须在两个选项之间进行选择：
重试循环中的每个插入/更新操作；或
锁定表并分批合并
个别行重试循环
如果您想同时尝试插入多个连接，则在重试循环中使用单个行高插入是合理的选择。
PostgreSQL文档包含一个有用的过程，允许您在数据库内循环此操作。与大多数幼稚的解决方案不同，它可以防止丢失更新和插入竞争。然而，它是READ COMMITTED在模式下工作，只有在事务中执行唯一操作时才是安全的。如果触发器或辅助唯一键导致唯一违规，该功能将无法正常工作。
这个策略效率很低。只要可行，就要把工作排在队列里，按照下面的规定批量加。
很多尝试解决这个问题的方法都没有考虑回滚，导致更新不完整。两笔交易相互竞争；他们的成功之一INSERTS; 另一个重复的密钥错误，UPDATE而是执行一个。UPDATE等待INSERT回滚或提交块。回滚时，UPDATE重新检查条件会匹配零行，所以即使是UPDATE事实上，提交并没有完成您期望的更新。必要时必须检查结果的行计数并重试。
没有考虑一些尝试解决方案SELECT竞争。如果你尝试简单明了的方法：
-- THIS IS WRONG. DO NOT COPY IT. It's an EXAMPLE.BEGIN;UPDATE testtableSET 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它可以防止重复的键错误或更新丢失，但你不能这样做。您需要检查行数或处理重复的键错误（取决于所选方法），然后重试。
请不要为此使用自己的解决方案。就像消息队列一样，这可能是错误的。
批量更新带锁
有时候你想批量上载，这里你有一个新的数据集合并成旧的现有数据集。这远远超过了各行各业upserts只要实用，更高效、更应该是首选。
在这种情况下，通常按照以下流程进行操作：
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 testtableSET somedata = newvals.somedataFROM newvalsWHERE newvals.id = testtable.id;INSERT INTO testtableSELECT newvals.id,newvals.somedataFROM newvalsLEFT OUTER JOIN testtable ON (testtable.id = newvals.id)WHERE testtable.id IS NULL;COMMIT;

如何在PostgreSQL中进行UPSERT（MERGE，INSERT…ON DUPLICATE UPDATE）？

慈悲心 LV1