PostgreSQL: Case insensitive string comparison

sql postgresql string-comparison case-insensitive

Adam Matan · Dec 19, 2010 · Viewed 96.1k times · Source

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

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]'])