Skip to main content
Question

What will be format of nested array data in csv

  • February 3, 2024
  • 1 reply
  • 4 views

sonu10428
When I'm going to insert data like this [[1,2,3]] getting error, so what could be nested array data structures in csv

1 reply

mosheg
Forum|alt.badge.img+2
  • Participating Frequently
  • February 5, 2024

Here is an example of how to load a nested array in INSERT and via bulk load using a COPY statement.

CREATE TABLE array_test
(
    f1 int,
    f2 ARRAY[VARCHAR(80)],
    f3 ARRAY[ARRAY[INT]]
);
INSERT INTO array_test VALUES (1, ARRAY['a', 'b', 'c'], ARRAY[ARRAY[1,2,3]]);
INSERT INTO array_test VALUES (2, '["d", "e", "f"]', ARRAY[[4,5,6]]);

COPY array_test FROM STDIN PARSER fjsonparser();
{"f1":3,"f2":["g","h","i"],"f3":[[7,8,9]]}
{"f1":4,"f2":["j","k","l"],"f3":[[10,11,12]]}
\.

SELECT * FROM array_test order by 1;
--  f1 |      f2       |      f3
-- ----+---------------+--------------
--   1 | ["a","b","c"] | [[1,2,3]]
--   2 | ["d","e","f"] | [[4,5,6]]
--   3 | ["g","h","i"] | [[7,8,9]]
--   4 | ["j","k","l"] | [[10,11,12]]
-- (4 rows)