Re: Pattern match against array elements?

On Mon, Oct 12, 2015 at 10:58 AM, Israel Brewster <israel(at)ravnalaska(dot)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