UTF-8 represents the Unicode character set in bytes, using one or more bytes per character. In PostgreSQL, it is the safe default for databases that must store multilingual text: server and database encodings determine how client text is interpreted and converted. section 5.1
A tempting mistake is leaving a database at SQL_ASCII because it appears to accept input from any client encoding. SQL_ASCII performs no conversion, so English, Greek, and Japanese byte sequences can be stored together without validation. The bytes may remain readable only when a later client already knows each row’s original encoding. section 5.1 section 5.7
Listing 5.1 sql_ascii_in.py: Inserting multi-language text
import psycopg
english_text = ("Good evening, I would like to return my last order "
+ "please.").encode('iso-8859-1')
greek_text = ("Καλησπέρα, θα ήθελα να επιστρέψω την τελευταία μου παραγγελία "
+ "παρακαλώ.").encode('windows-1253')
japanese_text = ("こんばんは、前回の注文を返品したいのですがお願いします。"
).encode('shift_jis')
with psycopg.connect("dbname=frogge user=frogge") as conn:
with conn.cursor() as cur:
cur.execute('''INSERT INTO support.tickets (content, status)
VALUES (%s, 20), (%s, 20), (%s, 20)''',
(english_text, greek_text, japanese_text))
This insert sends ISO-8859-1, Windows-1253, and Shift_JIS byte strings into one SQL_ASCII text column, demonstrating how incompatible encodings can enter without conversion. section 5.1
Diagnosing unreadable stored text
Reading SQL_ASCII content as UTF-8 succeeds for the ASCII-only English row but fails on a Greek byte sequence with an invalid-continuation-byte error. A conversion can recover a row only when its original encoding is known; mixed data makes a general repair uncertain. section 5.1
Use UTF-8 deliberately instead of treating encoding as a permissive storage setting:
- Create the database with UTF8 and matching UTF-8 locale settings, rather than inheriting SQL_ASCII from an improperly configured environment. section 5.1
- Keep text in a consistent encoding so PostgreSQL can perform automatic character-set conversion instead of preserving opaque bytes. section 5.7
- Before importing a legacy dump, identify its actual encoding. A latin1 byte such as 0xe9 is not valid UTF-8 until converted. section 10.3
- Remove ASCII NUL characters after conversion when preparing text for PostgreSQL import; a UTF-8 target does not make those values acceptable. section 10.3
Repairing a legacy import
A dump from a database still using latin1 can fail against a UTF8 target with an invalid byte-sequence error. Convert the dump from latin1 to UTF-8, then clean NUL bytes before retrying the import. Do not assume a newer MySQL default changed the encoding of existing tables. section 10.3
Do not confuse MySQL’s historical utf8 name with full UTF-8 support. That implementation handles only up to three-byte characters; utf8mb4 was added for four-byte characters, including many international characters and emojis. PostgreSQL database configuration uses UTF8. section 10.3 section 5.1
