PostgreSQL 22021 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22021: character not in repertoire

PostgreSQL error 22021 character not in repertoire occurs when a string contains characters that fall outside the accepted character repertoire of the current database encoding. This typically happens during encoding conversion, string function processing, or INSERT/UPDATE operations when the client and server encodings are mismatched or when multibyte characters are used in an SQL_ASCII database.

---

Top 3 Causes

1. Client-Server Encoding Mismatch

The most common cause. Your application sends UTF-8 encoded strings, but the database was created with SQL_ASCII or LATIN1 encoding, causing PostgreSQL to reject characters it cannot map.

-- Check current database and session encoding
SELECT pg_encoding_to_char(encoding), datname
FROM pg_database
WHERE datname = current_database();

SHOW client_encoding;

-- Quick fix: align client encoding to server encoding
SET client_encoding = 'UTF8';

2. Using String Functions on Multibyte Characters in SQL_ASCII Databases

Even though SQL_ASCII bypasses most encoding checks, functions like upper(), lower(), to_tsvector(), or regexp_replace() can still trigger a 22021 error when they encounter multibyte characters they cannot process.

-- This may fail in SQL_ASCII databases with multibyte data
-- SELECT upper(name_column) FROM users;

-- Workaround: convert explicitly before processing
SELECT upper(convert_from(name_column::bytea, 'UTF8'))
FROM users;

-- Use 'simple' dictionary to avoid encoding issues in text search
SELECT to_tsvector('simple', description)
FROM articles;

3. Incorrect client_encoding at the Session Level

When a driver (JDBC, psycopg2, etc.) connects without specifying an encoding, or specifies the wrong one, PostgreSQL encounters unconvertible characters during the internal translation step.

-- Set encoding immediately after connecting
SET client_encoding TO 'UTF8';

-- Persist the setting at the role level
ALTER ROLE app_user SET client_encoding = 'UTF8';

-- Or at the database level
ALTER DATABASE myapp SET client_encoding = 'UTF8';

-- Verify current settings
SELECT name, setting
FROM pg_settings
WHERE name IN ('client_encoding', 'server_encoding');

---

Quick Fix Solutions

If you need to scrub problematic characters from existing data:

-- Find rows with byte length greater than character length
-- (indicates multibyte or problematic characters)
SELECT id, octet_length(col) AS bytes, length(col) AS chars
FROM my_table
WHERE octet_length(col) > length(col);

-- Remove non-printable or out-of-repertoire characters safely
UPDATE my_table
SET col = regexp_replace(col, '[^\x20-\x7E]', '', 'g')
WHERE col ~ '[^\x20-\x7E]';

-- Create a new UTF-8 database correctly from scratch
CREATE DATABASE clean_db
  ENCODING = 'UTF8'
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE = 'en_US.UTF-8'
  TEMPLATE = template0;

---

Prevention Tips

1. Always create databases with explicit UTF-8 encoding. Never rely on defaults or use SQL_ASCII in production. Add encoding verification to your migration scripts and CI/CD pipelines.

-- Standard UTF-8 database creation template
CREATE DATABASE myapp_prod
  WITH ENCODING = 'UTF8'
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       TEMPLATE = template0;

1. Explicitly set client_encoding in every application connection. Use ALTER ROLE to set a persistent default for each application user, so no individual connection can accidentally use a mismatched encoding.

---

Related Errors

• 22P05 untranslatable_character — Closely related; fires when a character cannot be translated to the target encoding.
• 22000 data_exception — The parent error class for 22021.
• 08P01 protocol_violation — Can occur at the protocol level when encoding negotiation between client and server completely fails.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.