🚨 Postgres Error: 'invalid byte sequence for encoding' — Cause, Fix, and Troubleshooting Guide

Overview

PostgreSQL raises this error when bytes arriving from the client cannot be decoded as valid characters in the target encoding. The server is told the data is UTF8, but the actual bytes are not legal UTF-8 — so it rejects the whole statement rather than store garbage.

ERROR:  invalid byte sequence for encoding "UTF8": 0x00

The specific byte in the message is your clue. 0x00 is a NUL byte, which Postgres forbids in text even under UTF-8. Other values like 0xff or 0x92 usually mean the data is really Latin1 or Windows-1252 while the connection claims UTF-8.

Symptoms

• A COPY or bulk INSERT fails partway through an import.
• The error names a concrete byte (0x00, 0x92, 0xe9) that is not valid in the declared encoding.
• The same file loads fine into a Latin1 database but fails against a UTF-8 one.
• Data pasted from a spreadsheet or scraped from a legacy system triggers it.

COPY customers (id, name) FROM '/tmp/customers.csv' WITH (FORMAT csv, HEADER);

ERROR:  invalid byte sequence for encoding "UTF8": 0xe9 0x6c

0xe9 is é in Latin1, but as UTF-8 it is an incomplete multibyte sequence — the file is not actually UTF-8.

Common Root Causes

1. client_encoding mismatch

The connection declares UTF8, but the data is really Latin1 or Windows-1252. Postgres trusts the declared encoding and chokes on the mismatched bytes.

SHOW client_encoding;
SHOW server_encoding;

2. Embedded NUL byte (0x00)

Even valid UTF-8 cannot contain 0x00 in a text/varchar value. Binary data, C-string terminators, or corrupted exports drag NUL bytes into text columns.

ERROR:  invalid byte sequence for encoding "UTF8": 0x00

3. Corrupted or binary data in a text column

A blob, an image, or a gzip stream shoved into a text column contains bytes that are not decodable text in any encoding.

4. COPY importing a file in the wrong encoding

The file on disk is Windows-1252 (typical Excel export), but COPY decodes it as the database’s UTF-8 encoding by default.

5. Mixed-encoding source data

One export concatenates rows from systems with different encodings, so no single client_encoding decodes the whole file.

How to diagnose

Step 1: Check the encodings in play

SHOW client_encoding;
SHOW server_encoding;

 client_encoding
-----------------
 UTF8
(1 row)

If the client says UTF8 but you know the data is Latin1, that is the mismatch.

Step 2: Detect the file’s real encoding

Use the shell before importing — do not trust the file extension.

$ file -i /tmp/customers.csv
/tmp/customers.csv: text/plain; charset=iso-8859-1

charset=iso-8859-1 (Latin1) against a UTF-8 database is the classic cause.

Step 3: Inspect the offending byte

Dump the raw bytes around the failure to see whether it is a NUL or a high-Latin1 character.

$ grep -n $'\000' /tmp/customers.csv | head
$ hexdump -C /tmp/customers.csv | grep '00 '

A visible 00 in the hex dump confirms an embedded NUL byte.

Fixes

Set client_encoding to match the real data

If the data is genuinely Latin1, tell Postgres so it transcodes on the way in:

SET client_encoding TO 'LATIN1';
COPY customers (id, name) FROM '/tmp/customers.csv' WITH (FORMAT csv, HEADER);

Postgres converts Latin1 to the server encoding automatically once the client encoding is honest.

Use the ENCODING option on COPY

Cleaner than a session SET — declare the file encoding inline:

COPY customers (id, name)
FROM '/tmp/customers.csv'
WITH (FORMAT csv, HEADER, ENCODING 'WIN1252');

Transcode the file with iconv before loading

Convert the file to UTF-8 up front and drop bytes that will not map:

$ iconv -f WINDOWS-1252 -t UTF-8//TRANSLIT /tmp/customers.csv > /tmp/customers.utf8.csv

Then COPY the .utf8.csv.

Strip NUL bytes before import

For 0x00, remove the byte at the shell (nothing legally stores it in text):

$ tr -d '\000' < /tmp/customers.csv > /tmp/customers.clean.csv

If the data is already in a staging column, scrub it in SQL:

UPDATE staging_customers
SET name = regexp_replace(name, E'\\u0000', '', 'g')
WHERE name LIKE E'%\\u0000%';

Convert the whole database only as a last resort

If the server encoding itself is wrong, dump, recreate the database with the correct ENCODING, and reload — do not try to change encoding in place.

What to watch out for

• iconv without //TRANSLIT or //IGNORE aborts on the first unmappable byte; choose the flag deliberately so you know whether characters were dropped or transliterated.
• Setting client_encoding to LATIN1 for genuinely UTF-8 data corrupts multibyte characters silently — only set it to the true source encoding.
• NUL bytes can never be stored in text; if you truly need arbitrary bytes, use a bytea column instead.
• file -i guesses the encoding and can be wrong for short files — confirm with a hex dump when in doubt.
• After transcoding, spot-check accented names and currency symbols; a “successful” import can still be mojibake.

Related

• Postgres Error: ‘invalid input syntax for type integer’
• Postgres Error: ‘value too long for type character varying’
• Postgres Error: ‘SSL connection has been closed unexpectedly’