If you’re getting an error that reads “cannot determine type of empty array” in PostgreSQL, it could be that you’re trying to create an empty array without specifying the array type.
It’s impossible to create an array with no type, and so if we want to create an empty array, then we need to specify the type or add items to determine the type.
To fix this issue, either specify the array type for the empty array, or add items to the array.
Example of Error
Here’s an example of code that produces the error:
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. That’s because we didn’t specify the array type or add items that would effect this for us.
Solution 1
If we truely want to create an empty array then we’ll need to explicitly specify the array type (as the error message suggests).
Example:
SELECT ARRAY[]::integer[];
Result:
array
-------
{}
Solution 2
Another way of dealing with the issue is to add items to the array. By default, the array element type is the common type of the member expressions, determined using the same rules as for UNION
or CASE
constructs.
Example:
SELECT ARRAY[1,2,3];
Result:
array
---------
{1,2,3}
In this example I added integers to the array and so this was enough for PostgreSQL to work out the array type.