How can I add the array to the array of arrays in SQL (Postgres)

Question

I have a table with an attribute that is an array of arrays.

Example: a={{1,A, 1},{A,B,C},{45,46,47}}

I want to add an array to the array "a".

Example: new_array={Z,T} to "a" in order to have a={{Z,T},{1,A, 1},{A,B,C},{45,46,47}} `

I used the command: update test set a = ARRAY['Z','T'] || a;

but I got this error: No operator matches the given name and argument type(s). You might need to add explicit type casts.

So, I tried to add the ::text[] parameter in this way: update test set a = ARRAY['Z','T']::text[] || a::text[]; but there is still an error:

ERROR:  cannot concatenate incompatible arrays
DETAIL:  Arrays with differing dimensions are not compatible for concatenation.

Hence, my question is: how can I add the array to the array of arrays? .

Clodoaldo Neto · Accepted Answer · 2013-08-04 14:39:00Z

2

To make them the same dimention add a null:

select '{{1,A, 1},{A,B,C},{45,46,47}}'::text[] || '{Z, T, null}';
                ?column?                 
-----------------------------------------
 {{1,A,1},{A,B,C},{45,46,47},{Z,T,NULL}}

answered Aug 4, 2013 at 14:39

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

2 Comments

DarkCoffee Over a year ago

Thank you for helping me. I need different dimension. Is it possible to have it?

Craig Ringer Over a year ago

@DarkCoffee No, Pg arrays need to be symmetric across the dimensions. They're not arrays of arrays, they are actual multidimensional arrays - think "like a matrix". They can't be sparse or short. It's to do with how the memory for them is managed internally and how they are accessed.

Collectives™ on Stack Overflow

How can I add the array to the array of arrays in SQL (Postgres)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related