How to make "case-insensitive" query in Postgresql?

Question

Is there any way to write case-insensitive queries in PostgreSQL, E.g. I want that following 3 queries return same result.

SELECT id FROM groups where name='administrator'

SELECT id FROM groups where name='ADMINISTRATOR'

SELECT id FROM groups where name='Administrator'

Answer 1

Use LOWER function to convert the strings to lower case before comparing.

Try this:

SELECT id 
  FROM groups
 WHERE LOWER(name)=LOWER('Administrator')

Answer 2

using ILIKE instead of LIKE

SELECT id FROM groups WHERE name ILIKE 'Administrator'

Answer 3

The most common approach is to either lowercase or uppercase the search string and the data. But there are two problems with that.

1. It works in English, but not in all languages. (Maybe not even in most languages.) Not every lowercase letter has a corresponding uppercase letter; not every uppercase letter has a corresponding lowercase letter.
2. Using functions like lower() and upper() will give you a sequential scan. It can't use indexes. On my test system, using lower() takes about 2000 times longer than a query that can use an index. (Test data has a little over 100k rows.)

There are at least three less frequently used solutions that might be more effective.

1. Use the citext module, which mostly mimics the behavior of a case-insensitive data type. Having loaded that module, you can create a case-insensitive index by CREATE INDEX ON groups (name::citext);. (But see below.)
2. Use a case-insensitive collation. This is set when you initialize a database. Using a case-insensitive collation means you can accept just about any format from client code, and you'll still return useful results. (It also means you can't do case-sensitive queries. Duh.)
3. Create a functional index. Create a lowercase index by using CREATE INDEX ON groups (LOWER(name));. Having done that, you can take advantage of the index with queries like SELECT id FROM groups WHERE LOWER(name) = LOWER('ADMINISTRATOR');, or SELECT id FROM groups WHERE LOWER(name) = 'administrator'; You have to remember to use LOWER(), though.

---

The citext module doesn't provide a true case-insensitive data type. Instead, it behaves as if each string were lowercased. That is, it behaves as if you had called lower() on each string, as in number 3 above. The advantage is that programmers don't have to remember to lowercase strings. But you need to read the sections "String Comparison Behavior" and "Limitations" in the docs before you decide to use citext.

Answer 4

You can use ILIKE. i.e.

SELECT id FROM groups where name ILIKE 'administrator'

Answer 5

You can also read up on the ILIKE keyword. It can be quite useful at times, albeit it does not conform to the SQL standard. See here for more information: http://www.postgresql.org/docs/9.2/static/functions-matching.html

Answer 6

You could also use POSIX regular expressions, like

SELECT id FROM groups where name ~* 'administrator'

SELECT 'asd' ~* 'AsD' returns t

Answer 7

use ILIKE

select id from groups where name ILIKE 'adminstration';

If your coming the expressjs background and name is a variable use

select id from groups where name ILIKE $1;

Answer 8

Using ~* can improve greatly on performance, with functionality of INSTR.

SELECT id FROM groups WHERE name ~* 'adm'

return rows with name that contains OR equals to 'adm'.

Answer 9

ILIKE work in this case:

SELECT id 
  FROM groups
 WHERE name ILIKE 'Administrator'

Answer 10

For a case-insensitive parameterized query, you can use the following syntax:

 "select * from article where upper(content) LIKE upper('%' || $1 || '%')"

Answer 11

If you want not only upper/lower case but also diacritics, you can implement your own func:

CREATE EXTENSION unaccent;

CREATE OR REPLACE FUNCTION lower_unaccent(input text)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
BEGIN
    return lower(unaccent(input));
END;
$function$;

Call is then

select lower_unaccent('Hôtel')
>> 'hotel'

Answer 12

A tested approach is using ~*

As in the example below

SELECT id FROM groups WHERE name ~* 'administrator'

Answer 13

-- Install 'Case Ignore Test Extension'
create extension citext;

-- Make a request
select 'Thomas'::citext in ('thomas', 'tiago');

select name from users where name::citext in ('thomas', 'tiago');

Answer 14

None of the existing answers are correct.

The Turkish language has multiple representations of the letter I which are equal from a case-sensitivity point of view. Comparing the lowercase or uppercase forms of these characters will return false.

Georgian has a character ჳ that has no uppercase equivalent, and a character უ that has no lowercase equivalent. If you dig into Java's implementation of String.equalsIgnoreCase() you will find the following code snippet:

// Case insensitive comparison of two code points
private static int compareCodePointCI(int cp1, int cp2) {
    // try converting both characters to uppercase.
    // If the results match, then the comparison scan should
    // continue.
    cp1 = Character.toUpperCase(cp1);
    cp2 = Character.toUpperCase(cp2);
    if (cp1 != cp2) {
        // Unfortunately, conversion to uppercase does not work properly
        // for the Georgian alphabet, which has strange rules about case
        // conversion.  So we need to make one last check before
        // exiting.
        cp1 = Character.toLowerCase(cp1);
        cp2 = Character.toLowerCase(cp2);
        if (cp1 != cp2) {
            return cp1 - cp2;
        }
    }
    return 0;
}

Besides, if you'd like to compare strings in a case-insensitive manner you'd likely want to strip out other differences as well. You can use a process called "text normalization" to convert text into a canonical form that strips away accents, punctuation, characters that display the same but have different values, and invisible characters.

The way I personally dealt with this problem is storing two representations for each column that I want to search against:

• foobar_display which corresponds to the original value that the user entered, and is used for display purposes.
• foobar_compare which is used for search and comparison purposes.

Every time that I insert a row into the database I convert foobar_display into foobar_compareusing a [text normalization library](https://github.com/slugify/slugify). I store both values, and any time a user tries searching for a value I usefoobar_compare` to detect partial or full matches.

Finally, I add a unique constraint against foobar_compare to unwanted duplicates.

Answer 15

select id from groups where name in ('administrator', 'ADMINISTRATOR', 'Administrator')