On Oct 12, 2015, at 10:39 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > Jeff Janes <jeff.janes@xxxxxxxxx> writes: >> On Mon, Oct 12, 2015 at 10:58 AM, Israel Brewster <israel@xxxxxxxxxxxxxx> >> 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. That could work. I'll look into that. > >> 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 ----------------------------------------------- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 ----------------------------------------------- -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
