Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.