并且不要忘记删除插入集合中的重复项,这不会被半反连接 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 或其他技术之一会更快 . 有关:
通常,如果目标表很大, 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 1
FROM tbl
WHERE col1 = t.c1 AND col2 = t.c2
);
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!
2 回答
INSERT
将只插入所有行并且 nothing 将发生特殊情况, unless 您有某种constraint禁止重复/重叠值(PRIMARY KEY
,UNIQUE
,CHECK
或EXCLUDE
约束) - 您在问题中未提及 . 但这就是你可能担心的事情 .假设
(col1,col2)
上有UNIQUE
或PK约束,则表示您正在处理教科书UPSERT
情况 . 许多相关的问题和答案可以在这里找到 .通常,如果违反了任何约束,则会引发异常(除非陷入像plpgsql这样的过程服务器端语言中),不仅会回滚语句,还会回滚 whole transaction .
没有并发写入
即:没有其他事务会尝试同时写入同一个表 .
使用
WHERE NOT EXISTS ...
或任何其他适用技术排除表中已有的行:Select rows which are not present in other table
并且不要忘记删除插入集合中的重复项,这不会被半反连接
WHERE NOT EXISTS ...
排除一次处理这两种技术的一种方法是
EXCEPT
:EXCEPT没有关键字
ALL
折叠源中的重复行 . 如果你知道没有欺骗,EXCEPT ALL
或其他技术之一会更快 . 有关:通常,如果目标表很大,
WHERE NOT EXISTS
与源上的DISTINCT
组合可能会更快:如果可以有很多欺骗,那么首先在源中折叠它们是值得的 . 否则使用一个子查询 .
有关:
使用并发写入
在 Postgres 9.5 或更高版本中使用Postgres
UPSERT
implementation INSERT ... ON 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
问题的参考答案:它会停止整个查询并抛出异常吗?是 .
为避免这种情况,您可以查看以下SO问题here,该问题描述了当DB上已存在某些插入的键时,如何避免Postgres为多次插入引发错误 .
你基本上应该这样做: