Postgres sql exception handling for batch insert

Question

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 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.

Answer 1

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.

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.

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.

You want to do something like:

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;
$$;

For details, see the PL/PgSQL manual.

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 ....

Answer 2

Just don't insert those that would cause an error:

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

Or just select the unique ones if a is empty:

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