In PostgreSQL we can use the ARRAY constructor to create an array. When we do this, we provide the array elements as a comma separated list, enclosed in square brackets. Postgres then works out the data type based on the array elements.

But what if we want to create an empty array?

Creating an empty array can cause issues if we don’t explicitly specify the type. We need to specify the type. Below is an example of creating an empty array in PostgreSQL using the ARRAY constructor.

Example

Here’s an example of creating an empty array:

SELECT ARRAY[]::integer[];

Result:

 array 
-------
 {}

The important thing here is to explicitly cast the array to the desired type. In this case I cast it to an integer type.

By default, when we create an array that contains elements, the array element type is the common type of the member expressions, determined using the same rules as for UNION or CASE constructs. But when we don’t provide any elements, there’s no way for PostgreSQL to know what type the array should be. And arrays must have a type.

When We Don’t Specify the Type

Here’s what happens when we don’t specify the type:

SELECT ARRAY[];

Result:

ERROR:  cannot determine type of empty array
LINE 1: SELECT ARRAY[];
               ^
HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[].

PostgreSQL returns an error telling us that it can’t determine the array’s type.