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.

  • It's not the collation but the lc_ctype property of the database that makes upper('Å') being 'å', but unfortunately it cannot be changed dynamically. Maybe you just need to create your db with an lc_ctype that is Unicode-aware. Nov 28, 2017 at 13:48
  • Which Postgres version are you using? With Postgres 10 you might be able to do that using ICU collations although your obfuscation layer (aka "ORM") probably gets in the way when you try to specify a collation for a query.
    – user1822
    Nov 28, 2017 at 15:26
  • @a_horse_with_no_name I don't see PostgreSQL supporting that part of ICU required to do this. Nov 28, 2017 at 16:05
  • @a_horse_with_no_name dba.stackexchange.com/q/191905/2639 Nov 28, 2017 at 16:41
  • We're using PostgreSQL 9.5
    – Boezie
    Nov 29, 2017 at 11:21

1 Answer 1



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

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


WITH unaccent, french_stem;

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

  • Marked as closest to what I wanted to achieve. Although not entirely feasible given what we wanted to achieve (in that the app is to be distributed worldwide which would mean an awful lot of work). To work around the issue (using a hibernate criteria) we replace all "special" characters with '_' in the "like". The disadvantage is that it gives you false positives, but that's still better then having no results found at all...
    – Boezie
    Dec 1, 2017 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.