I create in the posgresovskaya table a numeric field of type array:

CREATE TABLE aaa ( test int[][] );

This insertion is done without problems:

 INSERT INTO aaa (test) VALUES ('{{4,5,7,9}}'), ('{{2},{3},{4}}'), (NULL), ('{8,9}');

There is a strong desire to perform an insert:

 INSERT INTO aaa (test) VALUES ('{{1},{3},{2},{},{}}'), ('{{1},{4,5,8},{},{},{}}'), ('{{1},{6},{},{},{}}'), ('{{1},{7,9},{},{},{}}');

... but an error is returned: "for multidimensional arrays, nested definitions must be specified according to the dimension" :

I can not figure out how to do it. I also cannot understand the logic of PostgreSQL regarding arrays. I would be grateful for both correcting the code and explaining why the lines from the first example are written with INSERT, but not from the last.

    2 answers 2

    The request should look like this:

     INSERT INTO aaa (test) VALUES ('{{1}, {3}, {2}, {NULL}, {NULL}}'), ('{{1}, {4,5,8}, {NULL,NULL,NULL}, {NULL,NULL,NULL}, {NULL,NULL,NULL}}'), ('{{1}, {6}, {NULL}, {NULL}, {NULL}}'), ('{{1}, {7,9}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}}');

    The number of elements in an array can grow once (and only once) within one line ; NULL will automatically be added to previous subarrays. But can not decrease . In other words, it works:

     INSERT INTO aaa (test) VALUES ('{{1}, {2,3}}'); INSERT INTO aaa (test) VALUES ('{{1}, {2,3,4}, {5,6,7}}');

    And such requests will not be executed:

     INSERT INTO aaa (test) VALUES ('{{1,2}, {3}}'); INSERT INTO aaa (test) VALUES ('{{1}, {2,3}, {4,5,6}}');

    Let's return to the corrected insert request.

    • Lines 1 and 3: in nested arrays one by one element;
    • Line 2: the second nested array contains three elements, so all subsequent elements must have three elements (where there is no data, set NULL);
    • Line 4: the second array contains two elements, so subsequent nested arrays also contain two elements.

    Only previous arrays within the string are affected. Therefore, there is no need to write three elements in a nested array in all rows.

      Apparently, there can be no array element without a value. Maybe try NULL as an empty value?

      • The trick is that within a single array, all subarrays must be the same size. Accordingly, it may take several NULLs. - Denis Khvorostin
      • Thanks for the enlightenment. Basically, logical work with arrays. - orkaan