sql - 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. 您的查询在表 phones 和 groups 之间没有JOIN 条件，这使得这有效有限的 CROSS JOIN - 您很可能不打算这样做。 IE。每部符合条件的电话都与每个符合条件的组相结合。如果您有 100 部手机和 100 个群组，那就是 10,000 种组合。

2. 插入(group_id, phone_name)的distinct组合

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

所有的事情都考虑过它可能看起来像这样:

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

并发

这种形式最大限度地减少了并发写入操作出现竞争条件的可能性。 如果您的表有重并发写入负载，您可能需要 lock the table exclusively或使用 serializable transaction isolation , 这可以防止在约束验证(行不存在)和查询中的写入操作之间的微小时间段内，行被并发事务更改的极不可能的情况。

BEGIN ISOLATION LEVEL SERIALIZABLE;
INSERT ...
COMMIT;

如果事务因序列化错误而回滚，请准备好重复该事务。有关该主题的更多信息，好的起点可能是这个 blog post by @depesz或者这个 related question on SO .

不过，通常情况下，您甚至不需要为这些烦恼。

性能

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 中通常较慢。