Using LIKE to search case insensitive over language specific characters (PostgreSQL)

Question

Using a PostgreSQL database (Collation C, Encoding UTF8) we store data from various languages. This means we also get the special characters for free (e.g. Å, å, ...)

Within an application we now want to perform a case-insensitive search. Meaning if the user types 'Å' in the search box, the query must also take into account 'å'.
However, using the lower(string) or upper(string) method completely ignores this values when casting. And as such, no matching records are found...

Is there any way around this?
Using regex it seems one could use \p{L} to compare values, but I need it at the query level so I guess that's a no-go.

On a side-note: we are using Hibernate to create the query.

Answer 1

unaccent

The traditional, non-PG 10 method of this is to the extension unaccent.

CREATE EXTENSION unaccent;
SELECT 'a' = lower(unaccent('Å'));

You jam this into a TEXT SEARCH CONFIGURATION

WITH unaccent, french_stem;

And then magically all of your fts stuff works as English speakers desire.