In PostgreSQL, we can create arrays and select data from within them. We can select the whole array, an individual element, or a slice.

A slice is a portion of the array returned in its own array. For example, we could extract 3 elements from the middle of an array that has 9 elements.

When we select a slice, we specify the start position and the end position. Actually, we can leave either side open if we want to select all previous or subsequent elements to/from a certain point. Either way, the syntax is quite straightforward for selecting a slice from an array.

Example

Here’s a basic example to demonstrate:

SELECT a[2:4]
FROM (
    SELECT ARRAY[ 'Zebra', 'Bird', 'Mouse', 'Cow', 'Pig' ] AS a
    );

Result:

{Bird,Mouse,Cow}

In this example I extracted three elements from the middle of the array. I specified [2:4] which meant that all elements from the second one to the fourth are returned.

Open Ended Slices

We can also leave one end open, so that it returns all prior or subsequent elements from the specified point:

SELECT 
    a[3:] AS "1", 
    a[:3] AS "2"
FROM (
    SELECT ARRAY[ 'Zebra', 'Bird', 'Mouse', 'Cow', 'Pig' ] AS a
    );

Result:

        1        |         2          
-----------------+--------------------
 {Mouse,Cow,Pig} | {Zebra,Bird,Mouse}

Here the first column returns all elements from subscript 3 to the end. The second column returns all elements from the start until subscript 3.

Multi Dimensional Arrays

We can return slices from multi dimensional arrays too:

SELECT 
    a[1:2][2:3] 
FROM (
    SELECT ARRAY[ 
        ARRAY[ 32, 78, 14 ], 
        ARRAY[ 102, 99, 37 ], 
        ARRAY[ 18, 65, 29 ] 
        ] AS a
    );

Result:

         a         
-------------------
 {{78,14},{99,37}}

In this example I extracted the second and third elements of the first two arrays in the array at a.

Note that if any dimension is written as a slice (i.e. using the colon syntax) then all dimensions are treated as slices, even if not all of them are written as a slice (i.e. no colon). Therefore we could write the following:

SELECT 
    a[1:2][2] 
FROM (
    SELECT ARRAY[ 
        ARRAY[ 32, 78, 14 ], 
        ARRAY[ 102, 99, 37 ], 
        ARRAY[ 18, 65, 29 ] 
        ] AS a
    );

Result:

         a          
--------------------
 {{32,78},{102,99}}

Here, we only included a colon in the first pair of square brackets but not the second. Any dimension that doesn’t contain a colon is treated as starting from 1 and ending at the specified number. Therefore in our example, the [2] part meant that it started at the first element and ended at the second.