PostgreSQL LIKE query on ARRAY field Is there any way to have a Postgres LIKE query on a ARRAY field?

Currently I want something like that:

SELECT * FROM list WHERE lower(array_field) LIKE '1234%'


Currently lower is not needed that much. However it should find ONE matching field inside the ARRAY. Is that even possible? 

Currently I use a materialized view to generate the "list" table with a JOIN and a ARRAY_AGG(), since I JOIN a table where more values could be on the right table. Which would duplicate fields on the left table, which is not what I want.

Edit this is how I create the view (really sluggish and ugly):

CREATE MATERIALIZED VIEW article_list_new AS
SELECT a.id, 
       a.oa_nr, 
       a.date_deleted, 
       a.lock, 
       a.sds_nr, 
       a.kd_art_nr, 
       a.kd_art_index, 
       a.kd_art_extend, 
       a.surface, 
       a.execution, 
       a.surface_area, 
       a.cu_thickness, 
       a.endintensity, 
       a.drilling, 
       array_agg(o.id::text) AS offer_list 
FROM article_list a LEFT JOIN task_offer o ON o.article = a.oa_nr 
GROUP BY .....;  


I also need to return the IDs of the task_offer table.
You can use unnest() like @dezso already mentioned, for instance with a LATERAL join (Postgres 9.3+)

SELECT l.*
FROM   list l, unnest(array_field) a  -- implicit lateral
WHERE  lower(a) LIKE '1234%';


You don't need any of this for the presented case. No materialized view at all. This query on the underlying tables is faster, because it can use an index:

SELECT *  -- or selected columns
FROM   article_list a
JOIN   LATERAL  (                      -- only matching IDs
   SELECT array_agg(id) AS offer_list
   FROM   task_offer
   WHERE  article = a.oa_nr            -- LATERAL reference  
   AND    id::text ILIKE '1234%'       -- or just LIKE
   ) o ON offer_list IS NOT NULL;


offer_list is an array of the original data type in my result, and contains only matching IDs.

Returns a single row from article_list with an array of matching IDs in task_offer. Add a text_pattern_ops index (for left-anchored matches) to get best read performance:

CREATE INDEX task_offer_foo_idx ON task_offer (article, (id::text) text_pattern_ops);



Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL


Or a trigram index for infix matches (not left-anchored):


How is LIKE implemented?


Or maybe a plain join for selective patterns:

SELECT a.*, o.offer_list -- or selected columns
FROM   article_list a
JOIN   (                          -- only matching IDs
   SELECT article, array_agg(id) AS offer_list
   FROM   task_offer
   WHERE  id::text ILIKE '1234%'  -- or just LIKE
   GROUP  BY 1
   ) o ON o.article = a.oa_nr;


Needs an index on article_list, too. (Which you probably have - no information in the question.) Like:

CREATE INDEX article_list_oa_nr_idx ON article_list (oa_nr);