Qlik Replicate: Support JSONB datatype for PostgreSQL ODBC data source

[john_wang]

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

1. Open PostgreSQL ODBC source endpoint
2. Go to the Advanced tab
3. Open Internal Parameters
4.   Add a new parameter named additionalConnectionProperties
   
5. 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

Qlik Replicate  all versions
PostgreSQL         all versions

Internal Investigation ID(s):

Support cases, #00062911

Ideation article, Support JSONB 

Qlik Replicate