Sample Header Ad - 728x90

PostgreSQL LIKE query on ARRAY field

21 votes
2 answers
38398 views
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.
Asked by Christian Schmitt (453 rep)
Oct 12, 2015, 01:16 PM
Last activity: Aug 1, 2023, 06:06 PM