Welcome Guest! Log in
The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…


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), '')

Knowledge Base

Suggest a new Article!