using LOWER with IN condition [duplicate]

Question

assume I have a table named comodity_group and the structure looks like:

+----------+-------+
| group_id | name  |
+----------+-------+
| 1        | Data1 |
+----------+-------+
| 2        | Data2 |
+----------+-------+
| 3        | data3 |
+----------+-------+

and I have the following query

SELECT * FROM comodity_group WHERE name IN('data1','data2','data3')

the query return 0 result, because condition is all in lowercase (note that the condition is also dynamic, meaning it can be Data1 or daTa1, etc)

so I want to make both condition and field name in lowercase, in other word case insensitive.

Answer 1

You can use ILIKE and an array:

select *
from comodity_group
where name ilike any (array['Data1', 'data2', 'dATA3']);

Note that this won't be really fast as the ILIKE operator can't make use of a regular index on the name column.