liveBook · Manning

Latin-1

As explained in PostgreSQL Mistakes and How to Avoid Them

latin1 is a character encoding associated with ISO-8859-1 and commonly used as a legacy MySQL default.

Latin-1 is a legacy character encoding associated with ISO-8859-1. A MySQL database can retain latin1 tables even after the server default changes, making migrations to PostgreSQL fail when bytes are interpreted as UTF-8. section 10.3

A migration that fails on é

A dump containing the Latin-1 byte 0xe9 for é can produce PostgreSQL’s invalid UTF-8 byte-sequence error. Converting the dump to UTF-8 exposes the original encoding mismatch instead of silently correcting it. section 10.3

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

Use the PostgreSQL error output to identify bytes that are invalid under UTF-8. section 10.3

Handle a Latin-1 dump as a migration artifact:

  • Convert it explicitly from latin1 to UTF-8 with iconv before retrying the import. section 10.3
  • Check for ASCII NUL characters after conversion; PostgreSQL cannot place code zero in a string constant. section 10.3
iconv -f latin1 -t utf8 prod_dump_latin1.sql -o prod_dump_utf8.sql

Run the supplied conversion command to rewrite a Latin-1 dump as UTF-8. section 10.3

How PostgreSQL Mistakes and How to Avoid Them covers it

How deep it goes
Explains retained table defaults, byte-level failure, conversion, and a subsequent NUL-character pitfall.
Examples
Uses a MySQL dump, the byte 0xe9 for é, PostgreSQL errors, iconv conversion, and NUL-byte cleanup.
What it assumes
Assumes database migration familiarity while explaining encoding defaults, byte values, and PostgreSQL string restrictions.
What you can do after
Diagnose/debug with PostgreSQL errors and convert dumps explicitly before retrying imports.
How it connects
Connects Latin-1 with MySQL, PostgreSQL, UTF-8, utf8mb4, Unicode, and null characters.
Cover of PostgreSQL Mistakes and How to Avoid Them
liveBookmanning.com
Read PostgreSQL Mistakes and How to Avoid Them on liveBook Get the book at manning.com

This explainer was generated by Manning's knowledge graph from the text of PostgreSQL Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.