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.
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]
-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.
[2:] works for taking all elements except the first one.[:5] and [:].
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}.