Re: WHERE 'Something%' LIKE ANY (array_field)

Hello

http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#LIKE_to_list_of_patterns

Regards

Pavel Stehule

2013/8/14 Tim Kane <tim(dot)kane(at)gmail(dot)com>

> Hi all,
>
> It seems like it isn't possible to perform a wildcard LIKE evaluation
> against array objects. Is this a bug, or just unsupported?
>
>
> See the queries in bold, that I would have expected to return a value.
> Postgres 9.1.9
>
>
> =# create temp table ids (id varchar(12)[]);
> CREATE TABLE
> Time: 185.516 ms
> =# insert into ids values ('{s1,s452334,s89}');
> INSERT 0 1
> Time: 0.728 ms
> =# insert into ids values ('{s89}');
> INSERT 0 1
> Time: 0.300 ms
> =# insert into ids values ('{s9323,s893}');
> INSERT 0 1
> Time: 0.133 ms
> =# insert into ids values ('{s9323,s893,s89}');
> INSERT 0 1
> Time: 0.110 ms
> =# select * from ids;
> id
> ------------------
> {s1,s452334,s89}
> {s89}
> {s9323,s893}
> {s9323,s893,s89}
> (4 rows)
>
> Time: 0.155 ms
> =# select * from ids where 's89' = ANY (id);
> id
> ------------------
> {s1,s452334,s89}
> {s89}
> {s9323,s893,s89}
> (3 rows)
>
> Time: 0.121 ms
> *clone=# select * from ids where 's45%' LIKE ANY (id);*
> id
> ----
> (0 rows)
>
> Time: 0.124 ms
>
> *clone=# select * from ids where 's452334%' LIKE ANY (id);*
> id
> ----
> (0 rows)
>
> Time: 0.278 ms
> clone=# select * from ids where 's452334' LIKE ANY (id);
> id
> ------------------
> {s1,s452334,s89}
> (1 row)
>
> Time: 0.134 ms
> clone=# select * from ids where 's452334' = ANY (id);
> id
> ------------------
> {s1,s452334,s89}
> (1 row)
>