How to use in operator on text[] column data type?

user_4137368 0
Viewed 933

I'm trying to use IN operator in a text[] column. But I got this error message:

ERROR: operator does not exist: text = text[] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Is it possible to use IN operator in this context? If not what should I use?

I've tried casting into json/jsonb, use ANY, or @> operator without success

Reproducible example:

CREATE TABLE visitor (name varchar(255), hobbies text[]);
CREATE TABLE hobbies(name varchar(255), id text);

INSERT INTO visitor (name, hobbies) VALUES ('sanghin', '{ida}');
INSERT INTO hobbies (name, id) VALUES ('sql', 'ida');

SELECT name FROM hobbies WHERE id IN (SELECT hobbies FROM visitor WHERE visitor.name = 'sanghin');
postgresql
2 Answers

demo:db<>fiddle

SELECT name 
FROM hobbies 
WHERE id IN 
     (SELECT unnest(hobbies) FROM visitor WHERE visitor.name = 'sanghin');

Your subquery returns a set of arrays. Then you are trying to compare a text with elements of the set. So your are trying to compare type text with type text[] which wouldn't work. Unnesting the array gives you a set of text values. The comparison works.

user_39842210

I would use an EXISTS predicate:

select h.name 
from hobbies h
where exists (select *
              from visitor v
              where v.name = 'sanghin'
                and h.id = any(v.hobbies));

Online example

user_3303150
Related