插入多行时重复会发生什么? [英] What happens with duplicates when inserting multiple rows?

问题描述

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

I am running a python script that inserts a large amount of data into a Postgres database, I use a single query to perform multiple row inserts:

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

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

I was wondering what would happen if it hits a duplicate key for the insert. Will it stop the entire query and throw an exception? Or will it merely ignore the insert of that specific row and move on?

推荐答案

INSERT 只会插入所有行，什么都不会发生, 除非你有某种约束 不允许重复/重叠值(PRIMARY KEY、UNIQUE、CHECK 或 EXCLUDE约束)-您在问题中没有提到.但这就是您可能担心的问题.

The INSERT will just insert all rows and nothing special will happen, unless you have some kind of constraint disallowing duplicate / overlapping values (PRIMARY KEY, UNIQUE, CHECK or EXCLUDE constraint) - which you did not mention in your question. But that's what you are probably worried about.

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

Assuming a UNIQUE or PK constraint on (col1,col2), you are dealing with a textbook UPSERT situation. Many related questions and answers to find here.

一般来说，如果 any 约束被违反，则会引发异常(除非陷入子事务中，就像在 plpgsql 这样的过程服务器端语言中可能发生的那样)不仅会回滚语句，还会回滚整个交易.

Generally, if any constraint is violated, an exception is raised which (unless trapped in subtransaction like it's possible in a procedural server-side language like plpgsql) will roll back not only the statement, but the whole transaction.

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

I.e.: No other transactions will try to write to the same table at the same time.

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

• Exclude rows that are already in the table with WHERE NOT EXISTS ... or any other applicable technique:

选择哪些行其他表中不存在

并且不要忘记删除插入集内的重复，这不会被半反连接排除不存在的地方...

And don't forget to remove duplicates within the inserted set as well, which would not be excluded by the semi-anti-join WHERE NOT EXISTS ...

同时处理这两种情况的一种技术是 EXCEPT:

One technique to deal with both at once would be 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 (或其他技术之一).见:

EXCEPT without the key word ALL folds duplicate rows in the source. If you know there are no dupes, or you don't want to fold duplicates silently, use EXCEPT ALL (or one of the other techniques). See:

• 在 PostgreSQL 中使用 EXCEPT 子句

一般来说，如果目标表是大，WHERE NOT EXISTS结合源上的DISTINCT可能会更快:

Generally, if the target table is big, WHERE NOT EXISTS in combination with DISTINCT on the source will probably be faster:

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
   );

如果可以有很多骗子，首先将它们折叠到源中是值得的.否则少用一个子查询.

If there can be many dupes, it pays to fold them in the source first. Else use one subquery less.

相关:

• 选择不存在的行其他表

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

Use the Postgres UPSERT implementation INSERT ... ON CONFLICT ... in Postgres 9.5 or later:

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!

进一步阅读:

• 如何在 ON CONFLICT 中使用 RETURNING在 PostgreSQL 中?
• 如何插入包含外键的行?

文档:

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

Craig 对 UPSERT 问题的参考答案:

Craig's reference answer for UPSERT problems:

• 如何在 PostgreSQL 中进行 UPSERT(合并、插入...在重复更新时)?

这篇关于插入多行时重复会发生什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！