插入多行时会出现重复的情况？ [英] What happens with duplicates when inserting multiple rows?
问题描述
INSERT INTO表（col1，col2）VALUES（'v1'，'v2'），（'v3'，'v4'）... etc/ pre>
我想知道如果它插入一个重复的键，会发生什么。会阻止整个查询并抛出异常吗？或者只是忽略该特定行的插入并移动？
解决方案
INSERT只会插入所有的行， 特殊会发生，除非你有某种 约束 不允许重复/重叠值（PRIMARY KEY，UNIQUE，CHECK或EXCLUDEconstraint） - 你没有提到你的问题。但是这是你可能担心的。
假设
UNIQUE或PK约束（col1，col2），您正在处理教科书UPSERT情况。许多相关的问题和答案可以在这里找到。
一般而言，如果任何约束被违反，会引发异常（除非被拦截在程序中服务器端的语言，如plpgsql）将不仅会回滚该声明，而且将回滚 整个交易 。
并行写入
即：没有其他交易将尝试同时写入同一张表。
-
使用
WHERE NOT EXISTS ...或任何其他适用的技术排除已在表中的行：
-
不要忘记在插入的集合中删除中的重复项，这将不会被 排除在半反连接之外
WHERE NOT EXIST S ...
同时处理两者的一种技术是 EXCEPT ：
INSERT INTO tbl（col1，col2）
VALUES
（文本'v1'，文本'v2'） - 第一行可能需要显式类型转换
，（'v3'，'v4'）
，（'v3' 'v4'） - 注意源
中的重复EXCEPT SELECT col1，col2 FROM tbl;
EXCEPT 没有关键字 ALL 折叠源中的重复行。如果您知道没有重复， EXCEPT ALL 或其他技术之一将更快。
一般来说，如果目标表是与 DISTINCT 相结合的源代码可能会更快：， WHERE NOT EXISTS >
INSERT INTO tbl（col1，col2）
SELECT *
FROM（
SELECT DISTINCT *
$（
VALUES
（text'v1'，text'v2'）
，（'v3'，'v4'）
，（'v3'，'v4 '） - 源
）t（c1，c2）
）t
WHERE NOT EXISTS（
SELECT 1
FROM tbl
WHERE col1 = t.c1 AND col2 = t.c2
）;
如果可以有很多重复，那么首先需要将它们折叠在源中。
并发写入
您将会喜欢新的Postgres UPSERT 实施 INSERT ... ON CONFLICT {UPDATE | Postgres 9.5
INSERT INTO tbl（col1，col2）
SELECT DISTINCT *
FROM（
VALUES
（text'v1'，text'v2'）
，（' v3'，'v4'）
，（'v3'，'v4'） - 你仍然需要在源代码中折叠副本！
）t（c1，c2）
ON CONFLICT DO没有; - 忽略具有*任何*冲突的行！
更详细的相关答案：
文档：
Craig对 UPSERT的参考答案问题：
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?
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.
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.
Generally, if any constraint is violated, an exception is raised which (unless trapped in a procedural server-side language like plpgsql) will roll back not only the statement, but the whole transaction.
Without concurrent writes
I.e.: No other transactions will try to write to the same table at the same time.
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 ...
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 without the key word ALL folds duplicate rows in the source. If you know there are no dupes, EXCEPT ALL or one of the other techniques will be faster.
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 1
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.
With concurrent writes
You will love the new Postgres UPSERT implementation INSERT ... ON CONFLICT {UPDATE | IGNORE} in Postgres 9.5:
INSERT INTO tbl (col1,col2)
SELECT DISTINCT *
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!
More elaborate related answer:
Documentation:
Craig's reference answer for UPSERT problems:
这篇关于插入多行时会出现重复的情况？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
