Pattern match against array elements?
От | Israel Brewster |
---|---|
Тема | Pattern match against array elements? |
Дата | |
Msg-id | 954B5C3A-EED9-4157-85C2-B907F12476FC@ravnalaska.net обсуждение исходный текст |
Ответы |
Re: Pattern match against array elements?
Re: Pattern match against array elements? |
Список | pgsql-general |
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 toselect all logs that have a flight number starting with an '8' (so '800' or '8000' series flights). My first thought wasto 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 wildcardis 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 thatstarts with an 8 (or whatever)? ----------------------------------------------- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 -----------------------------------------------
В списке pgsql-general по дате отправления: