org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0x00

When you migrate data from a different database to PostgreSQL, sometimes you will have such a problem. This error occurs because PostgreSQL does not allow \0x00,  the NULL value in UTF8 encoding, in its text field.

To fix it you need to remove the "\0x00'  from the sting.

1. Oracle as the source database

      UPDATE <table>
      SET <column>=Replace(<column>, unistr('\\0000'))
      WHERE instr(<column>, unistr('\\0000'))  >=  0;

2. MySQL as the source database

      UPDATE <table>
      SET <column> = replace(comment_text, 0x00, '')
     WHERE <column> like concat('%', 0x00, '%');


                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.