trouble handling strings with three or more '0' using pgloader

Asked Mar 27 '19 at 15:44

Active Mar 27 '19 at 15:44

Viewed 110 times

I am loading large .csv files into a database and want to use pgloader because it is able to skip and log erroneous records. However I frequently get

ERROR PostgreSQL Database error 22021: invalid byte sequence for encoding "UTF8": 0x00

Which I first thought was due to 0x00 characters in my data. I tried different ways to remove these to no avail, until I noticed that some variables e.g. timestamps with three or more 0characters seem to be causing the problem. e.g. 17:46:30 +0000. Does anyone know how to handle this problem without changing my data? About 2/3 of my records have this problem. Ideally I am looking for a fix that allows me to import the data using the same procedure.

asked Mar 27 '19 at 15:44

Mangobae

It's not a "0" character. Your first idea was correct - there are zero-valued bytes in the data stream – Richard Huxton Mar 27 '19 at 15:50
What would a good way to go about removing these @RichardHuxton? Trying sed and tr did not work for me then – Mangobae Mar 28 '19 at 09:38
Are you sure the file is actually UTF-8 and not some other encoding? That is the first thing to check. – Richard Huxton Mar 28 '19 at 20:31

trouble handling strings with three or more '0' using pgloader

0 Answers0