CODEWITHSUNDEEP
postgresqlcollationodoo
Hardikgiri Goswami
Hardikgiri Goswami

Reputation: 514

How to find all relative accent in Postgresql select query?

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.

Upvotes: 1

Views: 891

Answers (2)

klin
klin

Reputation: 121604

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)

Upvotes: 1

Atul Arvind
Atul Arvind

Reputation: 16743

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!

Upvotes: 3

Related Questions