If you’re getting a PostgreSQL error that reads “op ANY/ALL (array) requires array on right side” it’s probably because you’re passing a non array to the ANY() or ALL() construct.

These constructs require an array to be included in the parentheses, and so passing a non array returns an error.

To fix this issue, be sure to pass an array when using these functions.

Example of Error

Here’s an example of code that produces the error:

SELECT 'Zebra' = ANY(1);

Result:

ERROR:  op ANY/ALL (array) requires array on right side
LINE 1: SELECT 'Zebra' = ANY(1);
                       ^

As the message alludes to, I passed a non array when an array was required.

Same deal if we replace ANY() with ALL():

SELECT 'Zebra' = ALL(1);

Result:

ERROR:  op ANY/ALL (array) requires array on right side
LINE 1: SELECT 'Zebra' = ALL(1);
                       ^

And here’s a database example:

SELECT c1, c2 FROM t1 
WHERE 'Zebra' = ANY(c2);

Result:

ERROR:  op ANY/ALL (array) requires array on right side
LINE 2: WHERE 'Zebra' = ANY(c2);
                      ^

In this case I passed a column called c2, which is a text column (i.e. not an array).

Let’s check the data types of the columns:

\d t1

Result:

 Column |   Type    | Collation | Nullable | Default 
--------+-----------+-----------+----------+---------
 c1     | integer   |           |          | 
 c2     | text      |           |          | 
 c3     | text[]    |           |          | 
 c4     | integer[] |           |          | 

As suspected, c2 is a text column and not an array.

Solution

To fix this issue, we need to pass an array to the ANY() or ALL() constructs.

SELECT 'Zebra' = ANY( '{Zebra}' );

Result:

True

In this case I passed an array and the function worked without error.

In our database example, we can pass the c3 column (which is an array column):

SELECT c1, c2 FROM t1 
WHERE 'Zebra' = ANY(c3);

Result:

 c1 |   c2    
----+---------
  1 | Animals
  3 | Pets

This time we got a result without error.