Postgresql批处理插入或忽略

Question

我有责任将我们的代码从sqlite切换到postgres。我遇到麻烦的查询之一复制到下面。

INSERT INTO group_phones(group_id, phone_name)
SELECT g.id, p.name 
FROM phones AS p, groups as g
WHERE g.id IN ($add_groups) AND p.name IN ($phones);

当有重复的记录时，就会出现此问题。在此表中，两个值的组合必须唯一。我在其他地方使用了一些plpgsql函数来执行更新或插入操作，但是在这种情况下，我可以一次执行多个插入操作。我不确定如何为此编写存储的例程。感谢您提供的所有sql专家的所有帮助！

Answer 1

有 3个 挑战。

1. 您的查询在表和之间没有 JOIN 条件，这实际上使其受到限制-您很可能不希望这样做。即，每个合格的电话都与合格的每个组结合在一起。如果您有100部电话和100个群组，那么已经是10,000个组合。phones``groups``CROSS JOIN

2. 插入以下内容的 不同 组合(group_id, phone_name)

3. 避免插入table中 已经存在的 行group_phones。

所有考虑到的事物可能看起来像这样：

INSERT INTO group_phones(group_id, phone_name)
SELECT i.id, i.name
FROM  (
    SELECT **DISTINCT** g.id, p.name -- get distinct combinations
    FROM   phones p
    JOIN   groups g ON **??how are p & g connected??**
    WHERE  g.id IN ($add_groups)
    AND    p.name IN ($phones)
    ) i
**LEFT   JOIN** group_phones gp ON (gp.group_id, gp.phone_name) = (i.id, i.name)
**WHERE  gp.group_id IS NULL**  -- avoid duping existing rows

并发

这种形式将并发写入操作的竞争条件的可能性降到最低。 如果 您的表具有 较大的并发写入
负载，则可能要排他地锁定表或使用可序列化的事务隔离，这可以防止在约束验证（行）之间的微小时隙中并发事务更改行的可能性极小不存在）和查询中的写操作。

BEGIN ISOLATION LEVEL SERIALIZABLE;
INSERT ...
COMMIT;

如果事务因序列化错误而回滚，请准备好重复该事务。

通常，尽管如此，您甚至都不需要理会这些。

表现

LEFT JOIN tbl ON right_col = left_col WHERE right_col IS NULL

通常是最快的方法，在右表中有不同的列。如果您的栏中有重复项（特别是如果有很多），

WHERE NOT EXISTS (SELECT 1 FROM tbl WHERE right_col = left_col)

可能更快，因为一旦找到第一行它就可以停止扫描。

您也可以使用IN@dezso演示的，但是在PostgreSQL中通常较慢。