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 is unique and when I have repeating id in table b. H

Solution 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;
BEGINFOR r INSELECT a, b FROM mytable
  LOOP
    BEGININSERTINTO 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 ....

Solution 2:

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

insertinto a (id1, value1)
select id, valuefrom b
wherenotexists (select1from a
                  where a.id1 = b.id);

Or just select the unique ones if a is empty:

insert into a (id1, value1)
selectdistincton (id) id, value 
from b
orderby id;