Postgres Pro
Downloads
Home   >   mailing lists

Re: WHERE 'Something%' LIKE ANY (array_field) - Mailing list pgsql-general

From Tim Kane
Subject Re: WHERE 'Something%' LIKE ANY (array_field)
Date
Msg-id CADVWZZJ_=rM017iE63ETc1kCLCZGmC_mNhEoDfaJk-bTmB3ZSg@mail.gmail.com
Whole thread Raw
In response to Re: WHERE 'Something%' LIKE ANY (array_field)  (Pavel Stehule <pavel.stehule@gmail.com>)
Responses Re: WHERE 'Something%' LIKE ANY (array_field)  (Kevin Grittner <kgrittn@ymail.com>)
List pgsql-general
Tree view

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:


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 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)


pgsql-general by date:

Previous
From: coutinhoviola@gmail.com
Date:
Subject: Re: Please help
Next
From: Richard Hipp
Date:
Subject: Seemingly inconsistent ORDER BY behavior