当插入多行时，重复的行会发生什么？

Question

我正在运行一个Python脚本，将大量数据插入到Postgres数据库中，我使用单个查询来执行多行插入：

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

我想知道如果插入时遇到重复的键会发生什么。它会停止整个查询并抛出异常吗？还是只会忽略那一行的插入并继续执行下去？

- Garrigan Stafford

---

基本信息缺失：Postgres版本，表定义显示所有约束。 - Erwin Brandstetter

Answer 1

“INSERT”语句将只插入所有行，除非您在问题中没有提到的某种约束禁止重复/重叠值（PRIMARY KEY、UNIQUE、CHECK或EXCLUDE约束），否则不会发生任何特殊情况。但这可能是您担心的问题。

假设在(col1,col2)上有一个UNIQUE或PK约束，您正在处理一种典型的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子句

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

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或更高版本中使用Postgres的UPSERT实现{{link2：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中使用RETURNING与ON CONFLICT？
• 如何插入包含外键的行？

文档：

• 手册
• 提交页面
• Postgres维基页面

Craig关于UPSERT问题的参考答案：

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

- Erwin Brandstetter

Answer 2

它会停止整个查询并抛出异常吗？是的。

为了避免这种情况，您可以查看以下SO问题here，其中描述了如何在某些已存在于DB中的插入键时避免Postgres抛出多个插入错误。

您应该基本上这样做：

INSERT INTO DBtable
        (id, field1)
    SELECT 1, 'value'
    WHERE
        NOT EXISTS (
            SELECT id FROM DBtable WHERE id = 1
);

- Alexandros