3

I have a table with a field containing an array of strings (type is character varying(255)[]).

I'd like to compare a given string with a wildcard, say 'query%', to any of the elements of this field.

This request works and gets back the expected results: 

SELECT * FROM my_table WHERE 'query' ILIKE ANY(my_field)

But with the wildcard, I got no results: 

SELECT * FROM my_table WHERE 'query%' ILIKE ANY(my_field)

I think the reason is that the wildcard is supported only at the right side of the ILIKE operator, but ANY(my_field) also has to be after the operator.

Is there a way to achieve what I want?

Using PostgreSQL 9.5.

scandel
  • 1,692
  • 3
  • 20
  • 39
  • Possible duplicate of [PostgreSQL - text Array contains value similar to](http://stackoverflow.com/questions/34657669/postgresql-text-array-contains-value-similar-to) – pozs Oct 28 '16 at 13:31
  • You're right, this is a duplicate, I hadn't seen it. But I'm glad @klin wrote a detailed answer, which wasn't in the other post. – scandel Oct 28 '16 at 13:59

2 Answers2

3

You have to unnest the array field:

with my_table(my_field) as (
values
    (array['query medium', 'large query']),
    (array['large query', 'small query'])
)
select t.* 
from my_table t,
lateral unnest(my_field) elem
where elem ilike 'query%';

            my_field            
--------------------------------
 {"query medium","large query"}
(1 row)
klin
  • 112,967
  • 15
  • 204
  • 232
  • Thanks, that's the perfect solution. I just had to add a DISTINCT (select distinct t.*), because there were some rows in my table where 'query%' appeared several times in my_field. – scandel Oct 28 '16 at 13:40
2

Convert the array into a set with unnest() and use an EXIST clause

SELECT * FROM my_table t WHERE  EXISTS (SELECT unnest(t.my_field) AS f WHERE f ILIKE ‘query%’)
Serg M Ten
  • 5,568
  • 4
  • 25
  • 48
  • 1
    Thanks, that's also a good solution because everything stays in the where clause, it may be useful for more complicated requests. But your request raises a postgresql error, I had to modiify it to make it work: `SELECT * FROM my_table t WHERE EXISTS (SELECT f FROM unnest(t.my_field) AS f WHERE f ILIKE 'query%')` – scandel Oct 28 '16 at 14:04