0

I'm currently running a test migration of data from MySQL to PostgreSQL. I'm using psycopg2 to perform the task.

I was able to migrate the person's information but when I check the data for comparison, some lastnames with Ñ are showing as "?" in psql while some show as Ñ. I checked both server and client encoding in PostgreSQL and they are set to UTF8.

Where should I start troubleshooting this? I'm new to this kind of task.

10
  • have you got the code you're trying to use in your migration? - its most likely being converted out of UTF8 somewhere along the line
    – Ste Bov
    May 16, 2018 at 8:28
  • 1
    Which encoding is used in MySQL? Do you have an intermediate state (a file, I guess) where you can check if the exported data looks correct? May 16, 2018 at 8:31
  • @SteBov, I'm currently looking on the code but one thing I knew is that the DB source that I am playing around in MySQL is a dump file with --compatibility=postgresql of an EXISTING DB in MySQL. This DB was also used to play around with pgloader when testing to move it to postgresql. I compared the data from the original DB and this test DB I am using in MySQL, test DB has "?" marks as well.
    – tango ward
    May 16, 2018 at 8:49
  • @dezso I have a csv file of the original DB, and no issue with Enyes
    – tango ward
    May 16, 2018 at 8:50
  • 1
    Just read such a value into a python variable and compare it like this: 'Ñ' == u'Ñ'. Or just follow the answer at unix.stackexchange.com/a/11607/34908. To solve your issue, I guess you need to do something like 'Ñ'.decode('utf-8'). May 16, 2018 at 9:47

1 Answer 1

1

You can start troubleshooting this by looking at the bytes behind the characters, to check if there are invalid UTF-8 contents independently of the display layer.

An UTF-8 string can be converted into its series of bytes in SQL with

SELECT convert_to(value, 'UTF-8');

You may also split a suspicious string character by character to look at each codepoint individually, with the character, the Unicode codepoint, and the UTF-8 representation

SELECT c, to_hex(ascii(c)), convert_to(c, 'UTF-8') FROM
   regexp_split_to_table('El Niño', '') AS c;

 c | to_hex | convert_to 
---+--------+------------
 E | 45     | \x45
 l | 6c     | \x6c
   | 20     | \x20
 N | 4e     | \x4e
 i | 69     | \x69
 ñ | f1     | \xc3b1
 o | 6f     | \x6f

I would try this on the fields where some characters display as ? and compare with the fields where the characters display correctly. The goal is to identify what's exactly behind the ?.

1
  • Please use simply SELECT HEX(c) ... -- I fear that the other functions called will unexpectedly do some form of charset change, in spite of your SELECT correctly showing that ñ is hex f1 in latin1 and c3b1 in utf8.
    – Rick James
    May 28, 2018 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.