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

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hello

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

Regards

Pavel Stehule

2013/8/14 Tim Kane <tim.kane@xxxxxxxxx>

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)