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

Five Stars

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

I received the following error when insert string column from mysql to Postgres.
Some of the rows were fixed when I did trim(both CHAR(0x00) from text)  in my sql. 
But I am still getting this error for some other rows. 
Maybe it has invalid characters in the middle. 
How do I get rid of them? 
Moderator

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

Hi,
Postgesql does not supports zero in characters. 
Could you please take a look at this post:http://stackoverflow.com/questions/1347646/postgres-error-on-insert-error-invalid-byte-sequence-for-...?
You can use tMap to replace "\\x00" in expression.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

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

I had also faced the same issue recently. As I'm working with PostgreSQL database while inserting the data from Excel (.xlsx) file.

 

The PostgreSQL does not support zero in characters or 0x00 (unlike Oracle). You have to replace such chars with space or anything else.

 

In order to do so, you have to implement an expression in tMap to handle such situations. 

Use below expression:

row1.your_column_name.replace("\\x00", " ")

For reference, follow below link:

http://stackoverflow.com/questions/1347646/postgres-error-on-insert-error-invalid-byte-sequence-for-...

 

This work for me. Give it a try.

 

Regards

Four Stars

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

How can we handle the same issue in Talend data Mapper.

 

I used below function while loading the data into PostgreSQL database.

 

i've uploaded the regex and replace string.

 

Thanks,

Raghu.

 

 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog