Menu
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.
657
The equality operators ( = , <> ) do an exact element-by-element comparison. The ordering operators ( > , < , >= , <= ) also compare each element in an array in order. Results are based on the first different pair of elements, not the sizes of the arrays.
Unnest function generates a table structure of an array in PostgreSQL. Unnest array function is beneficial in PostgreSQL for expanding the array into the set of values or converting the array into the structure of the rows. PostgreSQL offers unnest() function.
array_to_string() function This function is used to concatenate array elements using supplied delimiter and optional null string.
You can use &&, the array overlap operator:
select *
from foo
where tags && ARRAY['apples', 'bananas', 'cherries']
From the documentation:
&&: overlap (have elements in common)
181
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
