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. 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)? > > Are you looking for this ? SELECT lognum array_agg(flightnum) FROM logs WHERE flightnum ~ '^8' GROUP BY lognum; > ----------------------------------------------- > 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Regards, Dinesh manojadinesh.blogspot.com
