Using the array result of a select statement returns ERROR: pq: operator does not exist: integer = integer[]

Question

The query in question is the following:

SELECT * FROM options WHERE id = any(SELECT option_ids FROM workshops WHERE id=3)

ERROR: pq: operator does not exist: integer = integer[]

Basically the statement inside any() returns an array of ids which I want to use to get some rows on the options table.

I don't understand why is it trying to compare an integer to the whole array, isnt any supposed to compare a single int to multiple integers in an array?

Answer 1

You can unnest the array in order to create a valid statement, i.e.

SELECT * FROM options WHERE id = any(SELECT unnest(option_ids) FROM workshops WHERE id=3);

Otherwise, if id is an integer, then the subquery inside of the any clause would need to return a column of integers (rather than a column of integer arrays) in order for the statement to be valid.