Understanding Arrays in PostgreSQL

Posted on by Ian

In PostgreSQL we have the ability to create variable-length multidimensional arrays and define columns as array types.

Here’s a quick overview of PostgreSQL arrays.

Creating an Array Column

We have a few options when it comes to creating a column with an array type in PostgreSQL. Regardless of which method we use, we need to specify the data type of the elements within the array.

We don’t use the keyword array in the same fashion that we do when defining other types.

One way to define a column as an array is to append square brackets ([]) to the data type name of the array elements.

Here’s an example:

CREATE TABLE array_test(
    c1 integer[],
    c2 text[],
    c3 integer[][]
);

That code creates a table with three columns. All three columns are defined as array types.

  • The c1 column is a single dimensional integer array, which means it accepts an array of integer values. We know it’s an array type because we appended square brackets to the data type name. And we know it’s single dimensional because it only has one pair of square brackets.
  • The c2 column is a single dimensional text array.
  • The c3 array is a multi dimensional integer array. It’s multi dimensional because we included more than one pair of square brackets. In this case there are two sets of square brackets, so it’s a two dimensional array.

At the time of writing, PostgreSQL 16.2 does not enforce the number of dimensions. So we could insert say, a two dimensional array into our c1 column, or a one dimensional array into the c3 column for example.

It’s possible to specify the array size, but PostgreSQL basically ignores this (at least as of PostgreSQL 16 at the time of writing). So we could do the following:

CREATE TABLE array_test_2(
    c1 integer[3],
    c2 text[4],
    c3 integer[3][2]
);

But as alluded to, PostgreSQL does not enforce any such size restrictions. However, arrays must be rectangular. Therefore, all sub-arrays at the same level must be of the same dimensions.

We can also use the ARRAY keyword to define a column as a one dimensional array:

CREATE TABLE array_test_3(
    c1 integer ARRAY,
    c2 text ARRAY[4],
    c3 integer ARRAY
);

We have the option of specifying the array size by appending square brackets to the ARRAY keyword (as seen in the definition for c2) but again, such size limits are not enforced by PostgreSQL.

Using the ARRAY keyword helps keep the code compliant with the SQL standard, but is entirely optional.

Inserting an Array

Once we’ve created a table or column that accepts an array type, we can go ahead and insert an array into it.

One way to insert an array is to provide it as a literal constant:

INSERT INTO array_test VALUES 
    (
        '{ 1,2,3 }',
        '{ "Cat", "Dog", "Horse", "Bear", "Duck" }',
        '{ {1,2,3}, {4,5,6}, {7,8,9} }'
    );

Here, all three arrays are provided as a literal constant. These are provided in the format specified by the following syntax:

'{ val1 delim val2 delim ... }'

Here, delim is the delimiter character for the type. This delimiter is stored in the pg_type catalog. Most of the standard data types use a comma (,), but the box type uses a semicolon (;).

Each val is either a constant of the array element type, or a subarray.

Another way to insert an array is with the ARRAY constructor:

INSERT INTO array_test VALUES 
    (
        ARRAY[ 41, 52, 67 ],
        ARRAY[ 'Zebra', 'Bird', 'Mouse', 'Cow', 'Pig' ],
        ARRAY[ ARRAY[ 32, 78, 14 ], ARRAY[ 102, 99, 37 ], ARRAY[ 18, 65, 29 ] ]
    );

We can also use the ARRAY constructor by itself to create an array on the fly:

SELECT ARRAY[ 1,2,3 ];

Result:

  array  
---------
 {1,2,3}

As mentioned, all sub-arrays at the same level must have the same dimensions. Therefore, we can do this:

SELECT ARRAY[ ARRAY[1,2,3], ARRAY[4,5,6] ];

Result:

       array       
-------------------
 {{1,2,3},{4,5,6}}

But we can’t do this:

SELECT ARRAY[ ARRAY[1,2,3], ARRAY[4,5] ];

Result:

ERROR:  multidimensional arrays must have array expressions with matching dimensions

Here, the outer array contains two inner arrays. The first one is defined as having three elements but the second has two. This is a “no no”, and we got an error accordingly.

Selecting an Array

Let’s return all data from our table:

SELECT * FROM array_test;

Result:

     c1     |             c2             |                 c3                  
------------+----------------------------+-------------------------------------
 {1,2,3}    | {Cat,Dog,Horse,Bear,Duck}  | {{1,2,3},{4,5,6},{7,8,9}}
 {41,52,67} | {Zebra,Bird,Mouse,Cow,Pig} | {{32,78,14},{102,99,37},{18,65,29}}

As expected, the rows reflect the data that we inserted.

We can return any of these arrays by simply including that column in our SELECT list:

SELECT c2 FROM array_test;

Result:

             c2             
----------------------------
 {Cat,Dog,Horse,Bear,Duck}
 {Zebra,Bird,Mouse,Cow,Pig}

In this case we returned the arrays in the c2 column. We didn’t narrow it down with a WHERE clause or anything, so all rows were returned.

Selecting Array Elements

We can select specific elements by specifying the element index within square brackets.

Here’s an example:

SELECT c2[1] FROM array_test;

Result:

  c2   
-------
 Cat
 Zebra

Array subscripts start at 1 by default, and so c2[1] returns the first element of the array.

We can also use this syntax in a WHERE clause to narrow the results down:

SELECT c2 FROM array_test 
WHERE c1[1] = 41;

Result:

             c2             
----------------------------
 {Zebra,Bird,Mouse,Cow,Pig}

In the above example we returned the whole array, but we can also return individual elements from within that array:

SELECT c2[1] FROM array_test 
WHERE c1[1] = 41;

Result:

  c2   
-------
 Zebra

We can access elements from multi dimensional arrays by using multiple sets of square brackets:

SELECT c3[2][3] FROM array_test;

Result:

 c3 
----
  6
 37

In this case we retrieved the third element of the second array in the c3 column.

Returning Slices from Arrays

We can return parts of an array by using a syntax that specifies the lower bound and the upper bound for the elements. This is written with a colon syntax, where the lower bound and upper bound is separated by a colon.

Example:

SELECT c2[2:4] FROM array_test;

Result:

        c2        
------------------
 {Dog,Horse,Bear}
 {Bird,Mouse,Cow}

Here we extracted the second, third and fourth elements of each array.

We can leave the lower or upper bound open, so that it returns all prior or subsequent elements from the given point. For example, if we omit our upper bound of 4, we get the following:

SELECT c2[2:] FROM array_test;

Result:

          c2           
-----------------------
 {Dog,Horse,Bear,Duck}
 {Bird,Mouse,Cow,Pig}

This time it returned all elements from the second one to the end of the array.

We can do a similar thing to get all elements from the start of the array up to a certain point:

SELECT c2[:2] FROM array_test;

Result:

      c2      
--------------
 {Cat,Dog}
 {Zebra,Bird}

This time we got all elements up to the second element.

On multi dimensional arrays we can use multiple pairs of square brackets:

SELECT c3[1:2][2:3] FROM array_test;

Result:

        c3         
-------------------
 {{2,3},{5,6}}
 {{78,14},{99,37}}

In this case I got the second and third elements of the first two arrays in the array at c3.

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 c3[1:2][3] FROM array_test;

Result:

            c3            
--------------------------
 {{1,2,3},{4,5,6}}
 {{32,78,14},{102,99,37}}

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 [3] part meant that it started at the first element and ended at the third.

Get the Array’s Dimension

We can use the array_dims() function to return the dimension of a given array:

SELECT array_dims(c3) FROM array_test;

Result:

 array_dims 
------------
 [1:3][1:3]
 [1:3][1:3]

Here it is across all columns:

SELECT 
    array_dims(c1) AS c1, 
    array_dims(c2) AS c2, 
    array_dims(c3) AS c3 
FROM array_test;

Result:

  c1   |  c2   |     c3     
-------+-------+------------
 [1:3] | [1:5] | [1:3][1:3]
 [1:3] | [1:5] | [1:3][1:3]

Another way to do it is with the array_upper() and array_lower() functions:

SELECT 
    array_lower(c3, 2) AS lower, 
    array_upper(c3, 2) AS upper
FROM array_test;

Result:

 lower | upper 
-------+-------
     1 |     3
     1 |     3

That returned the upper and lower bounds for the second dimension of the c3 array.

The array_upper() and array_lower() functions return an integer whereas array_dims() returns text.

Get the Array’s Cardinality

We can use the cardinality() function to return the total number of elements in an array across all dimensions:

SELECT cardinality(c3) FROM array_test;

Result:

 cardinality 
-------------
           9
           9

Here we can see that the function returned the total number of elements across the arrays at c3.

Search an Array

Previously we selected the value from a given index of an array. But we also have the option of searching through the whole array for a given value.

One way to do this is with the ANY() construct:

SELECT * FROM array_test 
WHERE 'Horse' = ANY(c2);

Result:

   c1    |            c2             |            c3             
---------+---------------------------+---------------------------
 {1,2,3} | {Cat,Dog,Horse,Bear,Duck} | {{1,2,3},{4,5,6},{7,8,9}}

We can also use SOME(), which is a synonym for ANY(). So the following produces the same result:

SELECT * FROM array_test 
WHERE 'Horse' = SOME(c2);

Another way to search through an array is to use the ALL() construct:

SELECT * FROM array_test 
WHERE 40 < ALL(c1);

Result:

     c1     |             c2             |                 c3                  
------------+----------------------------+-------------------------------------
 {41,52,67} | {Zebra,Bird,Mouse,Cow,Pig} | {{32,78,14},{102,99,37},{18,65,29}}

In this case all elements of c1 are greater than 40 on the second row, and so ALL() returns true for that row, which results in the row being returned.

Another tool we have for searching arrays is the && operator:

SELECT * FROM array_test 
WHERE c2 && ARRAY['Mouse'];

Result:

     c1     |             c2             |                 c3                  
------------+----------------------------+-------------------------------------
 {41,52,67} | {Zebra,Bird,Mouse,Cow,Pig} | {{32,78,14},{102,99,37},{18,65,29}}

The && operator looks for an overlap between two arrays.

Get the Position of a Value in an Array

We can use array_position() and array_positions() to get the position of a given value within an array.

The array_position() function returns the first occurrence of the value in the array, whereas array_positions() returns all positions:

SELECT 
    array_position(ARRAY[ 'Cat', 'Dog', 'Cat', 'Horse', 'Cat' ], 'Cat'),
    array_positions(ARRAY[ 'Cat', 'Dog', 'Cat', 'Horse', 'Cat' ], 'Cat');

Result:

 array_position | array_positions 
----------------+-----------------
              1 | {1,3,5}

Replace an Array

We can replace an array in full by using the literal constant:

UPDATE array_test 
SET c2 = '{"a", "b", "c", "d", "e"}'
WHERE c1 = '{1,2,3}';

SELECT * FROM array_test;

Result:

     c1     |             c2             |                 c3                  
------------+----------------------------+-------------------------------------
 {41,52,67} | {Zebra,Bird,Mouse,Cow,Pig} | {{32,78,14},{102,99,37},{18,65,29}}
 {1,2,3}    | {a,b,c,d,e}                | {{1,2,3},{4,5,6},{7,8,9}}

Or by using the ARRAY constructor:

UPDATE array_test 
SET c2 = ARRAY['Rabbit', 'Rat', 'Cheetah', 'Lion', 'Leopard']
WHERE c1 = '{1,2,3}';

SELECT * FROM array_test;

Result:

     c1     |                c2                 |                 c3                  
------------+-----------------------------------+-------------------------------------
 {41,52,67} | {Zebra,Bird,Mouse,Cow,Pig}        | {{32,78,14},{102,99,37},{18,65,29}}
 {1,2,3}    | {Rabbit,Rat,Cheetah,Lion,Leopard} | {{1,2,3},{4,5,6},{7,8,9}}

Update an Array Element

We can update a specific array element by referencing its index:

UPDATE array_test 
SET c2[2] = 'Squirrel'
WHERE c1 = '{1,2,3}';

SELECT * FROM array_test;

Result:

     c1     |                   c2                   |                 c3                  
------------+----------------------------------------+-------------------------------------
 {41,52,67} | {Zebra,Bird,Mouse,Cow,Pig}             | {{32,78,14},{102,99,37},{18,65,29}}
 {1,2,3}    | {Rabbit,Squirrel,Cheetah,Lion,Leopard} | {{1,2,3},{4,5,6},{7,8,9}}

And here it is updating a multi dimensional array:

UPDATE array_test 
SET c3[2][3] = 66
WHERE c1 = '{1,2,3}';

SELECT * FROM array_test;

Result:

     c1     |                   c2                   |                 c3                  
------------+----------------------------------------+-------------------------------------
 {41,52,67} | {Zebra,Bird,Mouse,Cow,Pig}             | {{32,78,14},{102,99,37},{18,65,29}}
 {1,2,3}    | {Rabbit,Squirrel,Cheetah,Lion,Leopard} | {{1,2,3},{4,5,66},{7,8,9}}

Update a Whole Slice

We can update a whole slice like this:

UPDATE array_test 
SET c2[2:4] = '{"Cat","Dog","Cow"}'
WHERE c1 = '{1,2,3}';

SELECT * FROM array_test;

Result:

     c1     |              c2              |                 c3                  
------------+------------------------------+-------------------------------------
 {41,52,67} | {Zebra,Bird,Mouse,Cow,Pig}   | {{32,78,14},{102,99,37},{18,65,29}}
 {1,2,3}    | {Rabbit,Cat,Dog,Cow,Leopard} | {{1,2,3},{4,5,66},{7,8,9}}

We can also leave one of the bounds open in order to update all remaining or preceding elements. For example, the following updates all elements from the fourth subscript:

UPDATE array_test 
SET c2[4:] = '{"Buffalo","Ant"}'
WHERE c1 = '{1,2,3}';

SELECT * FROM array_test;

Result:

     c1     |              c2              |                 c3                  
------------+------------------------------+-------------------------------------
 {41,52,67} | {Zebra,Bird,Mouse,Cow,Pig}   | {{32,78,14},{102,99,37},{18,65,29}}
 {1,2,3}    | {Rabbit,Cat,Dog,Buffalo,Ant} | {{1,2,3},{4,5,66},{7,8,9}}

Here’s what happens if we provide too many values:

UPDATE array_test 
SET c2[4:] = '{"Monkey","Moth","Butterfly"}'
WHERE c1 = '{1,2,3}';

SELECT * FROM array_test;

Result:

     c1     |              c2              |                 c3                  
------------+------------------------------+-------------------------------------
 {41,52,67} | {Zebra,Bird,Mouse,Cow,Pig}   | {{32,78,14},{102,99,37},{18,65,29}}
 {1,2,3}    | {Rabbit,Cat,Dog,Monkey,Moth} | {{1,2,3},{4,5,66},{7,8,9}}

Only the remaining elements were updated. So in our case, the first two elements (Monkey and Moth) made their way on to the array, but the third one (Butterfly) didn’t.

Enlarge an Array

We can enlarge an array by assigning an element to an index that doesn’t currently exist:

UPDATE array_test 
SET c2[7] = 'Swan'
WHERE c1 = '{41,52,67}';

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}}

When we do this, any positions between existing elements and the specified one/s will be set to NULL. We can see this in our example. I specified that Swan should be inserted into the 7th position, but there were previously only 5 positions. Therefore, the 6th position was filled with NULL in order to get Swan to the 7th position.

Specify the Lower Bound of an Array

By default, the lower bound of an array is 1. This means that the first element starts at 1, the second starts at 2, and so on.

But we do have the option of changing this.

We can use subscript assignment to specify the subscript for the array. We can do this by prepending the array with the subscript ranges enclosed in square brackets, with the dimensions separated by a colon.

Example:

SELECT ('[0:2]={ "Cat", "Dog", "Horse" }'::text[])[0];

Result:

 text 
------
 Cat

In this example I created a zero based array. In other words, the subscripts start at zero.

Therefore, in order to get the first item, we need to use 0 instead of 1. That’s exactly what we did here. We used [0] to return the first item in the array, which was Cat.

We can use this approach when inserting data into a table:

INSERT INTO array_test VALUES 
    (
        '[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} }'
    );

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 see that the arrays are inserted along with their respective subscript assignments.

We can reference the array elements by using the subscript based on the specified assignment. For example:

SELECT * FROM array_test
WHERE c2[-3] = 'Badger';

Result:

      c1       |                     c2                      |                       c3                        
---------------+---------------------------------------------+-------------------------------------------------
 [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}}

In this case Badger is at -3, and that’s what I specified in my query. If this was a normal one-based array, then we would have needed to specify 3 instead of -3.

Push a Value to an Array

We can push new values to an array by concatenating the value with the array:

SELECT ARRAY[1,2,3] || ARRAY[4,5,6];

Result:

{1,2,3,4,5,6}

Here’s an example that prepends and appends a value to an array from the database:

\x
SELECT 
    c2 AS Original,
    c2 || ARRAY['Horse'] AS Appended,
    ARRAY['Horse'] || c2 AS Prepended
FROM array_test
WHERE c1 = '{ 1,2,3 }';

Result (using vertical output):

original  | {Rabbit,Cat,Dog,Monkey,Moth}
appended  | {Rabbit,Cat,Dog,Monkey,Moth,Horse}
prepended | {Horse,Rabbit,Cat,Dog,Monkey,Moth}

For this example I used psql‘s \x meta command to change the results to expanded display to make the results easier to read without scrolling.

We can also use the array_prepend(), array_append(), and array_cat() functions to prepend and append values to arrays:

SELECT 
    c2 AS Original,
    array_append(c2, 'Horse') AS Appended,
    array_prepend('Horse', c2) AS Prepended,
    array_cat(c2, ARRAY['Horse']) AS "Appended with array_cat",
    array_cat(ARRAY['Horse'], c2) AS "Prepended with array_cat"
FROM array_test
WHERE c1 = '{ 1,2,3 }';

Result (using vertical output):

original                 | {Rabbit,Cat,Dog,Monkey,Moth}
appended                 | {Rabbit,Cat,Dog,Monkey,Moth,Horse}
prepended                | {Horse,Rabbit,Cat,Dog,Monkey,Moth}
Appended with array_cat  | {Rabbit,Cat,Dog,Monkey,Moth,Horse}
Prepended with array_cat | {Horse,Rabbit,Cat,Dog,Monkey,Moth}

The array_cat() function can be used with multi dimensional arrays, but the other two can’t.

Here’s an example:

SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);

Result:

{{1,2},{3,4},{5,6}}

And here’s one using an array from the database:

SELECT 
    c3,
    array_cat( c3, ARRAY[10,11,12] )
FROM array_test
WHERE c1 = '{1,2,3}';

Result:

             c3             |               array_cat               
----------------------------+---------------------------------------
 {{1,2,3},{4,5,66},{7,8,9}} | {{1,2,3},{4,5,66},{7,8,9},{10,11,12}}