different approach to multi-dimensional arrays in postgresql

Question

I'm working with a problem similar to this one: PostgreSQL: concatenate nested arrays with differing element dimensions

In this problem, a user wants to be able to create a multi-dimensional array like:

{{1,2},{3}}

using something like:

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

Erwin Brandsetter says it's not possible without a cheap trick like padding with some dummy value.

I'm wondering if I can approach this problem in a different way to construct a multi-dimensional array. Or some equivalent structure that's basically the same thing. Like constructing some data type to replace the int[] structure. Or possibly use the postgres json feature.

I would preferably like the solution to allow for easy appending/ replacing. Multidimensional array manipulation so far hasn't been fun in postgresql.

Roman Tkachuk · Accepted Answer · 2018-07-25 21:08:10Z

4

Probably jsonb is only one possible solution to multidimensional arrays with different dimension of array.

answered Jul 25, 2018 at 21:08

Roman Tkachuk

3,2761 gold badge18 silver badges16 bronze badges

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

1 Comment

agent nate Over a year ago

Thank you. I did decide to go down the jsonb road and it seemed possible but gave up and wrote what I wanted to do in python/psychopg2.

Pavel Stehule · Accepted Answer · 2018-07-24 05:23:11Z

0

Simple response - PostgreSQL has not array of arrays type. It is not dynamic type (collection) based system. Probably the user is on wrong way with no end. There is not any simple solution. Maybe you should to use different application or you should to change you mind to be more relational.

answered Jul 24, 2018 at 5:23

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Collectives™ on Stack Overflow

different approach to multi-dimensional arrays in postgresql

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