Postgres sql异常处理批量插入 [英] Postgres sql exception handling for batch insert

问题描述

我有两个表，我将数据从一个表插入到另一个表。

I have two tables and i am inserting the data from one table to other.

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

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

id1 is unique and when I have repeating id in table b. How can I catch the exception for each row in PostgreSQL without halting the execution.

推荐答案

如果您不能以@a_horse_with_no_name建议并避免异​​常，那么循环查询并执行 BEGIN ... EXCEPTION ... 块的PL / PgSQL过程就是要走的路。

If you can't do as @a_horse_with_no_name suggests and avoid the exception then a PL/PgSQL procedure that loops over a query and does a BEGIN ... EXCEPTION ... block is the way to go.

这是比使用 WHERE 子句过滤出问题行的效率更低，（if需要）加入，所以应该尽可能避免。

This is massively less efficient than filtering out the problem rows with a WHERE clause and (if needed) join, so it should be avoided if possible.

主要的时间是必要的，如果说，验证码抛出异常，你不能运行为其中子句生成一个布尔值。不幸的是PostgreSQL的大部分数据类型的输入函数都没有测试模式，在这里你可以得到一个 NULL 结果或无效输入的错误标志，所以这通常是例如日期/时间解析等。

The main time it's necessary is if, say, an exception is being thrown by validation code you can't run to produce a boolean for a where clause. Unfortunately most of PostgreSQL's data-type input functions don't have a "test" mode where you can get a NULL result or error flag for invalid input, so this is usually the case for things like date/time parsing.

您想要执行以下操作：

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手册。

For details, see the PL/PgSQL manual.

请注意，这对每个循环迭代都进行子事务处理，并且还需要每次迭代执行器状态设置，所以与 INSERT INTO ..相比，它的方式更慢。 。SELECT ... WHERE ... 。

Note that this does a subtransaction for every loop iteration and also requires executor state setup for every iteration, so it's way slower than doing it with an INSERT INTO ... SELECT ... WHERE ....

这篇关于Postgres sql异常处理批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！