topic Re: Loading jsonb datatype from postgres source to postgres target in Qlik Replicate

Loading jsonb datatype from postgres source to postgres target

vabclb — Wed, 26 Mar 2025 19:30:02 GMT

We have Amazon Aurora postgres db with a column datatype jsonb, when we are trying to run a full load only task and let create Qlik create target table with datatype jsonb, the task is failing with the following error:

[TARGET_LOAD ]E: Command failed to load data with exit error code 1, Command output: ERROR: invalid input syntax for type json.

Is there anykind of manipulation that Qlik is doing while trying to read and load data ? Because source and target datatype are same, so if data is present in any format on source, it should be loaded on target?

Re: Loading jsonb datatype from postgres source to postgres target

john_wang — Thu, 27 Mar 2025 03:47:46 GMT

Hello @vabclb ,

I'd reproduced the behavior in my labs. please allow me sometime, I will get back to you with my findings shortly.

Regards,

John.

Re: Loading jsonb datatype from postgres source to postgres target

john_wang — Thu, 27 Mar 2025 07:39:21 GMT

Hello @vabclb ,

The root cause of the error message "invalid input syntax for type json" is that the JSONB column was truncated, resulting in incomplete and invalid JSON documents. This led to the error.

To resolve this issue, follow these steps:

Adjust the task settings
- Set "Limit LOB size to" to an appropriate value, e.g., 1024.
- Ensure this value accommodates the maximum expected size of your JSONB column.
Verify the task logs
- Check the task log file to confirm that the JSONB column is not being truncated.
- If truncation occurs, you may see a warning message similar to the following:
2025-03-27T11:43:48:185756 [SOURCE_UNLOAD ]W: Truncation of a column occurred while fetching a value from array (for more details please use verbose logs) (ar_odbc_stmt.c:3485)

Hope this helps.

John.

Re: Loading jsonb datatype from postgres source to postgres target

DesmondWOO — Thu, 27 Mar 2025 07:46:37 GMT

Hi @vabclb ,

For additional information, JSONB data is treated as a wide string, which is 2 bytes long. Therefore, you need to double the limited LOB size.

Regards,
Desmond

Re: Loading jsonb datatype from postgres source to postgres target

vabclb — Thu, 27 Mar 2025 13:16:13 GMT

Thanks a lot for inputs, we'll try to add verbose logging and see if its truncation and will adjust the LOB size that qlik task need to handle. Will respond when we have found a working solution.

Re: Loading jsonb datatype from postgres source to postgres target

john_wang — Thu, 27 Mar 2025 13:17:23 GMT

Thank you so much for your support! @vabclb

Re: Loading jsonb datatype from postgres source to postgres target

vabclb — Thu, 27 Mar 2025 16:48:46 GMT

Update, it was truncation issue and once we updated the LOB size to 100kb, it worked fine, thanks for your prompt support.

Re: Loading jsonb datatype from postgres source to postgres target

john_wang — Fri, 28 Mar 2025 00:39:11 GMT

Thanks for the update. We really appreciate your support! @vabclb

Re: Loading jsonb datatype from postgres source to postgres target

vabclb — Fri, 28 Mar 2025 15:32:02 GMT

Follow up question, in the task settings > table transformations, the datatype for the source jsonb field is coming as NCLOB, I updated the subtype to JSON, is that required or Regular will work too? Thanks

Re: Loading jsonb datatype from postgres source to postgres target

john_wang — Mon, 31 Mar 2025 04:08:28 GMT

Hello @vabclb ,

The subtype is not applicable to PostgreSQL endpoints. You may ignore it here. The Regular works always.

thanks,

John.