Skip to main content
Level 6
Sep 27, 2023 4:15 PM UTC
Solved

PGS-220000 PostgreSQL error: ERROR: function substr(integer, integer, integer) does not exist LINE 1: ...0.ts4, W0.ts9, W0.ts11, W0.ts10, W0.ts12, W0.ts6, substr(W0.... ^

Hi, how to debug this error? 

I was tasked with adding a new column to a table. So I:

1.- Modified the XML of the schema to add this new column (attribute).
2.- Updated DB Structure.
3.- Configure fields on the Load activity.
4.- Add the new field in the Update activity (Destination and Source Expression).

What does it mean? 

Edit: I didn't find the reason. But exporting the pacakge , that was working from Production to Development, solve this.  


 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Manoj_Kumar
Manoj_KumarAccepted solution
Community Advisor
Sep 28, 2023 12:30 AM UTC

Hello @god_prophet 

 

You are trying to ingest an integer value in a String column.

 

To fix the issue, use ToString(@COLUMN_NAME) in the update data activity.

Manoj  | https://themartech.pro

6 replies

ParthaSarathy
Community Advisor
Community Advisor
Sep 27, 2023 4:21 PM UTC

Hi @god_prophet ,

Share the screenshot of Update data activity where you had configured substring function.

Syntax of substring is Substring(<string>,<offset> ,<length> )

Example,

Substring(@StringTypeAttributeName,1 ,1 )

 

Translate
~  ParthaSarathy S
Level 6
Sep 27, 2023 4:40 PM UTC

Please, see edit 1.

There is not SubString function applied, not by me.  Just added the last highlighted field.

Translate
ParthaSarathy
Community Advisor
Community Advisor
Sep 27, 2023 4:59 PM UTC

@god_prophet , So just after adding the 'yellow highlighted' field in update data activity, you're getting error?

Check what is the data type of that field in your schema.

Then select 'display target' in the inbound transition of update data activity, In top left navigate to 'Schema' tab. Click on your field and in bottom you can see the data type of it. This data type should be same as the data type of your schema.

Cross check the same for other fields as well.

Translate
~  ParthaSarathy S
Manoj_Kumar
Community Advisor
Manoj_KumarCommunity AdvisorAccepted solution
Community Advisor
Sep 28, 2023 12:30 AM UTC

Hello @god_prophet 

 

You are trying to ingest an integer value in a String column.

 

To fix the issue, use ToString(@COLUMN_NAME) in the update data activity.

Translate
Manoj  | https://themartech.pro
Sukrity_Wadhwa
Level 4
Oct 5, 2023 9:03 AM UTC

Hi @god_prophet,

Was the given solution by @_manoj_kumar_ helpful in answering your doubt or do you still need more help here? Do let us know.

Translate
Sukrity Wadhwa