1

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. – Daniel Vérité Nov 28 '17 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. – a_horse_with_no_name Nov 28 '17 at 15:26
  • @a_horse_with_no_name I don't see PostgreSQL supporting that part of ICU required to do this. – Evan Carroll Nov 28 '17 at 16:05
  • @a_horse_with_no_name dba.stackexchange.com/q/191905/2639 – Evan Carroll Nov 28 '17 at 16:41
  • We're using PostgreSQL 9.5 – Boezie Nov 29 '17 at 11:21
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.

  • 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 '17 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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