PostgreSQL 22021: invalid byte sequence for encoding
Text contains bytes that aren't valid in the database or client encoding: a Latin-1 or Windows-1252 file loaded as UTF-8, or a NUL byte in text.
- SQLSTATE
22021- Name
character_not_in_repertoire- Category
- Data Exception
What it means
PostgreSQL checks that every text value is valid in the encoding it arrives in. 22021 (character_not_in_repertoire) means a byte sequence isn't a valid character there. With a UTF-8 database it usually means the data was really in another encoding, such as Latin-1 or Windows-1252, where bytes like 0xe9 (é) are single bytes that aren't valid UTF-8 on their own.
PostgreSQL text types can never contain the NUL byte, so invalid byte sequence for encoding "UTF8": 0x00 means binary or C-string data reached a text column.
Common causes
- Loading a CSV exported by a tool that wrote Windows-1252 or Latin-1, with
COPYor\copyassuming UTF-8. client_encodingset toUTF8while the client actually sends another encoding.- Binary data, or strings with embedded NUL bytes, inserted into a
textorvarcharcolumn. - Data truncated in the middle of a multi-byte character by an application.
How to fix it
-
Tell
COPYthe file's real encoding; PostgreSQL converts it.\copy customers FROM 'customers.csv' WITH (FORMAT csv, HEADER true, ENCODING 'WIN1252')
-
Or set the client encoding for the session to match what the client sends.
SET client_encoding = 'LATIN1';
-
Convert the file before loading, for example
iconv -f WINDOWS-1252 -t UTF-8 in.csv > out.csv. -
Store binary data in
bytea, and strip NUL bytes from text in the application before inserting.
Related errors
22P02invalid input syntax for type22001value too long for type58P01undefined file1366MySQL: Incorrect value for column
More data exception errors
22008date/time field value out of range22012division by zero22007invalid datetime format22023invalid parameter value22003numeric value out of range
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.