From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
Cc: | Israel Brewster <israel(at)ravnalaska(dot)net>, "pgsql-general(at)postgresql(dot)org general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Pattern match against array elements? |
Date: | 2015-10-12 18:39:38 |
Message-ID: | 8171.1444675178@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Mon, Oct 12, 2015 at 10:58 AM, Israel Brewster <israel(at)ravnalaska(dot)net>
> wrote:
>> My first thought was to do something like this:
>>
>> SELECT * FROM (SELECT lognum,array_agg(flightnum) as flightnums FROM logs
>> GROUP BY lognum) s1 WHERE '8%' like ANY(flightnums);
>>
>> But while this doesn't give an error, it also doesn't return any results.
>> I'm guessing that this is because the wildcard is on the left of the
>> operator, and needs to be on the right.
> Right. The LIKE operator does not have a commutator by default. (And if
> you created one for it, it could not use an index in this case.)
Well, it couldn't use an index anyway, given that the query as written
wants to collect groups if *any* member is LIKE '8%', rather than
restricting the data to such flightnums before aggregation occurs.
Personally I'd suggest building a commutator operator (just need a
one-liner SQL or plpgsql function as infrastructure) and away you go.
> I think you're best bet is to do a subquery against the unaggregated table.
> select * from aggregated a where exists
> (select 1 from unaggregated ua where a.lognum=ua.lognum and flightnum
> like '8%')
That would work too, but not sure about performance relative to the other
way.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2015-10-12 18:51:38 | Re: Constraint allowing value up to 2 times but no more than 2 times |
Previous Message | Jeff Janes | 2015-10-12 18:18:07 | Re: Pattern match against array elements? |