a single NUL byte (0x00) in a TEXT column rolled back our entire 500-row batch insert
third-party webhook payloads we ingest occasionally contained a literal NUL byte (U+0000). Postgres text and varchar columns reject it outright, and we were inserting via a single multi-row statement of roughly 500 rows at a time.
one bad row failed the whole statement, so the entire batch rolled back. the symptom was intermittent, cryptic insert errors with no clean pattern, because it only fired on the batches where a payload happened to carry a NUL.
the fix was a strip-and-normalize pass at the data boundary, before the batch insert, not at the database. by the time Postgres rejects the byte, you've already attempted and lost the whole batch.
broader takeaway I took from it: sanitize anything from an external system between "it arrived" and "it enters the pipeline," because the DB is too far downstream to catch this without collateral damage.
curious how others handle invalid bytes in text columns in practice. do you strip upstream like we did, lean on a domain or check constraint, or insert row-by-row so one bad row can't take the batch with it?