character not in repertoire
SQLSTATE 22021 is raised when a character in an input string is not valid in the current database encoding or the target character repertoire. This commonly occurs during encoding conversion or when client and server encodings are mismatched.
- 1Sending data encoded in a different character set than the database expects
- 2Inserting bytes that are not valid in the database encoding (e.g., invalid UTF-8 sequences)
- 3Client_encoding mismatch causing conversion failure
Insert containing bytes invalid in the current database encoding.
-- In a UTF-8 database, inserting a raw Latin-1 byte sequence without conversion
expected output
ERROR: invalid byte sequence for encoding "UTF8": 0xe9
Fix 1
Set client_encoding to match the actual encoding of the data
WHEN When the client sends data in a non-UTF-8 encoding.
SET client_encoding = 'LATIN1';
Why this works
Postgres converts data from client_encoding to the database encoding. Setting it correctly allows the server to perform the conversion instead of failing.
Fix 2
Clean invalid bytes before insert
WHEN When importing data from external sources with mixed encodings.
SELECT convert_from(convert_to(input_col, 'UTF8'), 'UTF8') FROM staging;
Why this works
convert_from / convert_to normalise the encoding. Alternatively, use pg_catalog.pg_convert to strip or replace invalid sequences.
✕ Set client_encoding to SQL_ASCII to bypass conversion
SQL_ASCII disables all encoding checks and allows arbitrary bytes, which silently corrupts data.
Class 22 — Data Exception
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev