invalid byte sequence for encoding "UTF8": measured fixes

ERROR: invalid byte sequence for encoding "UTF8": 0x00 (SQLSTATE 22021, character_not_in_repertoire) means PostgreSQL received bytes that are not valid text in the database encoding, and refused them. The hex value names the first byte that broke the rule. The two causes we reproduced were a NUL byte (0x00), which PostgreSQL text cannot hold in any encoding, and a file written in Windows-1252 or Latin-1 being read as UTF-8 (0x92, 0xa0, 0xe9).

We measured on PostgreSQL 17.11 and 12.22 in containers, with psycopg 3.3.6 and psycopg2 2.9.13 as clients; the SQL_ASCII COPY and restore case on every major version from 12 to 18 (12.22, 13.23, 14.24, 15.19, 16.15, 17.11, 18.6).

What does the byte in the message tell you?

A Windows-1252 CSV with a curly apostrophe in O’Brien, loaded with COPY:

copy t from '/data/cp1252.csv' with (format csv, header);
-- ERROR:  22021: invalid byte sequence for encoding "UTF8": 0x92
-- CONTEXT:  COPY t, line 3

CONTEXT gives the line in the file, counting the header: line 3 was the O’Brien row. The whole COPY failed: the valid row before it was not loaded either. Inside a transaction the next statement got current transaction is aborted (25P02) and COMMIT answered ROLLBACK, taking an earlier insert with it — the pattern described in current transaction is aborted.

Sometimes the message lists more than one byte. For café followed by a newline, 17 reported 0xe9 0x0a 0x33 — the é, the newline and the 3 of the next line — and 12 just 0xe9. Only the first byte is the problem. A lone 0xa0 (a Latin-1 non-breaking space) gave 0xa0, and a 0xc0 followed by a space was reported as 0xc0 0x20.

How do you load a Windows-1252 or Latin-1 file?

Tell COPY what the file is, and PostgreSQL converts it:

copy t from '/data/cp1252.csv' with (format csv, header, encoding 'WIN1252');
-- COPY 4
select id, name, convert_to(name, 'UTF8') from t order by id;
--  2 | O’Brien | \x4fe28099427269656e
--  3 | café    | \x636166c3a9
--  4 | a b     | \x61c2a062

The bytes are now proper UTF-8: e2 80 99 is the right single quotation mark, c3 a9 is é, c2 a0 the non-breaking space. set client_encoding = 'WIN1252' before a plain COPY gave the identical result, and so did iconv -f WINDOWS-1252 -t UTF-8 on the file before loading it.

Do not pick LATIN1 for a file from Windows. It accepted the same file without any error, but stored 0x92 as the invisible control character U+0092 (\xc292) instead of ’. That is a silent corruption; WIN1252 is the safer guess when you do not know, and it loaded our pure Latin-1 file correctly too. Windows-1252 leaves five bytes undefined (0x81, 0x8D, 0x8F, 0x90, 0x9D). A file containing one of them failed loudly under WIN1252 with 22P05 … has no equivalent in encoding "UTF8", where LATIN1 quietly stored a control character.

Why can a NUL byte (0x00) not be stored?

PostgreSQL’s documentation states that “the character with code zero (sometimes called NUL) cannot be stored” in a character type, regardless of the encoding. Even with ENCODING 'WIN1252' the NUL file failed, now naming the other encoding: invalid byte sequence for encoding "WIN1252": 0x00. What you see depends on how the NUL arrives:

The jsonb row matters when you store request bodies: Python’s json.dumps turned a NUL into \u0000, and the insert failed with 22P05, not 22021. A json column only moves the error to the first query that extracts the field.

The clean fix is in the application, before the value is sent: s.replace("\x00", "") went through as ab. For a file, tr -d '\000' removed it. iconv -c did not, because NUL is valid UTF-8. In SQL, replace(col, chr(0), '') cannot work, because chr(0) itself raises 54000, and there is no replace() for bytea on 12, 17 or 18 (42883: function replace(bytea, bytea, bytea) does not exist). If the data is already in a bytea column, this removed only whole 00 bytes:

select convert_from(
  decode(replace(replace(regexp_replace(encode(raw, 'hex'), '(..)', '\1 ', 'g'),
                         '00 ', ''), ' ', ''), 'hex'),
  'UTF8')
from staging where id = 3;          -- raw = \x610062
-- ab

The shorter replace(encode(raw, 'hex'), '00', '') is wrong: on \xa00b it removed the 0 of a0 and the 0 of 0b and returned \xab.

How do you find the rows with invalid bytes?

Get the raw bytes into a bytea column without passing them through text: send each value from the application as bytes (psycopg 3 stored b'a\x00b' unchanged). A plain COPY into a bytea column does not help, because COPY rejects the file before the column type matters: 22021 … 0x92 on both 17 and 12. Then test each value. convert_from(bytea, 'UTF8') raises the same 22021 on bad input — on \x78ff79 it reported 0xff — so wrap it:

create function utf8_ok(b bytea) returns boolean language plpgsql immutable as $$
begin
  perform convert_from(b, 'UTF8');
  return true;
exception when character_not_in_repertoire then
  return false;
end $$;

select id, raw from staging where not utf8_ok(raw);
--  2 | \x4f92427269656e      (O’Brien in Windows-1252)
--  3 | \x610062              (NUL)
--  4 | \x78ff79
--  6 | \x78c02079
select id from staging where position('\x00'::bytea in raw) > 0;   -- 3

For the rows that fail, convert_from(raw, 'WIN1252') turned \x4f92427269656e into O’Brien. It also accepted 0xff and 0xc0, as ÿ and À: Windows-1252 has a character for almost every byte, so a successful conversion does not prove the guess was right.

Outside the database, iconv -f UTF-8 -t UTF-8 file stopped with illegal input sequence at position 11 on our 0xff file, and iconv -c dropped the byte (xy instead of x\xffy) with exit status 0 (glibc 2.39 iconv). On a Windows-1252 file, iconv -c silently turned O’Brien into OBrien and café into caf — so use -c only on data you have already decided to lose.

Does a dump from a SQL_ASCII database restore cleanly?

A SQL_ASCII database stores bytes without checking them. We loaded the Windows-1252 file into one and restored a plain pg_dump into a UTF8 database. The dump starts with SET client_encoding = 'SQL_ASCII'.

On 12.22 and 13.23 the restore failed: psql:/tmp/legacy.sql:44: ERROR: 22021: invalid byte sequence for encoding "UTF8": 0x92 with CONTEXT: COPY people, line 2, and pg_restore of the custom-format dump reported COPY failed for table "people" and exited with 1. The table was created, but empty.

On 14.24, 15.19, 16.15, 17.11 and 18.6 the same restore succeeded, and the invalid bytes were in the UTF8 database: \x4f92427269656e in a text column. The same happened with every COPY FROM whose input encoding was SQL_ASCII, whether set through client_encoding, PGCLIENTENCODING or COPY … (ENCODING 'SQL_ASCII'), and including \copy. An INSERT of E'x\xffy' under that setting was still rejected, and adding ENCODING 'UTF8' to the COPY brought the check back: 22021 … 0xff. The documentation says the server “will still check that incoming data is valid for that encoding” when the client uses SQL_ASCII. For COPY that has not held since version 14: commit f82de5c46b (‘Do COPY FROM encoding conversion/verification in larger chunks’) makes COPY validate its input against the file encoding only, and SQL_ASCII accepts every byte except 0x00. The damage surfaces later: a plain select and length() worked, upper(name) failed with 22021 … 0x92, and utf8_ok(name::bytea) found the rows.

The fix that worked on 17: dump with the encoding the data really has, pg_dump --encoding=WIN1252. That dump says SET client_encoding = 'WIN1252', and restoring it stored O’Brien as \x4fe28099427269656e. This only works if every row really is Windows-1252. In a SQL_ASCII database that also holds rows already written as UTF-8, the same dump turned café into café without an error, so check with utf8_ok(name::bytea) first which rows are already valid UTF-8. For copying a single tenant out of a shared database, see pg_dump for one tenant; the other codes in this family are in our SQLSTATE overview.

What we did not measure

Other client encodings than Windows-1252, Latin-1 and SQL_ASCII, such as UTF-16 files with a byte-order mark. Drivers other than psycopg 3 and psycopg2, and ORMs. Performance of utf8_ok on large tables.

Sources: PostgreSQL 17 documentation on character types (NUL), JSON types (\u0000 in jsonb) and character set support (SQL_ASCII as client encoding), read on 22 October 2026, and PostgreSQL commit f82de5c46b (‘Do COPY FROM encoding conversion/verification in larger chunks’, first in 14.0). All measurements are our own, on PostgreSQL 12.22, 13.23, 14.24, 15.19, 16.15, 17.11 and 18.6 in containers, on 22 October 2026.