PostgreSQL in clause with ignore case values

Question

I'm taking this link - PostgreSQL: How to make "case-insensitive" query and asking a question.

I am looking to pass values and should get response for case insensitive as well.

select * from account_role where descr in ('Acquirer','Advisors');

If I pass values like acquirer and advisors it should work. If I pass values like 'ACQUIRER' and 'ADVISORS'.

The same query I've to use in JPQL where I've join with other tables.

Answer 1

You can pass your values as an ARRAY and use ANY in combination with ILIKE to make it case insensitive, e.g.

WITH j (txt) AS (
  VALUES ('ACQUIRER'),('ADVISORS')
) 
SELECT * FROM j 
WHERE txt ILIKE ANY (ARRAY['AcQuIrEr', 'AdvisorS']);

   txt    
----------
 ACQUIRER
 ADVISORS

See this db<>fiddle