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.

what do you mean by EXCEPTION then? you can insert different values with the same id. If you only need one value, a condition needs to be specified. — Vamsi Prabhala
– Vamsi Prabhala, Commented Jul 10, 2015 at 21:14

Craig Ringer · Accepted Answer · 2015-07-11 07:14:14Z

7

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

answered Jul 11, 2015 at 7:14

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2728024 Over a year ago

But it breaks at the first exception and does not continue the loop.

user330315 · Accepted Answer · 2015-07-10 21:37:16Z

4

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;

answered Jul 10, 2015 at 21:37

user330315

Collectives™ on Stack Overflow

Postgres sql exception handling for batch insert

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related