How to fix Character not in repertoire in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 26, 2025· 138 views

This error occurs when PostgreSQL encounters characters that cannot be represented in the database's character encoding. Common causes include encoding mismatches between client and server, or attempting to insert Unicode characters into a limited encoding like LATIN1.

Quick fix

Fixes Character not in repertoire

SHOW server_encoding;
SHOW client_encoding;
  1. Check current database and client encoding
  2. Set client encoding to UTF-8 for the session
  3. For permanent fixes, recreate the database with UTF-8 encoding
  4. Clean problematic data before insertion
postgresql — error
SHOW server_encoding;
SHOW client_encoding;
Character not in repertoire

What this error means

PostgreSQL enforces strict character encoding rules. When you attempt to insert or process data containing characters that aren't valid in your database's encoding, PostgreSQL raises this error. The error indicates that the character repertoire (the set of characters supported) of the target encoding is too small to represent the character being inserted. For example, if your database uses LATIN1 (which only supports Western European characters) and you try to insert a Chinese character encoded as UTF-8, this error will occur.

Typical symptoms

  • INSERT or UPDATE statements fail with "character not in repertoire" error
  • Data import from external sources fails mid-way
  • Application receives encoding conversion errors when storing non-ASCII text
  • Queries work in psql but fail when running through application drivers

Common causes

  1. Database encoding is too restrictive (e.g., LATIN1 instead of UTF-8) and data contains characters outside that encoding's repertoire
  2. Client encoding mismatch where the application sends data in UTF-8 but the server expects LATIN1
  3. Invalid byte sequences in the source data that don't form valid characters in any encoding
  4. Importing data from a file with a different encoding than what PostgreSQL expects
  5. Control characters or null bytes embedded in the data being inserted

How to fix "Character not in repertoire"

1Check current database and client encoding

Connect to your PostgreSQL database and run these commands:

sql
SHOW server_encoding;
SHOW client_encoding;

Note the values. If server_encoding is LATIN1 or another limited encoding, that's likely the root cause.

2Set client encoding to UTF-8 for the session

Temporarily set the client encoding to match UTF-8:

sql
SET client_encoding = 'UTF8';

Then retry your INSERT or UPDATE statement. If this works, the issue is an encoding mismatch.

3For permanent fixes, recreate the database with UTF-8 encoding

The best long-term solution is to use UTF-8, which supports all Unicode characters:

sql
CREATE DATABASE mydb ENCODING 'UTF8';

If you have an existing database, you'll need to:

1. Dump the database:

bash
pg_dump old_db > backup.sql

2. Create a new UTF-8 database:

sql
CREATE DATABASE new_db ENCODING 'UTF8';

3. Restore the data:

bash
psql new_db < backup.sql
4Clean problematic data before insertion

If you can't change the database encoding, clean the source data to remove unsupported characters:

sql
-- Remove invalid UTF-8 byte sequences
SELECT convert_from(convert_to(your_column, 'UTF8'), 'UTF8');

-- Or filter to ASCII-only (if appropriate for your use case)
SELECT regexp_replace(your_column, '[^\x20-\x7E]', '', 'g');
5Verify source file encoding before import

If importing from a file, check and convert the file's encoding:

bash
# Check current file encoding
file -i yourfile.csv

# Convert to UTF-8 if needed
iconv -f LATIN1 -t UTF-8 input.csv > output.csv

Then import the converted file into PostgreSQL.

Advanced notes

PostgreSQL's strict encoding enforcement is a feature, not a bug. Some databases allow mixed encodings, leading to data corruption and subtle bugs. PostgreSQL enforces consistency.

If you're using Ruby on Rails or similar ORMs, ensure the config/database.yml specifies encoding: utf8mb4 (or UTF8 for PostgreSQL). Some older applications use latin1 by default.

For production databases, UTF-8 is the modern standard. LATIN1 and other single-byte encodings should only be used for legacy systems. If you're storing user-generated content, international text, or emojis, UTF-8 is essential.

The convert_to() and convert_from() functions can be helpful for handling data that's already in the database but in the wrong encoding, but they should be used cautiously as they can lose data if the target encoding can't represent the characters.

Frequently asked questions

What does "Character not in repertoire" mean?
PostgreSQL enforces strict character encoding rules. When you attempt to insert or process data containing characters that aren't valid in your database's encoding, PostgreSQL raises this error. The error indicates that the character repertoire (the set of characters supported) of the target encoding is too small to represent the character being inserted.
How do I fix "Character not in repertoire"?
This error occurs when PostgreSQL encounters characters that cannot be represented in the database's character encoding. Common causes include encoding mismatches between client and server, or attempting to insert Unicode characters into a limited encoding like LATIN1.
What causes "Character not in repertoire"?
Database encoding is too restrictive (e.g., LATIN1 instead of UTF-8) and data contains characters outside that encoding's repertoire Client encoding mismatch where the application sends data in UTF-8 but the server expects LATIN1 Invalid byte sequences in the source data that don't form valid characters in any encoding
Is the "Character not in repertoire" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading