invalid byte sequence for encoding UTF8 while importing data
ERROR: invalid byte sequence for encoding "UTF8": 0xe9Short answer
PostgreSQL checks incoming text against the encoding the client declares. If the file is really Latin-1 or Windows-1252 but is read as UTF8, the first byte that is not valid UTF8 stops the load. Tell the server the true encoding, with COPY ... ENCODING or client_encoding.
Fix it fast
1Find out which cause you have
Read the client encoding in use, and the encoding of the target database. The error line number from COPY shows where the first bad row is.
psql -d appdb -c "SHOW client_encoding" -c "SHOW server_encoding"2Match what you see, then jump to its fix
What does the error look like?
ERROR: invalid byte sequence for encoding "UTF8": 0xe9CONTEXT: COPY customers, line 12
Cause 1 of 2
The file is in a single-byte encoding but loaded as UTF8
1Check
Input is interpreted using the ENCODING option, or the current client encoding when that option is absent. A Latin-1 or Windows-1252 export fails against UTF8.
psql -d appdb -c "SHOW client_encoding"2Fix
Name the file's real encoding. Server-side COPY and client-side \copy both accept it.
\copy customers FROM 'customers.csv' WITH (FORMAT csv, HEADER true, ENCODING 'WIN1252')3Verify
The load completes and accented values read back correctly.
psql -d appdb -c "SELECT name FROM customers WHERE name ~ '[^ -~]' LIMIT 5"Confirmed by postgresql.org
Cause 2 of 2
client_encoding is set to something other than the data
1Check
PGCLIENTENCODING, the client_encoding setting or a psql \encoding command picks the client encoding at connect time, and the server converts from it.
echo "PGCLIENTENCODING=$PGCLIENTENCODING"
psql -d appdb -c "SHOW client_encoding"2Fix
Unset the stray variable, or set the encoding to match the data for this session.
unset PGCLIENTENCODING
psql -d appdb -c "SET client_encoding TO 'UTF8'"3Verify
SHOW client_encoding reports the value you chose and the import runs.
psql -d appdb -c "SHOW client_encoding"Confirmed by postgresql.org
How do I stop it coming back?
- Export data as UTF8 from the source system and say so in the export tool.
- Pass ENCODING on every COPY from a file whose origin you do not control.
- Avoid SQL_ASCII as a database encoding; it accepts bytes without validating them, so bad data surfaces later.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix