Ignore Postgresql batch insert errors

Question

Assume this simple SQL query:

INSERT INTO table (col1,col2) VALUES (val1,val2),(val3,val4),(val5,val6);

Lets say val3 is invalid value for col1. This would cause psql to abort whole INSERT command - it would not insert (val1,val2) nor (val5,val6) either.

Is it possible to make postgresql ignore this error so it does not insert (val3,val4) pair but would still proceed with (val1,val2) and (val5,val6)?

I'm getting daily database dumps in text files from my partner (can't change that), which I use to make my copy of it. Sometimes his huge INSERT queries cause errors like:

ERROR:  invalid byte sequence for encoding "UTF8": 0x00

... which makes whole 30000+ values not inserted to the table, because one of those values is wrong.

Answer 1

The application that processes the input file needs to wrap each INSERT statement with a savepoint. If the insert fails, it can be rolled back to the last savepoint. Something like:

(pseudo code)

foreach line
  set savepoint
  try 
    insert current line
  catch 
    rollback to savepoint
  end
endloop
commit