1

I have a table named contacts

 id |       name       
----+------------------
 44 | Aarón

I am trying to execute a query :

select id,name from contacts where name ilike 'Aaro%';

It return (0 rows)

I am trying to search 'o' and also expect to have result include all accent of 'o' like 'ó'. As I have did some googling and stackoverlfowing I have found that using locale I need to install the collation. So I have installed the collation in linux by

sudo locale-gen --no-archive de_DE.utf-8

after that I have tried to install collation in the postgresql database.

create collation de (LOCALE='de_DE.utf-8');

collation generated successfully. I have tried to list out this collation by using select * from pg_collation; and it is there.

after doing all this I tried again to get result of all relative accent 'o' by query:

select id,name from contacts where name ilike 'Aaro%';

but again i got (0 rows)

Ultimatly I want the record "Aarón" when I execute above query.

Thanks in Advance.

Atul Arvind
  • 16,054
  • 6
  • 50
  • 58
Hardikgiri Goswami
  • 514
  • 3
  • 14
  • http://dba.stackexchange.com/questions/124695/postgresql-translate-special-characters or you can define by yourself. – Luan Huynh Dec 14 '16 at 09:08
  • Collation will not alter equality checks and such (like `LIKE`, `ILIKE`, `~`, etc.) but instead help you order your data with a different rule-set (some languages have strict rules for ordering accented characters). In PostgreSQL the typical solution for your problem is `unaccent()` (see the answers). – pozs Dec 14 '16 at 09:37

2 Answers2

3

You can use unaccent module of postgres.

for that you need to install postgresql-contrib in your system. you can install it using below command in debian based linux.

sudo apt-get install postgresql-contrib

after that you can create unaccent in postgres.

postgres_db=# create EXTENSION unaccent;
CREATE EXTENSION
postgres_db=# select name from test where unaccent(name) ilike 'Aaro%';
 name  
-------
 Aarón
(1 row)

Hope this helps!

Atul Arvind
  • 16,054
  • 6
  • 50
  • 58
1

The extension unaccent is a good solution. If you deal with a small set of characters without ligatures you can alternatively use a simple function like this one for Polish:

create or replace function unaccent_pl(text)
returns text language sql immutable as $$
    select translate($1, 'ąćęłńóśźżĄĆĘŁŃÓŚŹŻ', 'acelnoszzACELNOSZZ')
$$;

select unaccent_pl('Zażółć gęślą jaźń');

    unaccent_pl    
-------------------
 Zazolc gesla jazn
(1 row)
klin
  • 112,967
  • 15
  • 204
  • 232
  • `translate()` could be a good alternative, but people usually forget about [unicode combining marks](https://en.wikipedia.org/wiki/Combining_character) when they use it. And, no, unfortunately [PostgreSQL does not normalize unicode](http://stackoverflow.com/questions/24863716/unicode-normalization-in-postgres) (yet). – pozs Dec 14 '16 at 09:47
  • As mentioned above `translate()` is of limited use. Unicode normalization is not a problem if the data come from a well-written client applications. – klin Dec 14 '16 at 10:06