Imagine the data:

id       item        category                         basket
1        Banana      {"Fruit"}                        {"Veggie","Health"}
2        Carrot      {"Veggie"}                       {"Health","Beauty","Art"}
3        Banana      {"Fruit","Health"}               {"Beauty","Art","Veggie","Health"}
4        Potato      {"Beauty","Veggie","Art"}        {"Beauty","Veggie"}
5        Lipstick    {"Fruit"}                        {"Veggie", "Health", "Beauty"}

I would like to obtain:

id       item        category                         basket                                include_item
1        Banana      {"Fruit"}                        {"Veggie","Health"}                   add_fruit
2        Carrot      {"Veggie"}                       {"Health","Beauty","Art"}             add_veggie
3        Banana      {"Fruit","Health"}               {"Beauty","Art","Veggie","Health"}    add_fruit
4        Potato      {"Beauty","Veggie","Art"}        {"Beauty","Veggie"}                   add_veggie
5        Lipstick    {"Fruit"}                        {"Veggie", "Health", "Beauty"}        do_not_add

I attempted the code:

select *,
     case
         -- when category::char like '%Fruit%' and item = 'Banana' then 'add_fruit'
         -- when 'Vegetable'=any(category) and item in ('Potato', 'Carrot') then 'add_veggie'
         else 'do_not_add'
     end as include_item
from my_table

Neither of the commented options worked. How should I adjust the code to meet both criteria in case ?

Both category and basket are of type character varying[]

CodePudding user response：

You could normalize your table, so that you don't need to make string comparisons.

select *,
     case
         when category like '%Fruit%' and item = 'Banana' then 'add_fruit'
         when category like '%Veggie%' and item in ('Potato', 'Carrot') then 'add_veggie'
         else 'do_not_add'
     end as include_item
from ordertab

id | item     | category                  | basket                             | include_item
-: | :------- | :------------------------ | :--------------------------------- | :-----------
 1 | Banana   | {"Fruit"}                 | {"Veggie","Health"}                | add_fruit   
 2 | Carrot   | {"Veggie"}                | {"Health","Beauty","Art"}          | add_veggie  
 3 | Banana   | {"Fruit","Health"}        | {"Beauty","Art","Veggie","Health"} | add_fruit   
 4 | Potato   | {"Beauty","Veggie","Art"} | {"Beauty","Veggie"}                | add_veggie  
 5 | Lipstick | {"Fruit"}                 | {"Veggie", "Health", "Beauty"}     | do_not_add  

db<>fiddle here