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

user_586528 0
Viewed 432422

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'
case-insensitive where-clause sql-like postgresql
13 Answers

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'.

user_91768590

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;
user_117469090

ILIKE work in this case:

SELECT id 
  FROM groups
 WHERE name ILIKE 'Administrator'
user_25941210

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

 "select * from article where upper(content) LIKE upper('%' || $1 || '%')"
user_118457480 
-- 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');
user_117164080

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'
user_165603970 
select id from groups where name in ('administrator', 'ADMINISTRATOR', 'Administrator')
user_50994310
Related