24

Assuming I have an array in one of my postgres columns:

> select array[1,2,3,4];
   array   
-----------
 {1,2,3,4}

How do I select a subset of the items from that array, in general? For instance, if I want to select items from the x index to the y index, how would I grab the items in that range (x to y)?

I'm using PostgreSQL 9.4.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
jbranchaud
  • 5,909
  • 9
  • 45
  • 70

1 Answers1

51

From the fine manual:

8.15.3. Accessing Arrays
[...]
We can also access arbitrary rectangular slices of an array, or subarrays. An array slice is denoted by writing lower-bound:upper-bound for one or more array dimensions.
[...]
It is possible to omit the lower-bound and/or upper-bound of a slice specifier; the missing bound is replaced by the lower or upper limit of the array's subscripts.

For example:

=> select (array[1,2,3,4,5,6])[2:5];
   array   
-----------
 {2,3,4,5}
(1 row)

=> select (array[1,2,3,4,5,6])[:5];
    array    
-------------
 {1,2,3,4,5}
(1 row)

=> select (array[1,2,3,4,5,6])[2:];
    array    
-------------
 {2,3,4,5,6}
(1 row)

=> select (array[1,2,3,4,5,6])[:];
     array     
---------------
 {1,2,3,4,5,6}
(1 row)

So to get a slice from index x to y (inclusive), you'd say:

array_column[x:y]
mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • Dose it support negative bound? like [-2:-5] – Moon soon Apr 23 '17 at 10:22
  • @Wooden Depends what you expect that `-2` to do: "Subscripted assignment allows creation of arrays that do not use one-based subscripts. For example one might assign to `myarray[-2:7]` to create an array with subscript values from `-2` to `7`.". That's straight out of the documentation. – mu is too short Apr 23 '17 at 17:34
  • 1
    Note that `[2:]` works for taking all elements except the first one. – Steven Soroka Apr 12 '19 at 16:31
  • @StevenSoroka That's actually mentioned a couple paragraphs down in the docs but worth noting anyway. You can also say `[:5]` and `[:]`. – mu is too short Apr 12 '19 at 18:29
  • 1
    Also note that for sparse arrays, the slices may not return what you expect. Given an empty array of integers where we only set `arr[3] = 3` and `arr[5] = 5`, the result for `arr[1]` would be `NULL`, but the result of `arr[1:]` is `{3,NULL,5}`. – Zilk Aug 01 '23 at 09:34