Postgres WHERE two arrays have a non-empty intersection

Question

I have a table with a column tags of type varchar [].

I would like to select all rows where the tags contains at least one of a set of values.

Something like this:

-- Not real code
SELECT * 
FROM foo 
WHERE non_empty(set_intersection(tags, '{ "apples", "bananas", "cherries" }'))

What is the syntax for this?

I know I can do a series of ORs but that seems less elegant.

GMB · Accepted Answer · 2020-03-10 10:29:44Z

17

You can use &&, the array overlap operator:

select *
from foo
where tags && ARRAY['apples', 'bananas', 'cherries']

&&: overlap (have elements in common)

answered Mar 10, 2020 at 10:29

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Answer 1