postgresql ARRAY ALLOF and ANYOF conditions

Question

I have the following Postgresql records:

CREATE TABLE People (Name TEXT, Languages TEXT[]);
INSERT INTO People Values ('BillG', ARRAY['English', 'French', 'Tamil']);
INSERT INTO People Values ('SteveJ', ARRAY['English', 'Spanish']);
INSERT INTO People Values ('WarrenB', ARRAY['English', 'French', 'Spanish']);

SELECT Name FROM People WHERE Languages @> ARRAY['English', 'French'];

Now the SELECT statement returns 'BillG' and 'WarrenB' as these two are the only ones who speak both 'English' and 'French'. However, if I want to return all the people, who speak either of the two languages (and not necessarily both), how do I achieve that ?

I tried:

SELECT Name FROM People WHERE ARRAY['English', 'French'] @> Languages;

but that returns zero records instead of all the records that I wanted. I seem to vaguely understand why the second SELECT query may fail, but I am not able to understand how to get all Persons who can speak any of the languages in the array that I give. Any help ?

Answer 1

Use the && (overlaps) operator instead of the @> (contains) operator:

SELECT Name 
FROM People 
WHERE Languages && ARRAY['English', 'French'];
┌─────────┐
│  name   │
├─────────┤
│ BillG   │
│ SteveJ  │
│ WarrenB │
└─────────┘
(3 rows)