Postgresql IN error: operator does not exist integer = integer[]

Question

This WHERE ANY query in Nestjs / Express works fine:

const sql = 'SELECT * FROM members WHERE '+ integerId + ' = ANY(skill_id_array)';
const membersBySkill = await this.entityManager.query(sql

);

This WHERE IN query fails with this post's title error message:

const sql = 'SELECT * FROM members WHERE '+ integerId + ' IN (skill_id_array)';
const membersBySkill = await this.entityManager.query(sql);

My understanding of SQL is IN and ANY should be almost the same queries with almost the same results.

integerId is a var integer such as 2.
skill_id_array in Postgres looks like this: {1,2,5}

Why is this error happening? I'm searching with an integer in a column of integer arrays.

Answer 1

In short: in() needs a list of scalars, while any() needs an array expression.

Per the documentation:

expression IN (value [, ...])

The right-hand side is a parenthesized list of scalar expressions.

Example:

where integerid in (1, 2, 5)

expression operator ANY (array expression)

The right-hand side is a parenthesized expression, which must yield an array value.

Example:

where integerid = any ('{1, 2, 5}');

Because

skill_id_array in Postgres looks like this: {1,2,5}

so when passed to the query the expression is enclosed in single quotes and you can use it with any(). If you do the same with in() you'll obviously get invalid syntax:

where integerid in ('{1, 2, 5}');

ERROR:  invalid input syntax for integer: "{1, 2, 5}"   

or, if your ORM uses automatic casting:

where integerid in ('{1, 2, 5}'::int[]);

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