In postgres, we can easily remove the accents in the string by using the extension named unaccent. This makes text comparison simpler and enables accent-insensitive searching. This extension is mainly used in multilingual applications, and it can also be used for full-text search and text normalization. It is one of the core extensions in PostgreSQL and is used by one of the major ERP systems, Odoo.
Odoo actually uses three extensions of postgres in its source code. When you search for create extension in pycharm, you get the names of these three extensions.
- pgcrypto: This extension is mainly used in postgres for hashing, encryption, and decryption, etc.
- pg_trgm: This extension is mainly used for full-text search in postgres using trigram algorithms
- unaccent: By using the unaccent extension, we can remove the accents from the text.
You can check if these extensions are available inside your postgres like this.
select * from pg_available_extensions where name in ('unaccent','pgcrypto','pg_trgm');Result :
name | default_version | installed_version | comment
----------+-----------------+-------------------+-------------------------------------------------------------------
unaccent | 1.1 | 1.1 | text search dictionary that removes accents
pg_trgm | 1.6 | 1.6 | text similarity measurement and index searching based on trigrams
pgcrypto | 1.4 | 1.4 | cryptographic functions
(3 rows)
In this article, we will detail explore the functionalities of the extension named unaccent.
Now, check if the unaccent extension is available in your postgres.
select * from pg_available_extensions where name = 'unaccent';
Result :
name | default_version | installed_version | comment
----------+-----------------+-------------------+---------------------------------------------
unaccent | 1.1 | | text search dictionary that removes accents
(1 row)
Now, create the extension.
create extension unaccent ;
Check the functionalities provided by this extension by using the command below.
\dx+ unaccent
Result :
Objects in extension "unaccent"
Object description
---------------------------------------------------------------
function unaccent_init(internal)
function unaccent_lexize(internal,internal,internal,internal)
function unaccent(regdictionary,text)
function unaccent(text)
text search dictionary unaccent
text search template unaccent
(6 rows)
unaccent_init()
\df+ unaccent_init();
Result :
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Leakproof? | Access privileges | Language | Internal name | Description
--------+---------------+------------------+---------------------+------+------------+----------+----------+----------+------------+-------------------+----------+---------------+-------------
public | unaccent_init | internal | internal | func | volatile | safe | postgres | invoker | no | | c | unaccent_init |
(1 row)
By inspecting the metadata of the function named unaccent_init(), we can see that the result_data_types and return_data_types are internal. Postgres refuses to execute any function that has an internal argument unless the caller is C code.
This is the code part related to this function in postgres source code.
CREATE FUNCTION unaccent_init(internal)
RETURNS internal
AS 'MODULE_PATHNAME', 'unaccent_init'
LANGUAGE C PARALLEL SAFE;
unaccent_lexize()
\df+ unaccent_lexize
Result :
List of functions
-[ RECORD 1 ]-------+---------------------------------------
Schema | public
Name | unaccent_lexize
Result data type | internal
Argument data types | internal, internal, internal, internal
Type | func
Volatility | volatile
Parallel | safe
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges |
Language | c
Internal name | unaccent_lexize
Description |
This function is also declared with internal arguments. So these types of functions cannot be called directly.
This is the code part related to this function in the source code of postgres.
CREATE FUNCTION unaccent_lexize(internal,internal,internal,internal)
RETURNS internal
AS 'MODULE_PATHNAME', 'unaccent_lexize'
LANGUAGE C PARALLEL SAFE;
unaccent()
\df+ unaccent
Result :
List of functions
-[ RECORD 1 ]-------+--------------------
Schema | public
Name | unaccent
Result data type | text
Argument data types | regdictionary, text
Type | func
Volatility | stable
Parallel | safe
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges |
Language | c
Internal name | unaccent_dict
Description |
-[ RECORD 2 ]-------+--------------------
Schema | public
Name | unaccent
Result data type | text
Argument data types | text
Type | func
Volatility | stable
Parallel | safe
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges |
Language | c
Internal name | unaccent_dict
Description |
Let’s do some practical queries related to this unaccent function.
SELECT unaccent('café');Result :
unaccent
----------
cafe
(1 row)
Now, we can see the accents from the text are removed and displayed to the user.
Now, create a simple table and insert values for testing.
CREATE TABLE people (
id serial,
name text
);
INSERT INTO people(name)
VALUES
('José'),
('André'),
('François'),
('Müller'),
('Zoë');
select * from people;
Result :
id | name
----+----------
1 | José
2 | André
3 | François
4 | Müller
5 | Zoë
(5 rows)
Now, use the unaccent() in the select query like this.
SELECT name,
unaccent(name)
FROM people;
Result :
name | unaccent
----------+----------
José | Jose
André | Andre
François | Francois
Müller | Muller
Zoë | Zoe
(5 rows)
Now, we can see the original text that contains the accent and the text that has no accents.
SELECT *
FROM people
WHERE unaccent(name) = unaccent('Jose');
Result :
id | name
----+------
1 | José
(1 row)
Now, use the other unaccent() that contains two arguments.
unaccent(regdictionary,text)
SELECT unaccent('unaccent', 'café');Result :
unaccent
----------
cafe
(1 row)
Here, the first argument is regdictionary. The regdictionary is like a postgres internal datatype that refers to a text search dictionary in postgres. Postgres has some text search dictionaries. You can see the available text search dictionaries by using the query below.
SELECT dictname, dictnamespace::regnamespace
FROM pg_ts_dict;
Result :
dictname | dictnamespace
-----------------+---------------
simple | pg_catalog
arabic_stem | pg_catalog
armenian_stem | pg_catalog
basque_stem | pg_catalog
catalan_stem | pg_catalog
danish_stem | pg_catalog
dutch_stem | pg_catalog
english_stem | pg_catalog
estonian_stem | pg_catalog
finnish_stem | pg_catalog
french_stem | pg_catalog
german_stem | pg_catalog
greek_stem | pg_catalog
hindi_stem | pg_catalog
hungarian_stem | pg_catalog
indonesian_stem | pg_catalog
irish_stem | pg_catalog
italian_stem | pg_catalog
lithuanian_stem | pg_catalog
nepali_stem | pg_catalog
norwegian_stem | pg_catalog
portuguese_stem | pg_catalog
romanian_stem | pg_catalog
russian_stem | pg_catalog
serbian_stem | pg_catalog
spanish_stem | pg_catalog
swedish_stem | pg_catalog
tamil_stem | pg_catalog
turkish_stem | pg_catalog
yiddish_stem | pg_catalog
unaccent | public
(31 rows)
Here, we pass the unaccent as the text search dictionary as the first argument.
SELECT unaccent('unaccent'::regdictionary,
'résumé');Result :
unaccent
----------
resume
(1 row)
We can see the oid of each dictionary name like this.
SELECT oid,
dictname
FROM pg_ts_dict
WHERE dictname='unaccent';
Result :
oid | dictname
--------+----------
199258 | unaccent
(1 row)
Now, use any other text search dictionary instead of unaccent and check the result like this.
SELECT unaccent('arabic_stem'::regdictionary,
'résumé');Result :
unaccent
----------
résumé
(1 row)
The unaccent extension offers a way to handle text without considering accents in the postgres. By learning the functions it adds and how text search dictionaries work, you can use it well in queries. No matter if you are checking names, searching for user input, or working with text in languages without accents makes results more reliable, without altering the original data.