Get an array out of a two dimensional array in PostgreSQL [duplicate]

Question

In PostgreSQL, I have a two dimensional array like:

SELECT ARRAY[[1,2,3],[4,5,6]];

and out of this array, I want to retrieve the whole 1st ([1,2,3]) array. Sadly something like:

SELECT (ARRAY[[1,2,3],[4,5,6]])[1];

doesn't work, since it returns a null.

Is it possible?

McNets · Accepted Answer · 2018-02-24 16:59:14Z

1

create table test(id int, val int[][]);
insert into test values (1, ARRAY[[1,2,3],[4,5,6]]);

✓

1 rows affected

select id, val[1:1] from test;

id | val      
-: | :--------
 1 | {{1,2,3}}

SELECT (ARRAY[[1,2,3],[4,5,6]])[1:1]

| array     |
| :-------- |
| {{1,2,3}} |

dbfiddle here

answered Feb 24, 2018 at 16:59

McNets

10.9k3 gold badges39 silver badges70 bronze badges

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

1 Comment

Works well too. I will probably use Roman's way, since i already understand how it works. Thank you

Shuumi · Accepted Answer · 2018-02-24 16:52:10Z

0

Okay, i worked it out:

SELECT (ARRAY[[1,2,3],[4,5,6]])[1][1:3]

does exacly what I wanted.

answered Feb 24, 2018 at 16:52

Shuumi

761 silver badge8 bronze badges

probably select(array[[1,2,3],[4,5,6]])[1][:] would be a bit more generic

@Roman: Your comment is the definitive answer for Postgres 9.6 or later.

That works even better, since it saves me the time used for calculating the size of my array, unless Postgres does that either way. Thank you