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.

Btw. One solution I came up with is parsing that text file and changing all batch INSERT commands to multiple single-value commands. But before I implement that - maybe you guys came up with better solution. — grzaks
– grzaks, Commented Oct 6, 2010 at 13:17
You'd probably be way better off using the COPY statement instead of a big insert. — Pointy
– Pointy, Commented Oct 6, 2010 at 13:21
Also, how are these files prepared? Why are there illegal byte sequences in there? — Pointy
– Pointy, Commented Oct 6, 2010 at 13:22
@Pointy: I have no idea why. Those are prepared by other company from their database (MySQL I guess). I can't force them to change the dump format. — grzaks
– grzaks, Commented Oct 6, 2010 at 13:32
Well what'd probably be best then would be to write some simple filter to transform the "INSERT" syntax into a CSV file, and also to filter out bogus UTF-8 sequences. Then you can use COPY FROM, which would be a lot faster than a bunch of INSERT statements anyway. — Pointy
– Pointy, Commented Oct 6, 2010 at 14:01

user330315 · Accepted Answer · 2010-10-06 19:34:47Z

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

answered Oct 6, 2010 at 19:34

user330315

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

Collectives™ on Stack Overflow

Ignore Postgresql batch insert errors

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related