How to make "case-insensitive" query in Postgresql?
PostgresqlWhere ClauseSql LikeCase InsensitivePostgresql Problem Overview
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'
Postgresql Solutions
Solution 1 - Postgresql
Use LOWER function to convert the strings to lower case before comparing.
Try this:
SELECT id
FROM groups
WHERE LOWER(name)=LOWER('Administrator')
Solution 2 - Postgresql
using ILIKE
instead of LIKE
SELECT id FROM groups WHERE name ILIKE 'Administrator'
Solution 3 - Postgresql
The most common approach is to either lowercase or uppercase the search string and the data. But there are two problems with that.
- 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.
- 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.
- 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.) - 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.)
- 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 likeSELECT id FROM groups WHERE LOWER(name) = LOWER('ADMINISTRATOR');
, orSELECT 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.
Solution 4 - Postgresql
You can use ILIKE
. i.e.
SELECT id FROM groups where name ILIKE 'administrator'
Solution 5 - Postgresql
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
Solution 6 - Postgresql
You could also use POSIX regular expressions, like
SELECT id FROM groups where name ~* 'administrator'
SELECT 'asd' ~* 'AsD'
returns t
Solution 7 - Postgresql
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'.
Solution 8 - Postgresql
ILIKE work in this case:
SELECT id
FROM groups
WHERE name ILIKE 'Administrator'
Solution 9 - Postgresql
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;
Solution 10 - Postgresql
-- 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');
Solution 11 - Postgresql
For a case-insensitive parameterized query, you can use the following syntax:
"select * from article where upper(content) LIKE upper('%' || $1 || '%')"
Solution 12 - Postgresql
select id from groups where name in ('administrator', 'ADMINISTRATOR', 'Administrator')