topic Data is populated with Whitespace through Talend in Talend Studio

Data is populated with Whitespace through Talend

Anonymous — Sat, 16 Nov 2024 03:51:28 GMT

Hi Team,

We are seeing issue in table value which are populated from DB2 (source) to Postgres (Target).

I have including here all the job details for each component.

Job SesignSource DB2 SchemaTarget Postgres schematmap

Based on the above approach and once the data has been populated, when we run the below query in the Postgres DB.

SELECT * FROM VMRCTTA1.VMRRCUST_SUMM where cust_gssn_cd='XY03666699' ;

SELECT * FROM VMRCTTA1.VMRRCUST_SUMM where cust_cntry_cd='847' ;

There will be no records were returned however, when we run the same query with Trim as below it works.

SELECT * FROM VMRCTTA1.VMRRCUST_SUMM where trim(cust_gssn_cd)='XY03666699' ;

SELECT * FROM VMRCTTA1.VMRRCUST_SUMM where trim(cust_cntry_cd)='847' ;

Below are the ways we have tried to overcome this but no luck.

1. Used tmap between source and target component.

2. Used trim in source component under Advanced setting.

3. Change the datatype in Postgres DB of cust_cntry_cd from char(5) to Character varying, this will allow value without any length restriction.

Please suggest what is missing as we have this issue in almost all the table where we have character/varchar columns.

We are using TOS.

Regards,

Rohit.

Re: Data is populated with Whitespace through Talend

manodwhb — Thu, 12 Dec 2019 05:21:15 GMT

@rohitatcs , As per my understanding you need to change the data type in psotgrssql side. check the below link to do that .

https://stackoverflow.com/questions/13160388/my-postgresql-auto-inserts-many-space-characters-after-the-value-of-a-field

http://www.postgresqltutorial.com/postgresql-char-varchar-text/

Re: Data is populated with Whitespace through Talend

Anonymous — Fri, 13 Dec 2019 03:02:31 GMT

@manodwhbThanks for your response.

I have found that I can able to resolve my issue for column where source have Char(5) and target I setup Text with trim selected in Source side but I see still we have issue with Varchar(30) column in source where we have tried both with setting target column as Varchar(30) or Text but no luck.

Please suggest. I am attaching here my schema configuration in Talend.

Highlighted column one have text another with varchar(30) , both have whitespace issueJob-Design

Re: Data is populated with Whitespace through Talend

Anonymous — Mon, 16 Dec 2019 05:39:02 GMT

Can someone please update on this or it's bug on TOS.

Please community member help us to resolve this issue.