Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl (
  id    serial PRIMARY KEY
, value text[]  -- subtly better than varchar[] !
);

INSERT INTO tbl (value) VALUES 
  ('{friend, apple, cat}')
, ('{cat, friend, dog}')
, ('{pasta, best-friend, lizard}')
, ('{wildcat, potato, alices-friend}')
, ('{friend1, friend2, no_tigers}')     -- ! added value to highlight corner case
;

CREATE TABLE



INSERT 0 5


-- simple equality match
SELECT id, value  -- no DISTINCT needed
FROM   tbl
WHERE  value @> '{friend, cat}';


id
value


1
{friend,apple,cat}

2
{cat,friend,dog}



SELECT 2


-- match with LIKE patterns
SELECT t.id, t.value
FROM   tbl t
WHERE  NOT EXISTS (
   SELECT FROM unnest('{%friend%, %cat%}'::text[]) AS p(pattern)
   WHERE  NOT EXISTS (
      SELECT FROM unnest(t.value) AS a(elem)
      WHERE  a.elem LIKE p.pattern
      )
   );


id
value


1
{friend,apple,cat}

2
{cat,friend,dog}

4
{wildcat,potato,alices-friend}



SELECT 3


-- sticky bit's query fails:
SELECT s.id,
       s.value
       FROM tbl AS s
            CROSS JOIN LATERAL unnest(value) AS un(v)
       WHERE un.v ~ 'friend|cat'
       GROUP BY s.id
       HAVING count(*) >= 2;


id
value


2
{cat,friend,dog}

5
{friend1,friend2,no_tigers}

4
{wildcat,potato,alices-friend}

1
{friend,apple,cat}



SELECT 4

id	value
1	{friend,apple,cat}
2	{cat,friend,dog}
4	{wildcat,potato,alices-friend}

id	value
2	{cat,friend,dog}
5	{friend1,friend2,no_tigers}
4	{wildcat,potato,alices-friend}
1	{friend,apple,cat}