PostgreSQL: Case insensitive string comparison

Question

Is there a simple ignore-case-comparison for PostgreSQL?

I want to replace:

SELECT id, user_name      FROM users          WHERE lower(email) IN (lower('[email protected]'), lower('[email protected]')); 

With something like:

SELECT id, user_name      FROM users          WHERE email IGNORE_CASE_IN ('[email protected]', '[email protected]'); 

The like and ilike operators work on single values (e.g. like '[email protected]'), but not on sets.

Answer 1

select *  where email ilike '[email protected]' 

ilike is similar to like but case insensitive. For escape character use replace()

where email ilike replace(replace(replace($1, '~', '~~'), '%', '~%'), '_', '~_') escape '~' 

or you could create a function to escape text; for array of text use

where email ilike any(array['[email protected]', '[email protected]'])