Why it works different with ~~ any()?

Question

Let me first show you my table:

INSERT INTO my_table(name, brand, source)
VALUES ('Abc', 'Abc', 'Orig'), 
('Auchan', 'Auchan', 'Orig'), 
('Auchan', 'Auchan', 'Added'),
('dj-auchan-djd', 'Auchan', 'Added'),
('Auchan', 'Other', 'Added'),
('Other', 'oj_auchan', 'Added'),
('Other', 'Other', 'Added');

What I want to do is to delete data where source is 'Added' and brand or name is like brand where source is 'Orig'. For example here we will delete next rows:

('Auchan', 'Auchan', 'Added'), - simply name and brand is 'Auchan'
('dj-auchan-djd', 'Auchan', 'Added'), - brand ad name has 'Auchan'(but name lowercase)
('Auchan', 'Other', 'Added'), - name is 'Auchan'
('Other', 'oj_auchan', 'Added') - brand has 'Auchan' but lowercase.

So what it would be when we manually create this brands comparing:

delete
    from my_table
    where lower(name) ~~ any
           ('{%auchan%,%abc%}') IS TRUE
       or lower(brand) ~~ any
          ('{%auchan%,%abc%}') IS TRUE
       and source = 'Added';

It works good, we deleting all rows with this 'Auchan'. But when I am trying to aggregate array of this brands:

delete
from my_table
where lower(name) ~~ any
      (select '{'||array_to_string(ARRAY_AGG(DISTINCT '%' || lower(brand) || '%'), ',')||'}'
      from my_table
      where source = 'Orig') IS TRUE
   or lower(brand) ~~ any
      (select '{' || array_to_string(ARRAY_AGG(DISTINCT '%' || lower(brand) || '%'), ',') || '}'
      from my_table
      where source = 'Orig') IS TRUE
   and source = 'Added';

No data will be deleted.

I even checked if they are similar, and yes these two arrays will be similar...

Can someone plz help(or maybe give advice how to delete this occurrences)?

score 0 · Accepted Answer · answered Sep 24 '21 at 10:48

The problem is that the result of your subquery is interpreted as a string, not an array. This is because these two forms are semantically different:

~~ ANY ('...')  -- will be interpreted as an array literal

and

~~ ANY (SELECT ...)  -- will compare with all query results in turn

So you can simply write:

WHERE lower(name) ~~ ANY
      (SELECT DISTINCT '%' || lower(brand) || '%'
       FROM my_table
       WHERE source = 'Orig')

Why it works different with ~~ any()?

1 Answers1