liveBook · Manning

Null character

As explained in PostgreSQL Mistakes and How to Avoid Them

The NUL character is the character with code point zero, commonly represented as hexadecimal 00 or octal 0.

A null character, or ASCII NUL, has code point zero and is commonly written as hexadecimal 00 (\x00) or octal 0 (\0). PostgreSQL rejects it in string constants, even with UTF8 enabled. section 10.3

A migration that fails after encoding conversion

A MySQL dump may contain NUL characters that PostgreSQL refuses during import. After converting a latin1 dump to UTF-8, retrying with psql can still produce `ERROR: invalid byte sequence for encoding "UTF8": 0x00`. The issue is not solved by selecting UTF8: PostgreSQL accepts valid UTF-8 but rejects ASCII zero, while another database may accept it. section 10.3

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

Use the representative PostgreSQL error to recognize a NUL-character failure during import. section 10.3

tr -d '\000' < prod_dump_utf8.sql > prod_dump_clean.sql
                                                
sed 's/\x0//g' prod_dump_utf8.sql > prod_dump_clean.sql

Remove NUL bytes from a converted dump with tr or sed before retrying the import. section 10.3

How PostgreSQL Mistakes and How to Avoid Them covers it

How deep it goes
Defines the character, explains PostgreSQL’s restriction, and demonstrates conversion, diagnosis, and cleanup without detailing storage internals.
Examples
Uses a converted MySQL dump, PostgreSQL’s 0x00 error, and tr or sed cleanup commands.
What it assumes
Assumes familiarity with database imports and encoding conversion while explaining the relevant character representation from zero.
What you can do after
Diagnose NUL-related import errors and remove null bytes before loading converted data into PostgreSQL.
How it connects
Connects NUL characters with character encoding, UTF-8, MySQL, PostgreSQL, and database migration.
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

Related concepts

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.