How deal with Redshift lack of support for Arrays

Question

Redshift does not support Arrays, however my source database has several Array columns that I need in Redshift.

How should this field type be handled when trying to migrate it into Redshift?

Answer 1

While Redshift does not support arrays in the PostgreSQL-sense, it provides some JSON functions you might want to have a look at: http://docs.aws.amazon.com/redshift/latest/dg/json-functions.html

You can insert arrays into varchar columns:

create temporary table _test (col1 varchar(20));
insert into _test values ('[1,2,3]');

Then using json_extract_array_element_text() would yield:

db=# select json_extract_array_element_text(col1, 2) from _test;
 json_extract_array_element_text
---------------------------------
 3
(1 row)