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?
2
Answers
You can use
LIKE
with theANY
construct. But the pattern goes to the right of the operand, andLIKE
(or rather, it’s internal implementation~~
) has noCOMMUTATOR
.Custom operator
With the necessary privileges, you could create your own custom operator to make it work:
Then:
I skipped
ELSE 0
. WithoutELSE
, SQLCASE
defaults tonull
, and sum ignores null anyway. Only makes a difference if all rows evaluate tonull
, in which case you get null instead of 0, (and you might want to catch that withCOALESCE
).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:
I am using minimal syntax for a "standard SQL" function, btw. See:
Without custom operator
Barring that, a correlated subquery does the job:
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.
unnest()
and uselike
with each element.sum(case)
conditional aggregate construct can be replaced with afilter
clause.Demo at db<>fiddle: