In PostgreSQL, an array constructor is an expression that builds an array value using values for its member elements. 

One of the things we can do is build an array based on the results of a subquery.

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 create an array from this.

We could create an array that contains all pet names:

SELECT ARRAY(
    SELECT PetName FROM Pets
    );

Result:

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

We could narrow it down to just cats or dogs:

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

Result:

      array       
------------------
 {Bark,Wag,Fetch}

Parentheses Instead of Square Brackets

One thing to be mindful of when constructing an array from a subquery is that we must use enclose the subquery inside parentheses, as opposed to square brackets, which is what we normally use when using the ARRAY constructor.

Here’s what happens if we use square brackets:

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

Result:

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

But as alluded to, square brackets is what we’d normally use if we weren’t using a subquery to construct the array. For example:

SELECT ARRAY[
    'Fluffy','Scratch','Purr','Bark','Wag','Fetch'
    ];

Result:

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

Only a Single Column is Allowed

Also, the subquery must return only one column. If it returns more than one column we get an error:

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

Result:

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

Create a Multi-Dimensional Array

We can create a multi-dimensional array from multiple subqueries:

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

Although 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.