0

I tried to insert data from table A to table B but encounter the error below, even though there is no ASCII characters exists in table A. I tried to cast the data as char in table A, but it's not working so far.

[Amazon](500310) Invalid operation: Invalid input
Details: 
 -----------------------------------------------
  error:  Invalid input
  code:      8001
  context:   Only ASCII characters are allowed in fixed length strings. Invalid ASCII char: ef
  query:     24440940
  location:  string.cpp:204
  process:   query1_123_24440940 [pid=711]
  -----------------------------------------------;

At another cluster, I have no error and the cluster version is the same, 1.0.31651.

According to an article, comparing char and varchar, char might be converted to varchar internally, and depends on the data in columns, seems to make the error happen. I'm still not sure why, since there contains only numeric (product code) though.

Redshift CREATE TABLE Error - invalid characters

I'm thinking to use only varchar in that table A and table B. Is there any better idea?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Sachiko
  • 808
  • 1
  • 12
  • 31
  • 1
    If the data is numeric, why use char? wouldn't an integer or numeric be better? –  Oct 03 '21 at 13:51
  • 1
    The obvious thought is that there *is* non-numeric data in table A. Try a select on table A where you only pick non-numeric rows, and see what you get. –  Oct 03 '21 at 13:54
  • @Max Ganz II Thanks for your comment. It's only number but that product code can starts 0 so we need to handle as string. And it's all same digit so we set it as char. (Other columns in the row are only integer), there should be no ASCII in the row. – Sachiko Oct 03 '21 at 13:57
  • 1
    If the product code has a fixed length, then you can infer any leading zeros. Yes, I understand there *should be* no ASCII, but it looks like there is. Redshift seems to be finding some. It is very often the case that data is not clean. –  Oct 03 '21 at 15:09
  • @Max Ganz II, you saved my day!! I never doubt about it, but actually exists non-ASCII. Product Code 1, not 4digit exists I checked it by regexp_instr(product_code,'[^\x00-\x7F]'). I really, really appreciated your advice:) – Sachiko Oct 03 '21 at 16:50
  • My pleasure, Sachiko. –  Oct 03 '21 at 18:19

0 Answers0