Can I configure the PostgreSQL locale so that it ignores umlauts in ILIKE queries?

Question

I'm using PostgreSQL to look for city names matching a user-entered string. I'd like to be able to match a row with the name "München" (German for "Munich") if the user types "Munchen". In other words, I'd like to be able to ignore umlauts.

For example:

SELECT * FROM cities WHERE cities.name ILIKE Mu%

would match

cities
id        name
1         München

Is this possible?

Answer 1

A common solution to this problem is to have a "shadow" or "search" column for proper names. This solution has the advantage of being portable.

For example, here in Ireland, there are many people who are called MacGuire and there are those called McGuire. Depending on other names, some Irish people also use accents in a particular name and some don't. Furthermore, some people put a space between "Mac" and "Guire". And don't even get me started on the problems that names such as O'Malley cause with SQL, i.e. escaping the single quote.

So, in a shadow column, one might have rules "Mac"/"Mc"/"Mac " -> MC (shadow columns are normally kept all caps - another source of varialbility (could have Mcguire). "O" and "O'" -> O.

What's more, say the first name was Rónán (Ronán and Ronan are also common), so another rule would be

á -> A and

ó -> O

You can apply these rules via ON INSERT/UPDATE triggers.

So, this would lead to a table thus:

Display_Name    Search_name    Address
------------    -----------    -------
Ronan MacGuire  RONAN MCGUIRE  1 Clontarf D1
Ronan McGuire   RONAN MCGUIRE  2 Malahide D2
Rónán Mcguire   RONAN MCGUIRE  3 Mary's terrace D3

And then the service agent will distinguish on the basis of address.

So, I am suggesting that something similar might be a solution to your problem.

Answer 2

You are mainly looking for an accent insensitive collation.

According to this post on stackoverflow Does PostgreSQL support “accent insensitive” collations? there is a unaccent module that supports it.

I haven't used it myself though.