如何使用PostgreSQL模拟“insert ignore”和“on duplicate key update”（SQL合并）？

Question

一些 SQL 服务器有一个功能，在违反主/唯一键约束时跳过INSERT。例如，MySQL 提供了 INSERT IGNORE。

如何使用 PostgreSQL 模拟 INSERT IGNORE 和 ON DUPLICATE KEY UPDATE 的最佳方法是什么？

- gpilotino

---

参见：https://dev59.com/HFXTa4cB1Zd3GeqPyh0n - Dave Jarvis

参见：https://dev59.com/uHNA5IYBdhLWcg3wC5Xh 和 http://dba.stackexchange.com/questions/78510/why-is-cte-open-to-lost-updates - user330315

截至9.5版本，它可以在本地实现：https://dev59.com/onNA5IYBdhLWcg3wVcFx#34639631 - warren

在PgSQL 9.5上模拟MySQL中的“ON DUPLICATE KEY UPDATE”仍然有些不可能，因为PgSQL的“ON CLAUSE”等效需要您提供约束名，而MySQL可以捕获任何约束而无需定义它。这阻止了我在不重写查询的情况下“模拟”此功能。 - NeverEndingQueue

Answer 1

在PostgreSQL 9.5中，现在有了本地功能（就像MySQL几年前就有的）：

INSERT ... ON CONFLICT DO NOTHING/UPDATE ("UPSERT")

9.5为"UPSERT"操作提供支持。 INSERT被扩展以接受ON CONFLICT DO UPDATE / IGNORE子句。此子句指定在出现重复冲突时采取的替代操作。

...

进一步的新语法示例：

INSERT INTO user_logins (username, logins)
VALUES ('Naomi',1),('James',1) 
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;

- warren

Answer 2

编辑：如果您错过了warren的回答，PG9.5现在已经原生支持此功能；是时候升级了！

---

在Bill Karwin的回答基础上，为了解释一个基于规则的方法会是什么样子（从同一数据库中的另一个模式转移，并且有多列主键）：

CREATE RULE "my_table_on_duplicate_ignore" AS ON INSERT TO "my_table"
  WHERE EXISTS(SELECT 1 FROM my_table 
                WHERE (pk_col_1, pk_col_2)=(NEW.pk_col_1, NEW.pk_col_2))
  DO INSTEAD NOTHING;
INSERT INTO my_table SELECT * FROM another_schema.my_table WHERE some_cond;
DROP RULE "my_table_on_duplicate_ignore" ON "my_table";

注意：该规则适用于所有的INSERT操作，直到该规则被删除，所以并不完全是临时的。

- EoghanM

---

@EoghanM 我在postgresql 9.3中测试了这个规则，发现仍然可以使用多行插入语句插入重复数据，例如：INSERT INTO "my_table" (a,b),(a,b); （假设行（a，b）尚不存在于“my_table”中。） - sema

@sema，明白了 - 这意味着规则在插入所有数据之前执行，并且在每行插入后不会重新执行。一种方法是首先将数据插入另一个没有任何约束的临时表中，然后执行 INSERT INTO "my_table" SELECT DISTINCT ON (pk_col_1, pk_col_2) * FROM the_tmp_table;。 - EoghanM

@EoghanM 另一种方法是暂时放宽重复约束，并在插入时接受重复项，但随后使用 DELETE FROM my_table WHERE ctid IN (SELECT ctid FROM (SELECT ctid,ROW_NUMBER() OVER (PARTITION BY pk_col_1,pk_col_2) AS rn FROM my_table) AS dups WHERE dups.rn > 1); 删除重复项。 - sema

我尝试了这个规则，运行时间似乎与我插入的行数呈二次关系。有什么想法吗？看起来像是一个 bug，因为天真的实现应该在线性时间内工作（O(n*m)，其中 n 是插入的行数，m 是表中已有的行数）。 - oerpli

这对我有用！问题在于，您必须确保要插入的批量数据中没有重复的元组，否则您仍将收到“重复键违规”错误。为了实现这一点，您仍需要使用关键字distinct修改select子句：insert into my_table (col1, col2) select distinct col3, col4 from another_table where ... - snowfox

Answer 3

对于那些使用Postgres 9.5或更高版本的用户，新的ON CONFLICT DO NOTHING语法应该可以工作：

INSERT INTO target_table (field_one, field_two, field_three ) 
SELECT field_one, field_two, field_three
FROM source_table
ON CONFLICT (field_one) DO NOTHING;

对于那些使用早期版本的人，可以使用右连接（right join）代替：

INSERT INTO target_table (field_one, field_two, field_three )
SELECT source_table.field_one, source_table.field_two, source_table.field_three
FROM source_table 
LEFT JOIN target_table ON source_table.field_one = target_table.field_one
WHERE target_table.field_one IS NULL;

- hanmari

---

第二种方法在并发环境下进行大量插入时无法正常工作。如果在执行此查询时向target_table插入了另一行，并且它们的键确实重复，则会出现“唯一性冲突：7 ERROR：违反唯一约束”的错误。我相信锁定target_table会有所帮助，但并发性显然会受到影响。 - G. Kashtanov

"ON CONFLICT (field_one) DO NOTHING" 是答案中最好的部分。 - Abel Callejo

ON CONFLICT DO NOTHING 的 Props。太喜欢了。 - D. Melo

Answer 4

尝试执行UPDATE，如果它不修改任何行，则表示它不存在，所以进行插入。显然，您需要在事务内执行此操作。

如果您不想在客户端上添加额外的代码，当然可以将其包装在函数中。在这种思路中非常罕见的竞争条件中，您还需要一个循环。

文档中有一个示例：http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html，在底部有一个40-2的示例。

这通常是最简单的方法。您可以使用规则执行某些操作，但是可能会更加混乱。我建议优先采用包含在函数中的方法。

这适用于单个或少量行的值。如果您正在处理大量的行，例如来自子查询，则最好将其拆分为两个查询，一个用于INSERT，另一个用于UPDATE（作为适当的联接/子选择 - 无需两次编写主过滤器）。

- Magnus Hagander

---

“如果你正在处理大量的行”，这正是我的情况。我想批量更新/插入行，使用mysql可以只用一个查询而不需要任何循环来做到这一点。现在我想知道是否在postgresql中也能够通过仅使用一个查询来实现批量更新或插入。你说：“最好将其拆分为两个查询，一个用于插入，另一个用于更新”，但是我如何执行一个不会在重复键上引发错误的插入？（即“INSERT IGNORE”） - gpilotino

Magnus 的意思是你可以使用以下查询语句："start transaction; create temporary table temporary_table as select * from test where false; copy temporary_table from 'data_file.csv'; lock table test; update test set data=temporary_table.data from temporary_table where test.id=temporary_table.id; insert into test select * from temporary_table where id not in (select id from test) as a" - Tometzky

更新：使用PostgreSQL 9.5，这现在只需要简单地使用“INSERT…ON CONFLICT DO NOTHING;”即可。另请参见答案https://dev59.com/onNA5IYBdhLWcg3wVcFx#34639631。 - Alphaaa

重要的是，标准的SQL MERGE 并不是并发安全的 upsert，除非您首先使用 LOCK TABLE 。虽然许多人这样使用它，但这是错误的。 - Craig Ringer

从v9.5开始，它现在是一项“本地”功能，请查看@Alphaaa的评论（只是广告评论，宣传答案）。 - Camilo Delvasto

Answer 5

为了获得"insert ignore"逻辑，您可以像下面这样做。我发现从文字值的选择语句中简单插入最有效，然后您可以使用NOT EXISTS子句屏蔽重复的键。为了获得重复时的更新逻辑，我认为需要一个pl/pgsql循环。

INSERT INTO manager.vin_manufacturer
(SELECT * FROM( VALUES
  ('935',' Citroën Brazil','Citroën'),
  ('ABC', 'Toyota', 'Toyota'),
  ('ZOM',' OM','OM')
  ) as tmp (vin_manufacturer_id, manufacturer_desc, make_desc)
  WHERE NOT EXISTS (
    --ignore anything that has already been inserted
    SELECT 1 FROM manager.vin_manufacturer m where m.vin_manufacturer_id = tmp.vin_manufacturer_id)
)

- Benbob

---

如果 tmp 包含一个重复的行，这种情况可能会发生怎么办？ - Henley

你可以始终使用DISTINCT关键字进行选择。 - Benbob

顺便提一下，"WHERE NOT EXISTS" 不适用于跨多个事务操作，因为不同的事务无法看到其他事务新增的数据。 - Dave Johansen

Answer 6

INSERT INTO mytable(col1,col2) 
    SELECT 'val1','val2' 
    WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE col1='val1')

- user2342158

---

多个事务同时尝试执行相同操作会产生什么影响？在 where not exists 执行和 insert 执行之间，是否有可能其他事务插入一行数据？如果Postgres可以防止这种情况，那么当所有事务到达此处时，Postgres是否引入了同步点？ - Καrτhικ

这在多个事务中不起作用，因为新添加的数据对其他事务不可见。 - Dave Johansen

Answer 7

正文翻译：正如@hanmari在他的评论中提到的那样，当插入数据到Postgres表时，使用on conflict(..) do nothing是避免插入重复数据的最佳代码。

注：该评论可能指的是在使用PostgreSQL数据库时，为了避免插入重复数据，可以使用"INSERT INTO ... ON CONFLICT DO NOTHING"语句。

query = "INSERT INTO db_table_name(column_name)
         VALUES(%s) ON CONFLICT (column_name) DO NOTHING;"

ON CONFLICT语句允许插入语句仍然插入数据行。查询和值代码是将Excel中的数据插入到Postgres数据库表中的示例。我已经在Postgres表中添加了约束条件，以确保ID字段唯一。而不是删除相同的数据行，我添加了一行SQL代码，重新编号从1开始的ID列。例如：

q = 'ALTER id_column serial RESTART WITH 1'

如果我的数据有一个ID字段，我不会将其用作主要的ID/序列ID，而是创建一个ID列并将其设置为序列。 希望这些信息对大家有所帮助。 *我没有软件开发/编码方面的大学学位。我所知道的关于编码的一切都是自学的。

- Yankeeownz

Answer 8

看起来PostgreSQL支持称为“规则”的模式对象。

http://www.postgresql.org/docs/current/static/rules-update.html

您可以为给定表创建一个ON INSERT的规则，使其在具有给定主键值的行已存在时执行NOTHING操作，否则使其执行UPDATE而不是INSERT操作。

我自己没有尝试过，因此无法从经验中讲述或提供示例。

- Bill Karwin

---

如果我理解正确，这些规则是在每次调用语句时执行的触发器。如果我只想将规则应用于一个查询怎么办？我必须创建规则，然后立即销毁它吗？（竞态条件怎么办？） - gpilotino

我也有同样的问题。在PostgreSQL中，规则机制是我能找到的最接近MySQL的INSERT IGNORE或ON DUPLICATE KEY UPDATE的机制。如果我们在谷歌上搜索"postgresql on duplicate key update"，会发现其他人也推荐使用规则机制，尽管规则适用于任何INSERT，而不仅仅是临时基础上的操作。 - Bill Karwin

PostgreSQL 支持事务性 DDL，这意味着如果您在单个事务内创建并删除一个规则，则该规则将从未在事务之外可见（因此也永远不会在事务之外产生任何影响）。 - cdhowie

Answer 9

这个解决方案避免了使用规则：

BEGIN
   INSERT INTO tableA (unique_column,c2,c3) VALUES (1,2,3);
EXCEPTION 
   WHEN unique_violation THEN
     UPDATE tableA SET c2 = 2, c3 = 3 WHERE unique_column = 1;
END;

但它有一个性能缺陷（请参见PostgreSQL.org）：

包含EXCEPTION子句的语句块比不含该子句的语句块在进入和退出时显著地更耗费性能。因此，不要没有必要就使用EXCEPTION。

- NumberFour

Answer 10

在批量操作中，您可以在插入之前始终删除行。删除不存在的行不会导致错误，因此可以安全地跳过。

- David Noriega

---

这种方法很容易遇到奇怪的竞态条件，我不建议使用它。 - Steven Schlansker

+1 这很简单且通用。如果小心使用，这实际上可以成为一个简单的解决方案。 - Wouter van Nifterick

当现有数据在插入后被更改（但不是在重复键上），且我们希望保留更新时，它也无法工作。这种情况发生在为一些略微不同的系统编写SQL脚本时，例如在生产、QA、开发和测试系统上运行的数据库更新。 - Hanno Fietz

如果您使用DEFERRABLE INITIALLY DEFERRED标志创建外键，那么外键就不会成为问题。 - temoto