On Mon, Oct 12, 2015 at 10:58 AM, Israel Brewster <isr...@ravnalaska.net> wrote:
> Is there any way to do a pattern match against the elements of an array in > postgresql (9.4 if the version makes a difference)? I have a grouped query > that, among other things, returns an array of values, like: > > SELECT lognum, array_agg(flightnum) as flightnums FROM logs GROUP BY > lognum; > > Where the flightnum field is a varchar containing either a text string or > a three-or-four digit number. Now say I want to select all logs that have a > flight number starting with an '8' (so '800' or '8000' series flights). 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.) > Of course, turning it around to be: > > WHERE ANY(flightnum) like '8%' > > gives me a syntax error. So is there any way I can run this query such > that I get any rows containing a flight number that starts with an 8 (or > whatever)? > 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%') This is a common problem. If you find a better solution, I'd love to hear it! Cheers, Jeff
