Qlik Replicate and Postgres Target Varbinary Datatypes

[Alan_Wang]

Varbinary Datatypes on Postgres Target databases are treated as LOB datatypes. Temporarily CSV Files generated by Replicate currently include LOB datatypes even when LOB support is turned off for the task. This leads to data being inputted into the wrong columns if there are LOB datatypes in your tables and LOB support is turned off.

Sample error of a numeric datatype column flowing into a Datetime column:

[TARGET_LOAD     ]E:  Command failed to load data with exit error code 3, Command output: psql:M:/Replicate/data/tasks/x3_testr4x_rez_ods_ls_pqsl_AgentTest_ac/data_files/1/LOAD00000001.csv.sql:1: ERROR:  date/time field value out of range: "0"

Resolution

• LOB support should be turned on if you would like to keep the LOB column. 
• The LOB column can be transformed to a string datatype if you prefer to keep LOB support off.
• Removing the LOB column from the replication process can also resolve this issue.

Cause 

The temporary CSV files of Replicate do not remove the LOB columns when LOB support is turned off. This leads to incorrect column matches.

Internal Investigation ID(s) 

QB-28455

Environment

• Qlik Replicate 2023 May
• Postgres Target Endpoint