PostgreSQL 批量插入的异常处理

Question

我有两个表，需要将一个表中的数据插入到另一个表中。

insert into a (id1, value1)
select id, value from b

id1是唯一的，但当我在表b中有重复的id时，如何在PostgreSQL中捕获每行的异常而不中止执行。

- user2728024

---

在表B中，对于给定的ID，ID的值是否不同？ - Vamsi Prabhala

是的，值不同。 - user2728024

什么是异常？如果您想要使用相同的ID插入不同的值，那么就可以这样做。但是，如果您只需要一个值，则需要指定条件。 - Vamsi Prabhala

Answer 1

如果无法像 @a_horse_with_no_name 建议的那样避免异常，那么循环查询并执行 BEGIN … EXCEPTION … 语句块的 PL/PgSQL 过程是可行的方法。

这种方法比通过 WHERE 子句和（如果需要）连接筛选出问题行要低效许多，所以应尽量避免使用。

主要需要这种方法的情况是，如果有一个验证代码抛出异常而您无法运行该代码生成 WHERE 子句的布尔值，通常就需要使用此方法。不幸的是，PostgreSQL 的大多数数据类型输入函数没有“测试”模式，因此在日期/时间解析等情况下通常会出现此类情况。

您可以这样做：

DO
LANGUAGE plpgsql
$$
DECLARE
  r record;
BEGIN
  FOR r IN SELECT a, b FROM mytable
  LOOP
    BEGIN
      INSERT INTO newtable (x, y)
      VALUES (r.a, r.b);
    EXCEPTION
      WHEN check_violation THEN
        RAISE NOTICE 'Skipped row %', r.a;
    END;
  END LOOP;
END;
$$;

详情请参阅PL/PgSQL手册。

请注意，这将为每个循环迭代执行子事务，并且还需要为每个迭代设置执行器状态，因此比使用INSERT INTO ... SELECT ... WHERE ...慢得多。

- Craig Ringer

---

但它在第一个异常处中断，不会继续循环。 - user2728024

Answer 2

只需避免插入会导致错误的内容：

insert into a (id1, value1)
select id, value 
from b
where not exists (select 1
                  from a
                  where a.id1 = b.id);

如果 a 为空，只需选择唯一的内容：

insert into a (id1, value1)
select distinct on (id) id, value 
from b
order by id;

- user330315