Search code examples
sqlpostgresqlcase-insensitivesql-in

Postgres Case Insensitive in IN operator?

My Sample Sql is

select * from fruits where name in ('Orange','grape','APPLE','ManGO',etc....);//

Is possible to include ilike or ~* in IN Operator in Postgres?

My solution is

select * from fruits where upper(name) in 
(upper('Orange'),upper('grape'),upper('APPLE'),upper('ManGO'),etc....);

i think it is not correct method, Please let me know any optimal solution for this case

Solution

  • You can try to use ILIKE with ANY

    SELECT * 
FROM fruits 
WHERE name ILIKE ANY(array['Orange', 'grape', 'APPLE', 'ManGO']);

    sqlfiddle