Menu
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.
529
PostgreSQL names are case sensitive. By default, AWS Schema Conversion Tool (AWS SCT) uses object name in lowercase for PostgreSQL. In most cases, you'll want to use AWS Database Migration Service transformations to change schema, table, and column names to lower case.
While using regular expressions, we need to use the PostgreSQL ~* operator instead of the like operator; we can also use the ilike operator in PostgreSQL. We can also create an extension name as citext to use the case insensitive query in PostgreSQL; we need to create it first to use the extension of citext.
Comparing strings in a case insensitive manner means to compare them without taking care of the uppercase and lowercase letters. To perform this operation the most preferred method is to use either toUpperCase() or toLowerCase() function. Example 1: This example uses toUpperCase() function to compare two strings.
<> is the standard SQL operator meaning "not equal". Many databases, including postgresql, supports != as a synonym for <> . They're exactly the same in postgresql.
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]']) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
