In PostgreSQL, we can use array constructors to create arrays that contain elements that we specify. We can populate the array from literal values, or we can let a query provide the values.

We can also create multi-dimensional arrays. When populating the array from a query, we can use multiple subqueries to create each sub-array. Therefore we can create a multi-dimensional array from a query.

Example

Suppose we have the following table:

SELECT * FROM Pets;

Result:

 petid | petname | pettype 
-------+---------+---------
     1 | Fluffy  | Cat
     2 | Scratch | Cat
     3 | Purr    | Cat
     4 | Bark    | Dog
     5 | Wag     | Dog
     6 | Fetch   | Dog

We can use this data to create a multi-dimensional array:

SELECT ARRAY[
        ARRAY(SELECT PetName FROM Pets WHERE PetType = 'Dog'),
        ARRAY(SELECT PetName FROM Pets WHERE PetType = 'Cat')
        ];

Result:

                  array                   
------------------------------------------
 {{Bark,Wag,Fetch},{Fluffy,Scratch,Purr}}

In this case we separated the inner arrays by pet type; one array contains cats and the other dogs.

One thing to keep in mind that we must ensure that each sub-array contains the same number of dimensions. If they don’t we’ll get an error. This is because multidimensional arrays must be rectangular.

So if we’d had say, three cats and four dogs we would have received an error.

Parentheses for the Inner Arrays

Another thing to remember is that we need to use parentheses for the array constructors that contain subqueries. We can see from the above example that the outer ARRAY constructor uses square brackets, but the inner constructors use parentheses.

Here’s what happens if we use square brackets on the inner array constructors:

SELECT ARRAY[
        ARRAY[SELECT PetName FROM Pets WHERE PetType = 'Dog'],
        ARRAY[SELECT PetName FROM Pets WHERE PetType = 'Cat']
        ];

Result:

ERROR:  syntax error at or near "SELECT"
LINE 2:         ARRAY[SELECT PetName FROM Pets WHERE PetType = 'Dog'...
                      ^

We get an error.

It’s the same if we replace the square brackets of the outer constructor with parentheses:

SELECT ARRAY(
        ARRAY(SELECT PetName FROM Pets WHERE PetType = 'Dog'),
        ARRAY(SELECT PetName FROM Pets WHERE PetType = 'Cat')
        );

Result:

ERROR:  syntax error at or near "ARRAY"
LINE 2:         ARRAY(SELECT PetName FROM Pets WHERE PetType = 'Dog'...
                ^

Regardless, any arrays produced by the (correct) statement are enclosed in curly brackets, as can be seen in the initial example.