facing issue in complex data

sonu10428

INSERT INTO array_test VALUES (123, '[d, e, r]', '[[1,2,3]]');
in single array data type data has been inserted successfully but in nested array getting error

mosheg

Try one of the options below:

CREATE TABLE public.array_test
(
    f1 int,
    f2 ARRAY[varchar(80), 10],
    f3 ARRAY[ARRAY[int], 10]
);
INSERT INTO array_test VALUES (123, ARRAY['d', 'e', 'r'], ARRAY[ARRAY[1,2,3]]);
INSERT INTO array_test VALUES (123, '["d", "e", "r"]', ARRAY[[1,2,3]]);

SELECT * FROM array_test;
 f1  |      f2       |    f3
-----+---------------+-----------
 123 | ["d","e","r"] | [[1,2,3]]
 123 | ["d","e","r"] | [[1,2,3]]
(2 rows)

Bryan_H

Parquet should work, and JSON is another option for text files:
d2=> create table nestarray (av array[varchar(80)], aai array[array[int]]);
CREATE TABLE
d2=> copy nestarray from local stdin parser fjsonparser();
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
{"av":["a","b","c"],"aai":[[1,2],[1,2,3]]}
.

Rows Loaded

       1

d2=> select * from nestarray ;
av | aai
---------------+-----------------
["a","b","c"] | [[1,2],[1,2,3]]

sonu10428

@mosheg thanks it was working for me but how can we make a csv data format for copy data from csv to this table. i mean we can do single insert by using ARRAY but how can we do bulk upload from csv/parquet ? and what will be data fromat for these files