PostgreSQL: concatenate nested arrays with differing element dimensions

Question

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.

What structure do you expect for the final array? {{1,2},{3}} ? — Tony
– Tony, Commented May 28, 2014 at 16:34

Erwin Brandstetter · Accepted Answer · 2014-05-28 16:37:08Z

2

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 ...

answered May 28, 2014 at 16:37

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3684490 Over a year ago

I've already feared that. Seems like I have to look for another approach for my application.

Geoffrey Garrett · Accepted Answer · 2024-03-21 09:26:05Z

0

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;

answered Mar 21, 2024 at 9:26

Geoffrey Garrett

3053 silver badges10 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: concatenate nested arrays with differing element dimensions

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related