Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT

Qlik Replicate: Support JSONB datatype for PostgreSQL ODBC data source

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
john_wang
Support
Support

Qlik Replicate: Support JSONB datatype for PostgreSQL ODBC data source

Last Update:

Dec 22, 2022 10:42:15 PM

Updated By:

john_wang

Created date:

Dec 22, 2022 10:42:15 PM

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
    john_wang_0-1671766541290.png

     


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):

john_wang_1-1671766616443.png

 

Environment

Qlik Replicate  all versions
PostgreSQL         all versions

Internal Investigation ID(s):

Support cases, #00062911

Ideation article, Support JSONB 

 

Qlik Replicate  

Contributors
Version history
Last update:
‎2022-12-22 10:42 PM
Updated by: