PostgreSQL的批量插入或忽略(Postgresql batch insert or igno

我从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个挑战。

您的查询已经没有JOIN条件的表之间phones和groups ，使这一有效有限的CROSS JOIN -你最有可能不打算。即每一个有资格的手机与每一个有资格组相结合。如果你有100个电话和100组这已经万个组合。
插入的不同组合(group_id, phone_name)
避免将那些已经存在于表中的行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;

做好准备，如果回滚一个序列化错误重复交易。欲了解更多关于该主题的很好的出发点可能是这个由@depesz博客文章或本上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的慢。

Answer 2:

尝试以下方法：

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

随着DISTINCT你可以肯定的是独特的行会被插入，并用NOT IN子句中排除已存在的行。

注：虽然这种解决方案可能更容易理解，在大多数情况下，欧文的效果会更好。

文章来源: Postgresql batch insert or ignore