sluice

Postgres text can't hold a NUL byte

text, varchar, and char reject an embedded 0x00 with SQLSTATE 22021; MySQL char/text store it without complaint. A cross-engine copy hits it, and because it fires inside the COPY protocol the error lands far from the offending row.

Landed in sluice · 2026-06-02 · Postgres

Observed — MySQL → Postgres copy of a text column containing an embedded NUL byte. Internally the Postgres prepareValue NUL guard.

What happened #

A cross-engine copy of a perfectly ordinary VARCHAR column failed on the Postgres side with a cryptic invalid byte sequence for encoding "UTF8": 0x00 — and because it surfaced inside the bulk COPY stream, the error landed nowhere near the row that carried the byte. The source column held a string with an embedded 0x00 (a stray NUL from an upstream C string, a serialized blob mislabeled as text, a bad import), which MySQL had stored without objection.

Why (the mechanism) #

Postgres text types — text, varchar, char — cannot store a 0x00 byte. The NUL is reserved as a string terminator in the server's internal C representation, so an embedded one is rejected as an invalid byte sequence (SQLSTATE 22021, character-not-in-repertoire). MySQL's CHAR/VARCHAR/TEXT have no such rule — they treat the NUL as an ordinary byte and store it. So the value is legal on one engine and illegal on the other, and a migration is exactly where the two meet. The diagnosis is made harder by COPY: the failure fires while streaming the bulk buffer, so the error message is detached from the individual offending row.

The repro #

-- MySQL stores an embedded NUL happily:
CREATE TABLE t (id INT, s VARCHAR(64));
INSERT INTO t VALUES (1, CONCAT('a', CHAR(0), 'b'));   -- OK, 3 bytes

-- Postgres rejects the same bytes in a text type:
SELECT E'a\x00b'::text;
--   ERROR: invalid byte sequence for encoding "UTF8": 0x00  (SQLSTATE 22021)
-- bytea holds arbitrary bytes, NUL included:
SELECT E'\x610062'::bytea;   -- \x610062, no complaint

What sluice does about it #

sluice refuses the value loudly with a coded error (SLUICE-E-VALUE-NUL-BYTE) that names the column and the constraint, rather than letting the opaque COPY-stream error surface far from the row — and rather than the tempting silent "fix" of stripping the NUL, which would quietly alter the data. The data-preserving path, when you genuinely need to carry those bytes, is to target bytea, which stores arbitrary binary including 0x00. Loud refusal with the remedy named beats a cryptic wire error or a silent mutation.

The transferable lesson #

"It's a string column on both sides" is not the same as "the same bytes are legal on both sides." Postgres text is Unicode text with a hard rule — no 0x00 — that MySQL text does not share, so a value that lives happily in MySQL is a hard error in Postgres. When the two disagree about what bytes a type may hold, the honest options are to refuse loudly (naming the column and the fix) or to route the data to a type that can hold it (bytea); silently stripping the offending byte to make the insert succeed is data corruption wearing the disguise of a bug fix.

Primary sources #