facing issue in complex data

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


Best Answers

  • Options
    moshegmosheg Vertica Employee Administrator
    Answer ✓

    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)
  • Options
    Bryan_HBryan_H Vertica Employee Administrator
    edited February 3 Answer ✓

    Parquet should work, and JSON is another option for text files:
    d2=> create table nestarray (av array[varchar(80)], aai array[array[int]]);
    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.

    Rows Loaded


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


  • Options
    edited February 3

    @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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file