PostgreSQL accent + case insensitive search
case-insensitive, database-design, full-text-search, indexing, postgresql
Solution
If you need to "combine with case insensitive", there are a number of options, depending on your exact requirements.
Maybe simplest, make the expression index case insensitive.
Building on the function `f_unaccent()` laid out in the referenced answer:
- Does PostgreSQL support "accent insensitive" collations?
CREATE INDEX users_lower_unaccent_name_idx ON users(lower(f_unaccent(name)));
Then:
SELECT *
FROM users
WHERE lower(f_unaccent(name)) = lower(f_unaccent('João'));
Or you could build the `lower()` into the function `f_unaccent()`, to derive something like `f_lower_unaccent()`.
Or (especially if you need to do fuzzy pattern matching anyways) you can use a trigram index provided by the additional module `pg_trgm` building on above function, which also supports `ILIKE`. Details:
- LOWER LIKE vs iLIKE
I added a note to the referenced answer.
Or you could use the additional module `citext` (but I rather avoid it):
- Deferrable, case-insensitive unique constraint
Problem
I'm looking for a way to support with good performances case insensitive + accent insensitive search. Till now we had no issue on this using MSSql server, on Oracle we had to use `OracleText`, and now we need it on PostgreSQL. I've found this post about it, but we need to combine it with case insensitive. We also need to use indexes, otherwise performances could be impacted. Any real experience about the best approach for large databases?