Postgres error on insert - ERROR: invalid byte sequence for encoding "UTF8": 0x00

ScArcher2_1310 0
Viewed 157152

I get the following error when inserting data from mysql into postgres.

Do I have to manually remove all null characters from my input data? Is there a way to get postgres to do this for me?

ERROR: invalid byte sequence for encoding "UTF8": 0x00
postgresql
7 Answers

Only this regex worked for me:

sed 's/\\0//g'

So as you get your data do this: $ get_data | sed 's/\\0//g' which will output your data without 0x00

user_71278240

If you need to store null characters in text fields and don't want to change your data type other than text then you can follow my solution too:

Before insert:

myValue = myValue.replaceAll("\u0000", "SomeVerySpecialText")

After select:

myValue = myValue.replaceAll("SomeVerySpecialText","\u0000")

I've used "null" as my SomeVerySpecialText which I am sure that there will be no any "null" string in my values at all.

user_22903690

This kind of error can also happen when using COPY and having an escaped string containing NULL values(00) such as:

"H\x00\x00\x00tj\xA8\x9E#D\x98+\xCA\xF0\xA7\xBBl\xC5\x19\xD7\x8D\xB6\x18\xEDJ\x1En"

If you use COPY without specifying the format 'CSV' postgres by default will assume format 'text'. This has a different interaction with backlashes, see text format.

If you're using COPY or a file_fdw make sure to specify format 'CSV' to avoid this kind of errors.

user_46926620
Related