Mysql to Poatgre bit(64) data issue

by

I have a table in mysql & Postgre:

 CREATE TABLE IF NOT EXISTS ABC_bit64(
    PK bigint primary key,
    x bit(64));

and data for in mysql:

PK                 |X                                                               |
-------------------+----------------------------------------------------------------+
7049900240000000001|-111011001001001000110011011010101110001001110100000000000000000|

when we are trying to migrate the same data to Postgre(same table structure in postgre also), it throws below error:

SQL Error [22P02]: ERROR: “-” is not a valid binary digit
Position: 55

How to proceed on it?

  • Remove the -, that’s not a valid bit. See the error message and the documentation: Bit strings are strings of 1's and 0's. The character - doesn’t look like 1 or a 0. postgresql.org/docs/current/datatype-bit.html

    – 

  • But I dont want to loose data, thats how it is stored in mysql.

    – 




  • Then use varchar.

    – 

  • it is migration from mysql to Postgre, I believe we are not supposed to change data type in postgre.

    – 

  • Your data is corrupt, a - isn’t a valid bit. How you want to deal with data corruption, is up to you.

    – 

Leave a Comment