Inserting a substring of column in Redshift

Question

Hello I am using Redshift where I have a staging table & a base table. one of the column (city) in my base table has data type varchar & its length is 100.When I am trying to insert the column value from staging table to base table, I want this value to be truncated to 1st 100 characters or leftmost 100 characters. Can this be possible in Redshift?

INSERT into base_table(org_city) select substring(city,0,100) from staging_table; 

I tried using the above query but it failed. Any solutions please ?

Answer 1

Try this!
Your base table column length is Varchar(100), so you need to substring 0-99 chars, which is 100 chars. You are trying to substring 101 chars.

INSERT into base_table(org_city) select substring(city,0,99) from staging_table;