postgresql - Postgres sql exception handling for batch insert -

i have 2 tables , inserting data 1 table other.

insert (id1, value1) select id, value b 

id1 unique , when have repeating id in table b. how can catch exception each row in postgresql without halting execution.

if can't @a_horse_with_no_name suggests , avoid exception pl/pgsql procedure loops on query , begin ... exception ... block way go.

this massively less efficient filtering out problem rows where clause , (if needed) join, should avoided if possible.

the main time it's necessary if, say, exception being thrown validation code can't run produce boolean where clause. unfortunately of postgresql's data-type input functions don't have "test" mode can null result or error flag invalid input, case things date/time parsing.

you want like:

do language plpgsql $$ declare   r record; begin   r in select a, b mytable   loop     begin       insert newtable (x, y)       values (r.a, r.b);     exception       when check_violation         raise notice 'skipped row %', r.a;     end;   end loop; end; $$; 

for details, see pl/pgsql manual.

note subtransaction every loop iteration , requires executor state setup every iteration, it's way slower doing insert ... select ... ....