首页 文章

插入多行时重复项会发生什么?

提问于
浏览
5

我正在运行一个将大量数据插入Postgres数据库的python脚本,我使用单个查询来执行多行插入:

INSERT INTO table (col1,col2) VALUES ('v1','v2'),('v3','v4') ... etc

我想知道如果它碰到插入的重复键会发生什么 . 它会停止整个查询并抛出异常吗?或者它只是忽略该特定行的插入并继续前进?

2 回答

  • 1

    INSERT 将只插入所有行并且 nothing 将发生特殊情况, unless 您有某种constraint禁止重复/重叠值( PRIMARY KEYUNIQUECHECKEXCLUDE 约束) - 您在问题中未提及 . 但这就是你可能担心的事情 .

    假设 (col1,col2) 上有 UNIQUE 或PK约束,则表示您正在处理教科书 UPSERT 情况 . 许多相关的问题和答案可以在这里找到 .

    通常,如果违反了任何约束,则会引发异常(除非陷入像plpgsql这样的过程服务器端语言中),不仅会回滚语句,还会回滚 whole transaction .

    没有并发写入

    即:没有其他事务会尝试同时写入同一个表 .

    • 使用 WHERE NOT EXISTS ... 或任何其他适用技术排除表中已有的行:

    • Select rows which are not present in other table

    • 并且不要忘记删除插入集合中的重复项,这不会被半反连接 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
       );
    

    如果可以有很多欺骗,那么首先在源中折叠它们是值得的 . 否则使用一个子查询 .

    有关:

    使用并发写入

    Postgres 9.5 或更高版本中使用Postgres UPSERT implementation INSERT ... ON CONFLICT ...

    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!
    

    更详细的相关答案:

    文档:

    Craig对 UPSERT 问题的参考答案:

  • 9

    它会停止整个查询并抛出异常吗?是 .

    为避免这种情况,您可以查看以下SO问题here,该问题描述了当DB上已存在某些插入的键时,如何避免Postgres为多次插入引发错误 .

    你基本上应该这样做:

    INSERT INTO DBtable
            (id, field1)
        SELECT 1, 'value'
        WHERE
            NOT EXISTS (
                SELECT id FROM DBtable WHERE id = 1
    );
    

相关问题