Is there a way to search partial match in multivalue field in PostgreSQL?

Question

I have a table quiet like this:

CREATE TABLE myTable (
    family text,
    names text[]
)

I can search like this:

SELECT family
FROM myTable where names @> array['B0WP04'];

But I would like to do:

SELECT family
FROM myTable where names @> array['%P0%'];

Is this possible ?

Answer 1

Adding a bit on Radek's answer, I tried

select family
from myTable where 
exists (select 1 from unnest(names) as name where name like '%P0%');

and it also works. I searched in the PostgreSQL docs for the un() function, but can't find anything.

I'm not saying it doesn't do anything, but I'm just curious about what the un() function should do (and happy to have my problem solved)

Answer 2

In postgreSQL 9.3 you can:

select family
from myTable 
join lateral unnest(mytable.names) as un(name) on true
where un.name like '%P0%';

But keep in mind that it can produce duplicates so perhaphs you'd like to add distinct.

For earlier versions:

select family
from myTable where 
exists (select 1 from unnest(names) as un(name) where un.name like '%P0%');