PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Using LIKE with ANY(array_col)?

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?

Tags:

2

Answers


  1. 0

    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:

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

    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.

    Login or Signup to reply.
  2. 0
    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search