sql - 插入多行时重复项会发生什么情况？

Question

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

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

我想知道如果它为插入插入一个重复的键会发生什么。它会停止整个查询并抛出异常吗？或者它会忽略该特定行的插入并继续前进吗？

Answer 1

INSERT 将只插入所有行并且没有会发生特殊情况，除非您有某种constraint不允许重复/重叠值(PRIMARY KEY、UNIQUE、CHECK 或 EXCLUDE 约束)- 您没有提到在你的问题中。但这可能就是您担心的。

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

通常，如果违反了任何约束，就会引发异常(除非陷入子事务中，就像在 plpgsql 这样的过程服务器端语言中可能发生的那样)不仅会回滚语句，还会回滚整个交易。

没有并发写入

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

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

• Select rows which are not present in other table

• 并且不要忘记删除插入集中内的重复项，这不会被半反连接排除哪里不存在...

同时处理两者的一种技术是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(或其他技术之一)。见:

• Using EXCEPT clause in PostgreSQL

一般来说，如果目标表大，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
   );

如果有很多重复项，首先将它们折叠到源代码中是值得的。否则少用一个子查询。

相关:

• Select rows which are not present in other table

并发写入

使用 Postgres UPSERT 实现 INSERT ... ON CONFLICT ...在 Postgres 9.5 或更高版本中:

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!

进一步阅读:

• How to use RETURNING with ON CONFLICT in PostgreSQL?
• How do I insert a row which contains a foreign key?

文档:

• The manual
• The commit page
• The Postgres Wiki page

Craig 对 UPSERT 问题的引用答案:

• How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?