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):

SELECT a.id, 
       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.

  • 1
    It would be much easier to answer if you showed some data :) However, you can possibly use unnest() to get a set from your array. Commented Oct 12, 2015 at 13:31
  • what do you mean with data? how I create my view or actual table data? Commented Oct 12, 2015 at 14:27

2 Answers 2


You can use unnest() like dezso commented, for instance with a LATERAL join:

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 o
   WHERE  o.article = a.oa_nr            -- LATERAL reference  
   AND    id::text ILIKE '1234%' COLLATE "C"  -- or just LIKE
   ) o ON offer_list IS NOT NULL;

Returns a single row from article_list with an array offer_list of matching IDs in task_offer (if any) - an array of the original data type.

Add an index using COLLATE "C", so it can be used for left-anchored LIKE patterns:

CREATE INDEX task_offer_foo_idx ON task_offer (article, (id::text) COLLATE "C");

In older versions of Postgres, the operator class text_pattern_ops served the same purpose. See:

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

Or maybe use 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%' COLLATE "C"  -- or just LIKE
   GROUP  BY 1
   ) o ON o.article = a.oa_nr;

Needs an index on article_list.oa_nr, too. (Which you probably have.) Like:

CREATE INDEX article_list_oa_nr_idx ON article_list (oa_nr);
  • I added some data oh and I would need to return the IDs of the Joined table aswell Commented Oct 12, 2015 at 14:29
  • 1
    Only IDs matching the pattern or all linked IDs? Please update the question to clarify. Commented Oct 12, 2015 at 20:42
  • I added an answer for just matching IDs. Commented Oct 13, 2015 at 4:15
  • wow thanks. however offer_list could also be empty, is that a problem at all, I mean how to write the ON part if offer_list is empty nvm seems to be the same as before..?? Also the index does not work since task_offer does not contains oa_nr, i think you meant article, right? Commented Oct 13, 2015 at 7:35
  • Also I think 10ms (raising if my table raises is really slow..., considering that this is yet not a problem but currently one table contains 30k rows and the other 10k rows and we produce ~5k rows on the left and ~5k on the right each year we will be really really slow at a certain point, however I could use the query to generate a materialized view..) Commented Oct 13, 2015 at 7:48

Have you considered the parray_gin extension, also located here?

I haven't used it in production yet, but from what I have seen of it it does what you want against the array. You can create the relevant index on the materialized view.

However, I would only do that if you are going to have the materialized view anyway. Otherwise I think @ErwinBrandstetter's answer is better than creating a materialized view only to hold the index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.