PostgreSQL LIKE query on ARRAY field

Question

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.

• András Váczi: 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.
• Christian Schmitt: what do you mean with data? how I create my view or actual table data?

Answer 1

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

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

• Is there a difference between text_pattern_ops and COLLATE "C"?

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

• How is LIKE implemented?
• Pattern matching with LIKE, SIMILAR TO or regular expressions

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

• Christian Schmitt: I added some data oh and I would need to return the IDs of the Joined table aswell
• Erwin Brandstetter: Only IDs matching the pattern or all linked IDs? Please update the question to clarify.
• Erwin Brandstetter: I added an answer for just matching IDs.
• Christian Schmitt: 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?
• Christian Schmitt: 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..)

Answer 2

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.