Skip to main content
Question

COPY's usage with complex types - ARRAY

  • November 23, 2023
  • 1 reply
  • 0 views

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]"

1 reply

marcothesane
Forum|alt.badge.img+1
  • Participating Frequently
  • November 24, 2023

I did not manage without a STRING_TO_ARRAY() function call :smile:

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"]