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

id        name
1         München

Is this possible?

  • I.e. dicratic-insenstive pattern matching and collation May 5, 2015 at 0:36

2 Answers 2


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.


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.

Your Answer

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

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