插入多行时重复项会怎样？

Question

我正在运行一个将大量数据插入Postgres数据库的python脚本，我使用一个查询执行多次行插入：

INSERT INTO table (col1,col2) VALUES ('v1','v2'),('v3','v4') ... etc

我想知道如果它插入一个重复键会发生什么。它会停止整个查询并引发异常吗？还是仅忽略该特定行的插入并继续？

Answer 1

该INSERT会只是插入所有行 _ 没什么_ 特别不会发生， 除非 你有某种
约束条件

不允许重复/重叠值（PRIMARY KEY，UNIQUE，CHECK或EXCLUDE约束） -你没有在你的问题提了。但这就是您可能担心的。

假设UNIQUE或上有PK约束(col1,col2)，则您正在处理教科书UPSERT情况。许多相关的问题和解答可以在这里找到。

通常，如果违反了 任何 约束，则会引发异常（除非陷入过程服务器端语言，如plpgsql），该异常不仅会回滚语句，还会回滚 _ 整个事务_ 。

没有并发写入

即：没有其他事务会尝试同时写入同一表。

• 使用WHERE NOT EXISTS ...或任何其他适用的技术排除表中已经存在的行：

• 选择其他表中不存在的行

• 而且不要忘了删除重复 内 插入设置为好，这将 不会 被排除半反连接WHERE NOT EXISTS ...

一次处理这两项的一种技术是EXCEPT：

INSERT INTO tbl (col1, col2)
VALUES
  (text 'v1', text 'v2')  -- explicit type cast may be needed in 1st row
, ('v3', 'v4')
, ('v3', 'v4')  -- beware of dupes in source
EXCEPT SELECT col1, col2 FROM tbl;

EXCEPT
没有关键字的话会ALL折叠源中的重复行。如果您知道没有重复项，或者您不想静静地折叠重复项，请使用EXCEPT ALL（或其他一种技术）。看：

• 在PostgreSQL中使用EXCEPT子句

通常，如果目标表 很大 ，则WHERE NOT EXISTS结合使用DISTINCT源表可能会更快：

INSERT INTO tbl (col1, col2)
SELECT *
FROM  (
   SELECT DISTINCT *
   FROM  (
       VALUES
         (text 'v1', text'v2')
       , ('v3', 'v4')
       , ('v3', 'v4')  -- dupes in source
      ) t(c1, c2)
   ) t
WHERE NOT EXISTS (
   SELECT FROM tbl
   WHERE  col1 = t.c1 AND col2 = t.c2
   );

如果可以有很多骗子，则首先将其折叠在源中是值得的。否则，少使用一个子查询。

有关的：

• 选择其他表中不存在的行

并发写入

在 Postgres 9.5* 或更高版本中使用PostgresUPSERT实现： INSERT ... ON CONFLICT ... *

INSERT INTO tbl (col1,col2)
SELECT DISTINCT *  -- still can't insert the same row more than once
FROM  (
   VALUES
     (text 'v1', text 'v2')
   , ('v3','v4')
   , ('v3','v4')  -- you still need to fold dupes in source!
  ) t(c1, c2)
ON CONFLICT DO NOTHING;  -- ignores rows with *any* conflict!

进一步阅读：

• 如何在PostgreSQL中结合使用ON CONFLICT和RETURNING？
• 如何插入包含外键的行？

说明文件：

• 手册
• 提交页面
• Postgres Wiki页面

克雷格（Craig）针对UPSERT问题的参考答案：

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