Qlik Replicate: Support JSONB datatype for PostgreSQL ODBC data source

john_wang — Fri, 23 Dec 2022 03:42:15 GMT

While working with PostgreSQL ODBC DSN as source endpoint, The ODBC Driver is interpreting JSONB datatype as VARCHAR(255) by default, it leads the JSONB column values truncated no matter how the LOB size or data type length in target table were defined.
In general the task report warning as:

2022-12-22T21:28:49:491989 [SOURCE_UNLOAD ]W: Truncation of a column occurred while fetching a value from array (for more details please use verbose logs)

Resolution

There are several options to solve the problem (any single one is good enough😞

I) Change PostgreSQL ODBC source endpoint connection string

Open PostgreSQL ODBC source endpoint
Go to the Advanced tab
Open Internal Parameters
Add a new parameter named additionalConnectionProperties
Press <Enter> and set the parameter's value to:

MaxVarCharSize=0

II) Or on Windows/Linux Replicate Server, add one line to "odbc.ini" in the DSN definition:

MaxVarCharSize=0

III) Or on Windows, set "Max Varchar" to 0 from default value 255 in ODBC Manager GUI (64-bit):

Environment

all versions
PostgreSQL all versions

Internal Investigation ID(s):

Support cases, #00062911

Ideation article, Support JSONB

Qlik Replicate

article Qlik Replicate: Support JSONB datatype for PostgreSQL ODBC data source in Official Support Articles

Qlik Replicate: Support JSONB datatype for PostgreSQL ODBC data source

Resolution

Environment

Internal Investigation ID(s):