I read enough to know that this occurs when a string contains some characters that Postgres doesn't like. However, I cannot figure out if there is a way to validate strings before writing them. In particular, I'm doing batch inserts.
insert into foo(col1,col2,col3) values ('a',2,3),('b',4,0),....
My DB is setup like this:
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+--------+----------+---------+-------+-------------------
stats | me | UTF8 | C | C |
Periodically, some bad string will get in and the whole insert will fail(e.g. change���=). I batch up quite a few values in a single insert so I'd like to ideally validate the string rather than bomb the whole insert. Is there a list of which characters are not allowed in a Postgres insert?
Using postgresql-jdbc 9.1-901.jdbc4
This message means that your string data has a null character "\0" in it.
I can't find an authoritative cite for this (let me know if you have one).
It is discussed at https://www.postgresql.org/message-id/alpine.BSO.2.00.0906031639270.2432%40leary.csoft.net
It is mentioned in passing in the official docs at https://www.postgresql.org/docs/9.3/static/functions-string.html
All other characters are allowed.