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!

where '2' = any(field_name)

Mike Organek
– Mike Organek

2020-08-14 16:53:27 +00:00
Commented Aug 14, 2020 at 16:53 — Mike Organek
– Mike Organek, Commented Aug 14, 2020 at 16:53

jjanes · Accepted Answer · 2020-08-14 19:27:28Z

5

=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)

answered Aug 14, 2020 at 19:27

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Check if array has value

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related