Home / Guides
Error fixed

invalid byte sequence for encoding "UTF8": 0x00

By · October 16, 2026 · 8 min read

The short version

ERROR: invalid byte sequence for encoding "UTF8": 0x00 means the data you are loading contains a NUL byte (a byte with value zero), and PostgreSQL cannot store the character with code zero in any text column, whatever the database encoding. Changing the encoding does not help. Remove the NUL before it reaches PostgreSQL (tr -d '\000' on the file, or REPLACE(col, CHAR(0), '') in the source query), or, if the column really holds binary data, load it into a bytea column instead of text.

What the error looks like

I made a three-row CSV with one NUL byte in the middle of a name, the way a C program or an old export tool leaves them behind:

$ printf 'id,name,note\n1,Acme Ltd,ok\n2,Bad\0Row,has a NUL\n3,Zeta,ok\n' > customers.csv
$ od -c customers.csv
0000000    i   d   ,   n   a   m   e   ,   n   o   t   e  \n   1   ,   A
0000020    c   m   e       L   t   d   ,   o   k  \n   2   ,   B   a   d
0000040   \0   R   o   w   ,   h   a   s       a       N   U   L  \n   3
0000060    ,   Z   e   t   a   ,   o   k  \n

Then I loaded it into PostgreSQL 16.14 (a throwaway database, server_encoding UTF8) with COPY ... FROM STDIN, which is the same command psql's \copy runs under the hood. I sent it from Python with psycopg2's copy_expert:

with open("customers.csv") as f:
    cur.copy_expert("COPY customers FROM STDIN WITH (FORMAT csv, HEADER true)", f)
psycopg2.errors.CharacterNotInRepertoire: invalid byte sequence for encoding "UTF8": 0x00
CONTEXT:  COPY customers, line 3

The whole COPY is rolled back: rows 1 and 3 were fine, and none of them loaded. The SQLSTATE is 22021, which the PostgreSQL error code table calls character_not_in_repertoire.

line 3 counts the header, so it is the third physical line of the file. To find the bad lines yourself:

$ LC_ALL=C grep -a -n -P '\x00' customers.csv | cat -v
3:2,Bad^@Row,has a NUL

^@ is how cat -v prints a NUL.

The same error from an INSERT

With a parameterised INSERT the result depends on the driver. asyncpg sends the string to the server, and the server rejects it with the exact same message:

>>> await conn.execute("INSERT INTO customers VALUES ($1, $2, $3)", 2, "Bad\x00Row", "x")
asyncpg.exceptions.CharacterNotInRepertoireError: invalid byte sequence for encoding "UTF8": 0x00

psycopg2 refuses earlier, on the client, so you get a different message for the same problem:

>>> cur.execute("INSERT INTO customers VALUES (%s, %s, %s)", (2, "Bad\x00Row", "x"))
ValueError: A string literal cannot contain NUL (0x00) characters.

Typing it as an escape string in SQL gets the server error again, and chr(0) has its own wording:

SELECT E'Bad\x00Row';   -> invalid byte sequence for encoding "UTF8": 0x00
SELECT chr(0);          -> null character not permitted

The lexical rules in the manual say it plainly: the character with the code zero cannot be in a string constant.

JSON goes the same way. The \u0000 escape inside a JSON document is rejected when it is cast to jsonb:

INSERT INTO j VALUES ($1::jsonb)   -- '{"name": "Bad\u0000Row"}'
UntranslatableCharacterError: unsupported Unicode escape sequence
DETAIL:  \u0000 cannot be converted to text.

The JSON types page says jsonb rejects \u0000 because it cannot be represented in the text type.

Why can't PostgreSQL store 0x00 in a text column?

The character types page has one sentence on it: "Regardless of the specific character set, the character with code zero (sometimes called NUL) cannot be stored" (PostgreSQL: Character Types). The binary types page draws the line from the other side: binary strings allow octets of value zero, and character strings disallow zero octets.

So 0x00 is a different problem from the other "invalid byte sequence" errors. Bytes like 0xa0, 0x92 or 0xff mean the file is in some other encoding (Latin-1, Windows-1252) and the fix is to declare that encoding. A zero byte is valid in every encoding PostgreSQL knows, and still cannot go in a text column. I checked by retrying the same file with an explicit encoding:

COPY customers FROM STDIN WITH (FORMAT csv, HEADER true, ENCODING 'LATIN1')
22021 invalid byte sequence for encoding "LATIN1": 0x00

Same error, with the encoding name swapped. For 0xa0, 0x92, 0xff and friends, see invalid byte sequence for encoding UTF8: 0xa0, 0x92, 0xff.

Before stripping anything, check that the file really is UTF-8. A UTF-16 file read as UTF-8 is full of zero bytes, one after every ASCII letter, and fails with the same message on line 1:

$ printf 'id,name\n1,Acme\n' | iconv -f UTF-8 -t UTF-16LE > u16.csv
$ od -c u16.csv | head -1
0000000    i  \0   d  \0   ,  \0   n  \0   a  \0   m  \0   e  \0  \n  \0
22021 invalid byte sequence for encoding "UTF8": 0x00
CONTEXT:  COPY u16, line 1

If od -c shows \0 after every character, stripping NULs is the wrong fix; convert the file from UTF-16 to UTF-8 first (iconv -f UTF-16LE -t UTF-8). If the zeros are rare and sit inside a few values, as in customers.csv, read on.

Fix 1: strip NUL from the file before loading

tr is the simplest and works on macOS and Linux:

$ tr -d '\000' < customers.csv > customers_clean.csv
$ LC_ALL=C grep -c -a -P '\x00' customers_clean.csv
0

The sed 's/\x00//g' one-liner you will see recommended did not work with the BSD sed that ships with macOS:

$ sed 's/\x00//g' customers.csv
sed: first RE may not be empty

Perl does it everywhere and gave a byte-identical result to tr:

$ perl -pe 's/\x00//g' customers.csv > customers_pl.csv
$ cmp customers_clean.csv customers_pl.csv && echo same
same

In Python, read bytes and replace, so nothing gets decoded on the way:

data = open("customers.csv", "rb").read()
print(data.count(b"\x00"), "NUL bytes")      # 1 NUL bytes
open("customers_clean.csv", "wb").write(data.replace(b"\x00", b""))

The cleaned file loads:

(1, 'Acme Ltd', 'ok')
(2, 'BadRow', 'has a NUL')
(3, 'Zeta', 'ok')

If your pipeline inserts rows from code, do the same per value before the INSERT: value.replace("\x00", "") on every string. I ran that with psycopg2 executemany and row 2 went in as BadRow.

Deleting the byte joins the two halves (BadRow). If the NUL was acting as a separator, replace it with a space or another character instead of an empty string. Look at a few affected rows before choosing.

Fix 2: replace NUL in the source query (MySQL example)

When the data comes out of another database, cleaning it in the SELECT means the bad byte never lands in a file. MySQL stores NUL in a VARCHAR without complaint. On MySQL 8.4.11:

CREATE TABLE customers (id INT, name VARCHAR(50)) CHARSET utf8mb4;
INSERT INTO customers VALUES (1,'Acme Ltd'),(2,CONCAT('Bad',CHAR(0),'Row'));
SELECT id, name, LENGTH(name), HEX(name) FROM customers;
id  name      LENGTH(name)  HEX(name)
1   Acme Ltd  8             41636D65204C7464
2   Bad\0Row  7             42616400526F77

The 00 in the middle of the hex is the culprit. Find the affected rows, then clean them in the export query with REPLACE() and CHAR(0):

SELECT id FROM customers WHERE name LIKE CONCAT('%', CHAR(0), '%');
-- 2

SELECT id, REPLACE(name, CHAR(0), '') AS name, HEX(REPLACE(name, CHAR(0), ''))
FROM customers;
id  name      HEX(REPLACE(name, CHAR(0), ''))
1   Acme Ltd  41636D65204C7464
2   BadRow    426164526F77

Wrap every text column you export this way, or run an UPDATE ... SET name = REPLACE(name, CHAR(0), '') on a copy of the source first. The full move is in MySQL to PostgreSQL, step by step.

Fix 3: use bytea when the column really is binary

If the column holds file contents, hashes, encrypted values or anything else where every byte matters, stripping NUL corrupts it. Load it into bytea, which allows octets of value zero:

cur.execute("CREATE TABLE blobs (id int, payload bytea)")
cur.execute("INSERT INTO blobs VALUES (%s, %s)", (1, psycopg2.Binary(b"Bad\x00Row")))
cur.execute("SELECT id, payload, length(payload), position('\\x00'::bytea in payload) FROM blobs")
1 b'Bad\x00Row' 7 4

All seven bytes are kept, and the zero is at position 4. In text form PostgreSQL shows it as hex, \x42616400526f77. You cannot turn it back into text with the NUL still in it:

SELECT convert_from(payload, 'UTF8') FROM blobs;
CharacterNotInRepertoire: invalid byte sequence for encoding "UTF8": 0x00

That is the right outcome: bytea is for data that is bytes, and a column that is meant to be text should be cleaned with fix 1 or 2.

What our CSV to PostgreSQL converter does with a NUL

I ran the same customers.csv, NUL and all, through the reader and PostgreSQL writer behind the CSV to PostgreSQL converter:

from app.converter.router import READERS, WRITERS
read_csv, write_pg = READERS["csv"], WRITERS["postgresql"]
data = read_csv(Path("customers.csv"))
write_pg(data, Path("out"))

The writer drops the zero byte from text values, so dump.sql has no NUL left in it (grep -c -a -P '\x00' dump.sql prints 0):

INSERT INTO "customers" ("id", "name", "note") VALUES
(1, 'Acme Ltd', 'ok'),
(2, 'BadRow', 'has a NUL'),
(3, 'Zeta', 'ok');

I loaded that dump into a fresh database on the same PostgreSQL 16 server, and all three rows went in:

(1, 'Acme Ltd', 8)
(2, 'BadRow', 6)
(3, 'Zeta', 4)

Note what that means: the byte is removed, which is the same result as fix 1. Bad\0Row (7 bytes) becomes BadRow (6). For a name or a note that is what you want. If the zero carries meaning, the value is binary and belongs in a bytea column (fix 3), so load that column some other way than as text. The tr -d '\000' step is only needed when you load the CSV yourself with COPY or \copy; for those, and for pgAdmin, see CSV to PostgreSQL with COPY, \copy and pgAdmin.

Sources