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

Question

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

User · Answer

You can first insert data into blob field and then copy to text field with the folloing function

CREATE OR REPLACE FUNCTION blob2text() RETURNS void AS $$
Declare
    ref record;
    i integer;
Begin
    FOR ref IN SELECT id, blob_field FROM table LOOP

          --  find 0x00 and replace with space    
      i := position(E'\\000'::bytea in ref.blob_field);
      WHILE i > 0 LOOP
        ref.bob_field := set_byte(ref.blob_field, i-1, 20);
        i := position(E'\\000'::bytea in ref.blobl_field);
      END LOOP

    UPDATE table SET field = encode(ref.blob_field, 'escape') WHERE id = ref.id;
    END LOOP;

End; $$ LANGUAGE plpgsql;

--

SELECT blob2text();

User · Answer

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 · Answer

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 · Answer

If you are using Java  you could just replace the x00 characters before the insert like following   myValue replaceAll   u0000         The solution was provided and explained by Csaba in following post   https   www postgresql org message-id 1171970019 3101 328 camel 40coppola muc ecircle de  Respectively      in Java you can actually have a  0x0  character in your string  and   that s valid unicode  So that s translated to the character 0x0 in   UTF8  which in turn is not accepted because the server uses null   terminated strings    so the only way is to make sure your strings   don t contain the character   u0000

User · Answer

PostgreSQL doesn t support storing NULL   0x00  characters in text fields  this is obviously different from the database NULL value  which is fully supported    Source  http   www postgresql org docs 9 1 static sql-syntax-lexical html SQL-SYNTAX-STRINGS-UESCAPE  If you need to store the NULL character  you must use a bytea field - which should store anything you want  but won t support text operations on it   Given that PostgreSQL doesn t support it in text values  there s no good way to get it to remove it  You could import your data into bytea and later convert it to text using a special function  in perl or something  maybe    but it s likely going to be easier to do that in preprocessing before you load it

User · Answer

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 · Answer

Just regex out null bytes   s  x00  g

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

Examples related to postgresql