Postgres: org.postgresql.util.PSQLException: ERROR: insufficient data left in message

Question

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

Answer 1

date type is not match target type.for example int4->int8

Answer 2

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.