Using LIKE with ANY(array)?

Question

select sum(case when '%sys%' like any(series_arr)
                  or 'displays' = any(lob_arr)
                then revenue else 0 end) as sys_displ_revenue
from mytable

Basically, when I run this query, the term '%sys%' like any(<array column>) seems to get ignored, and only the 'displays' revenue is used.

How to solve this?

Answer 1

1. All PostgreSQL pattern matching operators accept the string as their left operand and the pattern as their right operand. Your code tries to do the opposite order.
2. As initially suggested by @mandy8055, you can unnest() and use like with each element.
3. The sum(case) conditional aggregate construct can be replaced with a filter clause.

Demo at db<>fiddle:

select sum(revenue)filter(where exists(select from unnest(series_arr)_(e) 
                                       where e like '%sys%')
                          or 'displays' = any(lob_arr)) as sys_displ_revenue
from mytable

Answer 2

You can use LIKE with the ANY construct. But the pattern goes to the right of the operand, and LIKE (or rather, it's internal implementation ~~) has no COMMUTATOR.

Custom operator

With the necessary privileges, you could create your own custom operator to make it work:

CREATE FUNCTION reverse_like (text, text)
  RETURNS boolean
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE COST 10
RETURN $2 LIKE $1;

CREATE OPERATOR <~~ (
  FUNCTION = reverse_like
, LEFTARG  = text
, RIGHTARG = text
);

Then:

SELECT sum(CASE WHEN '%sys%' <~~ ANY (series_arr)
                  OR 'displays' = ANY (lob_arr) THEN revenue END) AS sys_displ_revenue
FROM   mytable;

I skipped ELSE 0. Without ELSE, SQL CASE defaults to null, and sum ignores null anyway. Only makes a difference if all rows evaluate to null, in which case you get null instead of 0, (and you might want to catch that with COALESCE).

Be aware that you can't get (direct) index support with the indexed column to the right of the operator. But that hardly matters for your query, which does a full sequential scan of the table anyway.

See:

• Match string pattern to any array element
• PostgreSQL Operator Class for index support of custom operator
• Can PostgreSQL index array columns?

I am using minimal syntax for a "standard SQL" function, btw. See:

• What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

Without custom operator

Barring that, a correlated subquery does the job:

SELECT sum(CASE WHEN (SELECT bool_or(s ~ 'sys') FROM unnest (series_arr) s)
                  OR 'displays' = ANY (lob_arr) THEN revenue ELSE 0 END) AS sys_displ_revenue
FROM   mytable t

Using the simpler regexp operator ~, but all the same.

I would use the latter, unless you can put the custom operator to good use in more than just this query.