Postgresql: Pattern match against array elements?

Question

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 an aggregate function that, among other things, returns an array of elements, 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 wild card is on the left of the operator. 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)?

---

Please note that this is a simplified example demonstrating just the section I am having difficulty with.

Answer 1

What I ended up doing was writing a "commutator" operator. In postgres I created a new function and operator using the following two commands:

create function like_rev (text, text) returns boolean as $$ select $2 like $1 $$ language SQL;
create operator ~~~~ (procedure = like_rev,  leftarg=text, rightarg=text);

Which is effectively identical to a like, but reverses the argument order when comparing. So now I can accomplish my goal of doing a wildcard match against an array by simply replacing like with ~~~~, like so:

SELECT 
   * 
FROM (
      SELECT 
         lognum,
         array_agg(flightnum) as flightnums 
      FROM logs 
      GROUP BY 
         lognum
      ) s1 
WHERE 
   '8%' ~~~~ ANY(flightnums);

Note that this may not be the most performant solution - it's quite possible that the answer from Cassandra would be better if you require maximum performance. However, this works well for me, as it doesn't require any changes to the structure of the query.

Answer 2

You can accomplish what you're asking for by doing something like this.

I created a table and data to help better illustrate what I'm doing.

CREATE TABLE logs (id serial NOT NULL PRIMARY KEY, lognum int, flightnum int);

INSERT INTO logs (lognum, flightnum) VALUES (1,6);
INSERT INTO logs (lognum, flightnum) VALUES (1,7);
INSERT INTO logs (lognum, flightnum) VALUES (1,8);
INSERT INTO logs (lognum, flightnum) VALUES (2,80);
INSERT INTO logs (lognum, flightnum) VALUES (3,12);
INSERT INTO logs (lognum, flightnum) VALUES (4,8008);

postgres@[local]:5432:postgres:=# SELECT * FROM logs;
 id | lognum | flightnum 
----+--------+-----------
 13 |      1 |         6
 14 |      1 |         7
 15 |      1 |         8
 16 |      2 |        80
 17 |      3 |        12
 18 |      4 |      8008
(6 rows)

Time: 0.188 ms
postgres@[local]:5432:postgres:=#

Note that 1, 2, and 4 have flights with 8, 80, and 8008.

Now, using this query, find the lognums with 8 as a flightnum. This takes your original query with an array_agg, wraps another query around it to generates subscripts for each member in the array, which can be arbitrarily large. Finally, an outer query wraps that, which uses the generated subscript to allow you to do a comparison against each of the members of the flightnums array to see if they are LIKE '8%'.

SELECT lognum FROM ( 
                      SELECT lognum, 
                      flightnums, 
                      generate_subscripts(flightnums, 1) AS s 
                      FROM ( 
                             SELECT lognum, 
                                    array_agg(flightnum) AS flightnums 
                                    FROM logs 
                                         GROUP BY lognum
                           ) AS t1
                   ) AS t2 
            WHERE flightnums[s]::text LIKE '8%' ORDER BY lognum;

Which gives you the following output

postgres@[local]:5432:postgres:=# SELECT lognum FROM (SELECT lognum, flightnums, generate_subscripts(flightnums, 1) AS s FROM ( SELECT lognum, array_agg(flightnum) AS flightnums FROM logs GROUP BY lognum) AS t1) AS t2 WHERE flightnums[s]::text LIKE '8%' ORDER BY lognum;
 lognum 
--------
      1
      2
      4
(3 rows)

Time: 0.338 ms
postgres@[local]:5432:postgres:=# 

As would be expected from the data above.

For further array manipulation needs, I would recommend reading up on their chapter in the PostgreSQL documentation arrays