PostgreSQL check if array contains any value from list

Question

I'm trying to use PostgreSQL to check if any values from a certain list are in any of the arrays stored in a database.

Something like:

SELECT * FROM table_name WHERE ('value1', 'value2', 'value3') = ANY(field_name);

field_name is a VARCHAR(100)[]

Is this even possible?

score 4 · Accepted Answer · 2020-03-09 14:59:12Z

4

As field_name is an array, you can use the overlaps operator &&:

SELECT * 
FROM table_name 
WHERE field_name && array['value1', 'value2', 'value3']::varchar[]

answered Mar 9, 2020 at 14:54

user330315

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

3 Comments

Seems like VARCHAR and text are incompatible to compare, but this is definitely a step in the right direction!

@pteixeira: then cast the array to varchar[]

Got to it eventually; waiting on timer to accept answer. Thank you!