PostgreSQL: Return value with case if text is found in varchar array

Question

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[]

In what way did it "not work"? ("It didn't work", without any descriptions, is the least helpful phrase ever used in questions here.) The LIKE expression should have worked? Also, why is the data in a string, not a native array or a JSON format? Do you have the option to Fix the datatype being used? — MatBailie
– MatBailie, Commented Jun 9, 2022 at 17:18

nbk · Accepted Answer · 2022-06-09 18:30:23Z

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

Collectives™ on Stack Overflow

PostgreSQL: Return value with case if text is found in varchar array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related