[SOLVED] Ignore special characters in the WHERE clause

Ignore special characters in the WHERE clause

I have a table named artists with a record with the value 'Miró' in the name column. When I do this request:

SELECT "artists".* FROM "artists" WHERE name = 'Miró'

I have one result, so it works.

Now, when I do this request (without the special ó) :

SELECT "artists".* FROM "artists" WHERE name = 'Miro'

I don't find anything. I want to ignore the special char. Is there a way to do it?
I have postgres 9.1.9.

Solution

For a more targeted pattern matching, you can use the function unaccent(), provided by the additional module unaccent:

SELECT * FROM artists WHERE unaccent(name) = 'Miro';

To make this fast, create a functional index. You have to overcome the obstacle that the function is only STABLE, not IMMUTABLE. I wrote a comprehensive answer with instructions (including installation) and links recently:
Does PostgreSQL support "accent insensitive" collations?