Check if array has value

Question

I'm running this simple check:

select * from mytable
where field_name = any(array['2']::_varchar);

field_name is _varcharso it's an array

but I'm getting this: ERROR: operator does not exist: character varying[] = character varying

What am I missing?

Thanks!

Answer 1

=ANY unwraps it RHS and compares them individually to the LHS, so it would be the same thing as field_name = '2'::varchar. You can't compare an array to a scalar like that. You want an operator that doesn't unwrapped the argument but compares arrays to each other:

field_name @> array['2']::_varchar

or

field_name && array['2']::_varchar

Or you want to leave the literal as a scalar, and then unwrap the other side which is already an array so it too becomes a scalar:

'2' =ANY (field_name)