Обсуждение: WHERE 'Something%' LIKE ANY (array_field)
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)
Pavel Stehule
2013/8/14 Tim Kane <tim.kane@gmail.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 TABLETime: 185.516 ms=# insert into ids values ('{s1,s452334,s89}');INSERT 0 1Time: 0.728 ms=# insert into ids values ('{s89}');INSERT 0 1Time: 0.300 ms=# insert into ids values ('{s9323,s893}');INSERT 0 1Time: 0.133 ms=# insert into ids values ('{s9323,s893,s89}');INSERT 0 1Time: 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 msclone=# select * from ids where 's45%' LIKE ANY (id);id----(0 rows)Time: 0.124 msclone=# select * from ids where 's452334%' LIKE ANY (id);id----(0 rows)Time: 0.278 msclone=# select * from ids where 's452334' LIKE ANY (id);id------------------{s1,s452334,s89}(1 row)Time: 0.134 msclone=# select * from ids where 's452334' = ANY (id);id------------------{s1,s452334,s89}(1 row)
Thanks Pavel,
Unless I'm being bleary eyed and not quite grasping it... I'm not sure that answers my question.
I'm using a single LIKE clause against an array parameter, rather than multiple LIKE clauses against a single parameter.
It seems I'm so far stuck with a FOREACH style traversal within plpgsql (which is fine, as this is all to be used within a function anyway).
On Wed, Aug 14, 2013 at 10:55 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Pavel Stehule2013/8/14 Tim Kane <tim.kane@gmail.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 TABLETime: 185.516 ms=# insert into ids values ('{s1,s452334,s89}');INSERT 0 1Time: 0.728 ms=# insert into ids values ('{s89}');INSERT 0 1Time: 0.300 ms=# insert into ids values ('{s9323,s893}');INSERT 0 1Time: 0.133 ms=# insert into ids values ('{s9323,s893,s89}');INSERT 0 1Time: 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 msclone=# select * from ids where 's45%' LIKE ANY (id);id----(0 rows)Time: 0.124 msclone=# select * from ids where 's452334%' LIKE ANY (id);id----(0 rows)Time: 0.278 msclone=# select * from ids where 's452334' LIKE ANY (id);id------------------{s1,s452334,s89}(1 row)Time: 0.134 msclone=# select * from ids where 's452334' = ANY (id);id------------------{s1,s452334,s89}(1 row)
Tim Kane <tim.kane@gmail.com> wrote: > I'm using a single LIKE clause against an array parameter, rather > than multiple LIKE clauses against a single parameter. The problem is that you have the arguments to the LIKE parameter reversed with the attempts you show. Try something like this: select * from ids where exists (select * from (select unnest(ids.id)) x(id) where x.id like 's45%'); -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Thank it is working in my senario -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html