Symptom
When executing a mapping or a query which loads data into a Postgresql database, the following error may occur if source data contains a NUL character (we are talking about the 0x00 value, not the "null" which means no-value)
org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0x00
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2893)
at com.indy.engine.actionCodes.JdbcActionCodeI.a(SourceFile:516)
at com.indy.engine.actionCodes.JdbcActionCodeI.finalizeDirectBindedFetch(SourceFile:541)
at com.indy.engine.action.common.ActionCodeTypeI.a(SourceFile:1111)
at com.indy.engine.action.common.ActionCodeTypeI.executeDirectBindedCode(SourceFile:1322)
at com.indy.engine.action.common.ActionCodeTypeI.executeBindedCode(SourceFile:1448)
at com.indy.engine.action.common.ActionCodeTypeI.executeCode(SourceFile:1505)
at com.indy.engine.action.common.ActionCodeTypeI.run(SourceFile:1666)
at java.lang.Thread.run(Thread.java:748)
Solution
Postgresql cannot load the NUL character. You need to process the source data in order to remove NUL characters.
For example, you can add a Stage between the source and the Postgresql target, and apply a REPLACE() function to the target field.
Example with an H2 stage:
REPLACE(source.column, char(0), '')