Postgresql: dynamic slice notation for arrays

Question

After reading this I wrote a naive attempt to produce this

col1
---------
1
4
7

from this

ARRAY[[1,2,3], [4,5,6], [7,8,9]]

This works

SELECT unnest((ARRAY[[1,2,3], [4,5,6], [7,8,9]])[1:3][1:1]);

But I in my case, I don't know the length of the outer array.

So is there a way to hack together the slice "string" to take into account this variability?

Here was my attempt. I know, it's a bit funny

_ids := _ids_2D[('1:' || array_length(_ids_2D, 1)::text)::int][1:1];

As you can see, I just want to create the effect of [1:n]. Obviously '1:3' ain't going to parse nicely into what the array slice needs.

I could obviously use something like the unnest_2d_1d Erwin mentions in the answer linked above, but hoping for something more elegant.

Do you want an array with the first element of all nested (2nd level) of an array (1st level)? — lnrdo
– lnrdo, Commented Feb 23, 2015 at 16:10
I am not sure if the array_length would not work, because the 1:n is a string anyway. Need to check. — lnrdo
– lnrdo, Commented Feb 24, 2015 at 9:31

4 revs, 3 users 72% · Accepted Answer · 2017-03-13 09:50:25Z

5

If you are trying to get the first element of all nested (2nd dimension) arrays inside an array (1st dimension) then you may use

array_upper(anyarray, 1)

to get all elements of a specific dimension

anyarray[1:array_upper(anyarray, 1)][<dimension num>:<dimension num>]

e.g, to get all elements of the first dimension

anyarray[1:array_upper(anyarray, 1)][1:1]

as in the code above. Please refer to PostgreSQL manual section on Arrays for more information.

community wiki

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

1 Comment

Great stuff Inrdo! array_length that I was using would also work. But it was trying to make a string first that caused my problem.