COPY's usage with complex types - ARRAY

xpegenaute

I would like to be able to COPY into Vertica data using a complex type, in this case an Array, is it possible ?

For example, having this dataset, how could I use COPY to load the data on Vertica with the "accepted_forms" as an array ?

id	name	accepted_forms
1	foo	"[form1, form2]"
2	bar	"[form2]"

marcothesane

I did not manage without a STRING_TO_ARRAY() function call

DROP TABLE IF EXISTS with_array;
-- out DROP TABLE
CREATE TABLE with_array (
  id          INT
, name        VARCHAR(3)
, accepted_forms ARRAY[VARCHAR(5),9]
);
-- out CREATE TABLE

COPY with_array (
  id
, name
, accform_in FILLER VARCHAR(64)
, accepted_forms AS STRING_TO_ARRAY(accform_in)
)
FROM LOCAL STDIN  DELIMITER ';' ENCLOSED BY '"' SKIP 1 EXCEPTIONS '/dev/tty';
id;name;accepted_forms
1;foo;"[form1, form2]"
2;bar;"[form2]"
\.
-- out  Rows Loaded 
-- out -------------
-- out            2

SELECT * FROM with_array;
-- out  id | name |  accepted_forms   
-- out ----+------+-------------------
-- out   1 | foo  | ["form1","form2"]
-- out   2 | bar  | ["form2"]