PostgreSQL provides us with the ability to create arrays, extract element values from them, and more. When it comes to selecting values, there’s a special syntax that we must use in order to get the exact element that we want to select.

Basically, the syntax involves the array or column name, followed by a pair of square brackets with the subscript of the element that we want to select.

Example

Here’s a basic example to demonstrate:

SELECT a[2] 
FROM (
    SELECT '{ "Cat", "Dog", "Bird" }'::text[] AS a
    );

Result:

Dog

The part that reads a[2] is the part that selects the element from the array. In this case I chose to select the second element, and that’s why I used 2. By default, PostgreSQL arrays are one-based (i.e. their numbering starts at 1) and so 2 selects the second element.

So here it is selecting each element in its own column:

SELECT 
    a[1] AS "1",
    a[2] AS "2",
    a[3] AS "3"
FROM (
    SELECT '{ "Cat", "Dog", "Bird" }'::text[] AS a
    );

Result:

  1  |  2  |  3   
-----+-----+------
 Cat | Dog | Bird

Here’s another example, except this time we use an array constructor to build the array:

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

Result:

   1   |  2   |   3   |  4  |  5  
-------+------+-------+-----+-----
 Zebra | Bird | Mouse | Cow | Pig

Zero Based Arrays, etc

By default arrays are one based in PostgreSQL. This means that the numbering starts at 1, so that we can refer to the first item by using 1.

It is possible to change this though.

We can use subscripted assignment to reassign the numbers for each array element. For example, we can do the following:

SELECT 
    a[0] AS "0",
    a[1] AS "1",
    a[2] AS "2"
FROM (
    SELECT '[0:2]={ "Cat", "Dog", "Bird" }'::text[] AS a
    );

Result:

  0  |  1  |  2   
-----+-----+------
 Cat | Dog | Bird

In this case I reassigned the subscripts so that they start with zero. My array is therefore a zero based array.

To reassign the numbers, prepend the array with the new subscripts, separated by a colon, enclosed in square brackets, and with an equals sign on the right.

In my case I used [0:2]= to specify that the numbering goes from zero to two. This meant that I needed to use a[0] to refer to the first element, a[1] for the second, and a[2] for the third.

A Database Example

Working with arrays that are stored in the database is pretty much the same as the above. All we do is append the square brackets and subscript to the column name.

Suppose we have a table like this:

SELECT * FROM array_test;

Result:

      c1       |                     c2                      |                       c3                        
---------------+---------------------------------------------+-------------------------------------------------
 {1,2,3}       | {Rabbit,Cat,Dog,Monkey,Moth}                | {{1,2,3},{4,5,66},{7,8,9}}
 {41,52,67}    | {Zebra,Bird,Mouse,Cow,Pig,NULL,Swan}        | {{32,78,14},{102,99,37},{18,65,29}}
 [0:2]={8,7,9} | [-5:-1]={Gecko,Agra,Badger,Beaver,Platypus} | [1:3][10:12]={{45,78,13},{14,53,31},{98,26,27}}

We can select array elements like this:

SELECT c1[1] FROM array_test;

Result:

 c1 
----
  1
 41
  7

Here we returned the first element from the c1 column.

Actually, that’s only partly true.

The first two rows contain one based arrays, but the third row contains a zero based array for the c1 column. We know this because the value in the third row has [0:2]= prepended to the array.

So when we used c1[1] in our query, it selected the first value from the first two arrays but the second value from the third (due to its numbering starting at zero).

Multi Dimensional Arrays

We can select elements from within multi dimensional arrays by using multiple pairs of square brackets. For example for two dimensional arrays we can use two pairs of square brackets. The first pair refers to the first dimension, the second pair to the second dimension, and so on.

Example:

SELECT a[2][3] 
FROM (
    SELECT '{ 
        {"Cat", "Dog", "Rat"}, 
        {"Bird", "Bear", "Zebra"}, 
        {"Ant", "Buffalo", "Antelope"} 
        }'::text[] AS a
    );

Result:

Zebra

Here I got the value of the third array element of the second array (within the outer array).

Here’s an example using the database from the previous example:

SELECT c3[2][3] FROM array_test
WHERE c1 = '{1,2,3}';

Result:

66

In this case I used a WHERE clause to narrow the query down to a single row. It returned the value of the third element of the second array.

Extract a Slice

We can also extract a segment from an array by specifying the start point and end point, separated by a colon:

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.

We can also leave one end open:

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.