How to Copy text to ARRAY type column

liquidbear

i saw the follwing article

https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/DataLoad/LoadingUTF-8FormatData.htm

In this article, we have confirmed that COPY is performed using the query below.

CREATE TABLE researchers (id INT, name VARCHAR, grants ARRAY[VARCHAR], values ARRAY[INT]);

COPY researchers FROM STDIN;
--- Enter data to be copied followed by a newline.
--- End with a backslash and a period on a line by itself.
--- 42|Sheldon Cooper|[US-7376,DARPA-1567]|[65000,135000]
--- 17|Howard Wolowitz|[NASA-1683,NASA-7867,SPX-76]|[16700,85000,45000]
--- \.

So I created the following text file based on the query above.

42|Sheldon Cooper|[US-7376,DARPA-1567]|[65000,135000]

but, i saw below error

[0A000][9222] [Vertica]VJDBC ERROR: Default COPY parsers do not support arrays yet

using query 'COPY researchers FROM LOCAL '/Users/liquid.bear/Desktop/test.txt';

Looking at the message, it seems that the ARRAY type cannot be specified in the basic COPY statement, so how should I modify it?

i use vertica v10.0.0-2

mosheg

In addition, I've tried the following on v12.0.1 and it worked.

cat test.txt
42|Sheldon Cooper|[US-7376,DARPA-1567]|[65000,135000]
17|Howard Wolowitz|[NASA-1683,NASA-7867,SPX-76]|[16700,85000,45000]

CREATE TABLE researchers (id INT, name VARCHAR, grants ARRAY[VARCHAR], values ARRAY[INT]);
COPY researchers FROM LOCAL '/mnt/c/DIR4VM/TMP/test.txt';
SELECT * FROM researchers;
 id |      name       |               grants               |       values
----+-----------------+------------------------------------+---------------------
 17 | Howard Wolowitz | ["NASA-1683","NASA-7867","SPX-76"] | [16700,85000,45000]
 42 | Sheldon Cooper  | ["US-7376","DARPA-1567"]           | [65000,135000]
(2 rows)

I think it will also work on v11.0 because the following error message do not exist in v11.0 documentation:
"Default COPY parsers do not support arrays yet"

SergeB

Support for array with the default DELIMITED parser was added in 10.0.1 (see https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/NewFeatures/10.0/10.0.1/LoadingData)

marcothesane

You can always use expressions in the COPY statement:

CREATE TABLE public.researchers (
  id INT
, name VARCHAR
, grants ARRAY[VARCHAR]
, VALUES ARRAY[INT]
);
-- out CREATE TABLE
-- out Time: First fetch (0 rows): 339.992 ms. All rows formatted: 340.329 ms
COPY public.researchers (
  id
, name
, grants_in FILLER VARCHAR, grants AS string_to_array(grants_in)
, values_in FILLER VARCHAR, values AS string_to_array(values_in)::ARRAY[INT]
)
FROM STDIN
REJECTED DATA '/dev/tty' EXCEPTIONS 'public.researchers.log'
;
42|Sheldon Cooper|[US-7376,DARPA-1567]|[65000,135000]
\.
-- out NOTICE 7850:  In a multi-threaded load, rejected record data may be written to additional files
-- out HINT:  Exceptions may be written to files [public.researchers.log], [public.researchers.log.1], etc
-- out Time: First fetch (0 rows): 405.654 ms. All rows formatted: 405.788 ms
SELECT * FROM public.researchers;
-- out  id |      name      |          grants          |     VALUES     
-- out ----+----------------+--------------------------+----------------
-- out  42 | Sheldon Cooper | ["US-7376","DARPA-1567"] | [65000,135000]

marcothesane

You can always use expressions in the COPY statement:

CREATE TABLE public.researchers (
  id INT
, name VARCHAR
, grants ARRAY[VARCHAR]
, VALUES ARRAY[INT]
);
-- out CREATE TABLE
-- out Time: First fetch (0 rows): 339.992 ms. All rows formatted: 340.329 ms
COPY public.researchers (
  id
, name
, grants_in FILLER VARCHAR, grants AS string_to_array(grants_in)
, values_in FILLER VARCHAR, values AS string_to_array(values_in)::ARRAY[INT]
)
FROM STDIN
REJECTED DATA '/dev/tty' EXCEPTIONS 'public.researchers.log'
;
42|Sheldon Cooper|[US-7376,DARPA-1567]|[65000,135000]
\.
-- out NOTICE 7850:  In a multi-threaded load, rejected record data may be written to additional files
-- out HINT:  Exceptions may be written to files [public.researchers.log], [public.researchers.log.1], etc
-- out Time: First fetch (0 rows): 405.654 ms. All rows formatted: 405.788 ms
SELECT * FROM public.researchers;
-- out  id |      name      |          grants          |     VALUES     
-- out ----+----------------+--------------------------+----------------
-- out  42 | Sheldon Cooper | ["US-7376","DARPA-1567"] | [65000,135000]

ryankim1726

I have a table with a column of type ARRAY. The column is called "name" and it's a varchar(255).

I want to insert a new row into the table, and I want to copy the contents of another column called "full_name" into the "name" column.

SruthiA

@ryankim1726 : Please find the sample on how to insert the data into array column..

CREATE TABLE transactions (tid INT, name ARRAY[VARCHAR,255], quantities ARRAY[INT,100]);
CREATE TABLE

=> INSERT INTO transactions VALUES (12345, ARRAY['name1', 'name2'], ARRAY[15,2]);

OUTPUT

  1

is full_name column present in another table? if so you can use insert select.. Please share sample example from environment and error you are receiving.

marcothesane

The question is confusing to me. Is your "name" column now a VARCHAR(255), an ARRAY[INT] or an ARRAY[VARCHAR(255)]?