Qlik Replicate Postgres White Space

[zwatkinscovermymeds]

I have noticed in Postgres there will be a column that is varchar with a character limit and Replicate will replicate that data to my sql server environment with the same character limit, but then I will get an error saying that the data has been truncated.  After doing some investigating in Postgres I was able to find that the data was able to exceed the character limit. As an example the character limit on the Postgres source is 200, but I have found columns (that include white space) that have 500 characters. Is there a work around for this so that the column on the target is able to expand dynamically or is this something that can be addressed in feature request? Has anyone else run into this issue?

[john_wang]

Hello @zwatkinscovermymeds ,

Thanks for you opening the article. I noticed you opened a duplicate FR article in April:

https://community.qlik.com/t5/Suggest-an-Idea/Qlik-Replicate-Postgres-White-Space/idi-p/1803181

however it's hard to understand a 200 length column can store 500 characters. At least in PostgreSQL DB itself, the extra chars are truncated by the DB and it stores exact 200 chars only. 

For example if we try to store additional 220 white spaces suffix 'xxx' in the VARCHAR(200) column:

create table kit (id integer, name varchar(20), address varchar(200), primary key(id));
insert into kit values (2,'abc','abc');
insert into kit values (4,'abc   ','xxx                                                                                                                                                                                                                         ');

 Then the query result show the extra white spaces are truncated

select id,name||'#',length(name),length(address) from kit;

As a start I'd like to suggest you open a support ticket with below information:

1. The source DB creation SQL

      We need to check if any special settings in the DB include the encoding and collation settings etc

2. The source table creation SQL, and the sample SQL how to generate such a row (that include white space and have 500 characters in total)

3. Let us know the problem occurs in Full Load stage, or CDC stage, or both

4. The Diagnostics Package with proper logging level

      If the problem occurs in Full Load then enable SOURCE_UNLOAD/TARGET_LOAD    to Verbose

      If the problem occurs in CDC           then enable SOURCE_CAPTURE/TARGET_APPLY to Verbose

      or both of them.

5. The target SQL Server DB collation.

      If the target table is created manually, then please attach the creation SQL too.

Regards,

John.