Redshift - truncating string when inserting to target

0
votes

I have two tables. Table A is an operational store and table B is the destination table.

Table A DDL: Column A Varchar(1000)

Table B DDL: Column B Varchar(250)

So I'm trying to do an insert of truncated column A as so: Insert into table B (select left(table a.column a, 249)) , but it gives the error

"error: Value too long for character type"

I have also tried substring to try and truncate the text but to no avail. Please note, that there is also Arabic text in Column A - but it hasn't been an issue in Table A.

Any help / suggestions would be much appreciated!

2
amazon-redshift
I doubt that's the full error. It probably says Value too long for character varying (250). Though Redshift isn't really PostgreSQL, so who knows. Show the full, exact error message and the exact SQL you are running. - Craig Ringer
ERROR: Value too long for character type Detail: ----------------------------------------------- error: Value too long for character type code: 8001 context: Value too long for type character varying(250) query: 769317 location: funcs_string.hpp:390 process: query0_44 [pid=12023] ----------------------------------------------- - MrZ

2 Answers

5
votes

To get around the issue of multi-byte characters, you can cast your field to the desired VarChar size using ::VarChar([some length]). For your example, you would do:

Insert into table B (select table a.column a::VarChar(250))

2
votes

The problem is that each Arabic symbol taking more than 1 byte because RedShift is Unicode DB. The varchar definition is in bytes. So to be on safe side you can divide everything by 4.