One Star

value too long for type character varying(40)ERROR

Hi,
whean I run the job Iam getting error
mysql source table columnn(value) length(255), psql destination table column(value)length(40)
ERROR: value too long for type character varying(40)ERROR:current transaction is aborted,commands ignored until end of transaction blocError.

Thanks in advance.
11 REPLIES
One Star

Re: value too long for type character varying(40)ERROR

Hi
The error indicates that the lengh of inserted value is longer than the lenth of this column in DB table.
Please check it.
Regards,
Pedro
One Star

Re: value too long for type character varying(40)ERROR

I don't have permissions to change the length of the fields in both the databases
is there any component to con-just the data
One Star

Re: value too long for type character varying(40)ERROR

Use one of the string functions provided or trim with java.
One Star

Re: value too long for type character varying(40)ERROR

Hi,
I am using out put expression row2.value.StringHandling.TRIM(1,30) i am getting error StringHandling cannot be resolved or is not a field
and i tried to one more row2.value.TalendString.talendTrim(0,30) i am getting erro TalendString cannot be resolved or is not a field
One Star

Re: value too long for type character varying(40)ERROR

Hi
Try this.
row2.value.substring(0,30)
Regards,
Pedro
One Star

Re: value too long for type character varying(40)ERROR

Hi,
Thanks for reply. The out put error resolved
but This is Input error value too long for type character varying(255)ERROR:
java.lang.StringIndexOutOfBoundsException: String index out of range: 39
at java.lang.String.substring(String.java:1935)
One Star

Re: value too long for type character varying(40)ERROR

Hi
What's your expression?
It seems that the expression you typed is row2.value.substring(0,39).
But the input value is shorter than that.
Regards,
Pedro
One Star

Re: value too long for type character varying(40)ERROR

Hi
The input length is 255 using mysql value(column)
The output value(column1) length is 40, name(column2) length is 60
I mapped mysql to psql
row2 out1
mysql(source) psql(dest)
value row2.value(0,30) value(column)
row2.value(0,39) name(column)
One Star

Re: value too long for type character varying(40)ERROR

Hi
The max length of name is 60.
But the length of some actual data is shorter than that, even shorter than 39.
That's why you get the error. String index out of range: 39
Are you familiar with Java?
Regards,
Pedro
One Star

Re: value too long for type character varying(40)ERROR

Hi,
I tried this one
Relational.ISNULL(row2.value)?"":row2.value.trim().length()<40 ?row2.value.trim():row2.value.trim().substring(0,39)
resolved error in output side

but i got ERROR: value too long for type character varying(255)ERROR: current transaction is aborted, commands ignored until end of transaction blockERROR: current transaction is aborted, commands
This is input side
Do you have any idea to Input side solve this issue?
One Star

Re: value too long for type character varying(40)ERROR

Hi
Try this.
row2.value==null?""Smiley Sadrow2.value.trim().length()<40 ?row2.value.trim():row2.value.trim().substring(0,39))
Regards,
Pedro