SQL_ASCII is PostgreSQL’s pseudo-encoding: bytes in the ASCII range behave as ASCII, while higher bytes are left uninterpreted. It is effectively the absence of a database text encoding, not a way to store every language safely. PostgreSQL performs no client/server character-set conversion for SQL_ASCII, so a database can accept text-shaped byte sequences without establishing what character encoding they use. section 5.1 section 5.7
The tempting mistake is to retain SQL_ASCII because multilingual input appears to insert successfully. A support-ticket application can send ISO-8859-1 English, Windows-1253 Greek, and Shift_JIS Japanese bytes into the same text column. The rows are accepted, but the stored values are incompatible byte sequences rather than uniformly encoded text. section 5.1
A successful insert that produces unreadable text
After inserting values encoded differently by the application, selecting the ticket content displays hexadecimal byte values. Casting content to bytea and decoding every row as UTF-8 reads the English row but fails on the Greek row with a UnicodeDecodeError. Acceptance at write time therefore does not prove that later clients can read the data correctly. section 5.1
Treat an SQL_ASCII database as a data-quality and migration problem, not as a permissive multilingual configuration:
- Inspect the database encoding with psql’s \l output; an unset locale can cause PostgreSQL to default to SQL_ASCII. section 5.1
- Do not assume one decoding works for all rows. A conversion can recover a value only when its original encoding is known, as illustrated by converting an ISO-8859-1 value to UTF-8. section 5.1
- Create a UTF8 database with an appropriate locale and load text as UTF-8 rather than continuing to mix encodings in the old database. section 5.1
- Plan remediation carefully: mixing encodings can be irreversible, so unknown legacy bytes may require investigation before they can be interpreted safely. section 5.7 section 11.3.1
\l List of databases -[ RECORD 1 ]-----+---------------------- Name | frogge Owner | frogge Encoding | SQL_ASCII Locale Provider | libc Collate | C Ctype | C ICU Locale | ICU Rules | Access privileges | -[ RECORD 2 ]-----+---------------------- Name | postgres Owner | postgres Encoding | SQL_ASCII Locale Provider | libc Collate | C Ctype | C ICU Locale | ICU Rules | Access privileges | ...
Use the database listing to confirm that the server databases are configured with SQL_ASCII and to compare their locale settings before planning a replacement. section 5.1
Prefer UTF-8 when text may cross languages, clients, or system boundaries. Its value here is operational: PostgreSQL can apply automatic character-set conversion instead of preserving unknown bytes and leaving every reader to guess their meaning. section 5.7 section 5.1
