CODEWITHSUNDEEP
arrayspostgresql
user3684490
user3684490

Reputation: 43

PostgreSQL: concatenate nested arrays with differing element dimensions

Concatenating the nested arrays {{1,2}} and {{3,4}} is no problem at all:

SELECT array_cat(
           ARRAY[ARRAY[1,2]]
         , ARRAY[ARRAY[3,4]]
       )

   array_cat   
---------------
 {{1,2},{3,4}}

But how to concatenate {{1,2}} and {{3}} in order to get {{1,2},{3}}?

SELECT array_cat(
           ARRAY[ARRAY[1,2]]
         , ARRAY[ARRAY[3]]
       )
psql: …: ERROR:  cannot concatenate incompatible arrays
DETAIL:  Arrays with differing element dimensions are not compatible
         for concatenation.

Upvotes: 4

Views: 1952

Answers (2)

Geoffrey Garrett
Geoffrey Garrett

Reputation: 305

I ended up using the following method, instead of padding with NULL, for jagged arrays which extends from the original example:

SELECT jsonb_agg(elem) FROM (
  SELECT jsonb_array_elements(
    jsonb_agg(to_jsonb(v))
  ) AS elem
  FROM (
    VALUES 
    (ARRAY[1,2]), 
    (ARRAY[3])
  ) AS vals(v)
) q;

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656231

This is impossible in PostgreSQL. Multi-dimensional arrays must have the same number of element dimensions, just as the error message informs. Per documentation:

Multidimensional arrays must have matching extents for each dimension. A mismatch causes an error.

You might want to pad with NULL or some other dummy value ...

Upvotes: 2

Related Questions