org.jooq.exception.DataAccessException: unsupported Unicode escape sequence u0000

I’m trying to insert a serialized JSON into a Postgres JSONB column using jOOQ, but it’s failing because the serialized JSON has a \0 inside of it.

Is there a way jOOQ will escape this automatically for us?

This is the exception I’m getting:

org.jooq.exception.DataAccessException: SQL [insert into ...]; 
ERROR: unsupported Unicode escape sequence 
Detail: u0000 cannot be converted to text. 
Where: JSON data, line 1: ...ity":17,"REDACTED":["REDACTED"],"REDACTED":... at 
org.jooq_3.15.8.POSTGRES.debug(Unknown Source) at 
org.jooq.impl.Tools.translate(Tools.java:2997) at 
org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:639) at 
org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:354) at 
org.jooq.impl.DMLQueryAsResultQuery.fetch(DMLQueryAsResultQuery.java:137) at 
org.jooq.impl.ResultQueryTrait.fetchLazy(ResultQueryTrait.java:278) at 
org.jooq.impl.ResultQueryTrait.fetchLazyNonAutoClosing(ResultQueryTrait.java:287) at 
org.jooq.impl.ResultQueryTrait.fetchOne(ResultQueryTrait.java:506) at 
...

Answer

Given the details provided in your bug report #13903, I don’t think there’s much jOOQ can do for you here out of the box. The NUL byte that can be contained in strings seems to have a special meaning in PostgreSQL, and is thus forbidden:

• https://www.commandprompt.com/blog/null-characters-workarounds-arent-good-enough/
• Are null bytes allowed in unicode strings in PostgreSQL via Python?
• How exactly does the one-byte “char” type work in PostgreSQL?

You can try this:

select '{"a":"u0000"}'::jsonb;

You’ll get this error:

SQL Error [22P05]: ERROR: unsupported Unicode escape sequence Detail: u0000 cannot be converted to text. Position: 8 Where: JSON data, line 1: {“a”:…

Showing this has nothing to do with jOOQ itself. jOOQ can’t encode this reasonably on your behalf, because any chosen encoding would inevitably conflict with some other data of some users. For example, if "u0000" were to be encoded as "#u0000", then that might break the assumptions some users have already made about the difference between the and # characters. Hence, jOOQ can’t offer this out of the box.

However, you could encode the NUL byte in any way you want on your end using a custom jOOQ data type binding.