Arabic Search That Ignores Diacritics in PostgreSQL
Mohammed Moutawakkil
5 min read
PostgreSQLArabicSearchpg_trgm
A user types محمد in the search box. The student is saved as مُحَمَّد. PostgreSQL says there is no match. For an Arabic app, that is not a small bug: it means search does not work.
I hit this while building Ratil, a platform for Quran memorization associations where teachers search students by name all day. Names are typed by many different people, some with diacritics, some with أ and some with ا, some ending in ة and some in ه. In this article I'll show how I made search ignore all of that, with one SQL function, a generated column and a trigram index. No search engine, no extra service.
The Stack
PostgreSQL 16
The pg_trgm extension for fast LIKE '%...%' and fuzzy matching
A generated column, so the normalized text is always up to date
Any backend that sends SQL: the examples use node-postgres
Why Arabic Search Fails
The same name can be written in many ways, and to PostgreSQL they are all different strings:
Diacritics (tashkeel): مُحَمَّد and محمد. Fatha, damma, kasra, tanwin, shadda and sukun are separate Unicode characters.
Alef forms: أحمد, إسماعيل, آدم are often typed as احمد, اسماعيل, ادم.
Final letters: مصطفى (alef maqsura) vs مصطفي, فاطمة (ta marbuta) vs فاطمه.
Hamza seats: عائشة vs عايشة.
Tatweel: محـــمد is محمد stretched with U+0640 for decoration.
Digits: ١٢٣ and 123 for phone numbers and IDs.
Persian keyboards: ی and ک instead of ي and ك.
The unaccent extension does not help here: its rules are made for Latin accents. So I wrote my own normalization.
The trick is PostgreSQL's translate(). It replaces each character of the second argument with the character at the same position in the third. When the second argument is longer, the extra characters are deleted. So one call can both map letter variants and remove diacritics:
SQL
CREATE OR REPLACE FUNCTION normalize_arabic(input text)
RETURNS text
LANGUAGE sql
IMMUTABLE STRICT PARALLEL SAFE
AS $$
SELECT lower(translate(
input,
-- characters to replace ...
'أإآٱىیةکؤئ' || '٠١٢٣٤٥٦٧٨٩'
-- ... followed by characters to delete: harakat, shadda, sukun,
-- dagger alef, maddah/hamza marks and tatweel
|| U&'\064B\064C\064D\064E\064F\0650\0651\0652\0653\0654\0655\0670\0640',
'ااااييهكوي' || '0123456789'
));
$$;
A few details:
The diacritics are written as U&'\064B...' escapes. They are combining characters, so typed directly they stick to the quote before them and are impossible to read or review.
lower() handles Latin names too, so MOHAMMED finds Mohammed.
The function is IMMUTABLE: the same input always gives the same output. PostgreSQL requires this for generated columns and indexes.
Mapping ة to ه and ى to ي is a choice. For person names it is what users expect. If you search Quranic text, where the difference matters, keep a stricter version.
SQL
SELECT normalize_arabic('مُحَمَّد الأمين'); -- محمد الامين
SELECT normalize_arabic('فاطمة الزهراء'); -- فاطمه الزهراء
SELECT normalize_arabic('محـــمد'); -- محمد
Step 2: A Generated Column
I don't want to normalize every row on every search, and I don't want to remember to update a second column in application code. A stored generated column does both:
SQL
ALTER TABLE students
ADD COLUMN full_name_search text
GENERATED ALWAYS AS (normalize_arabic(full_name)) STORED;
PostgreSQL computes it on insert and update. The original name stays exactly as the user typed it, with its diacritics, for display.
Step 3: A Trigram Index
Users type part of a name, so the query is LIKE '%term%'. A normal B-tree index can't help with a leading wildcard. A trigram index can:
SQL
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX students_full_name_search_trgm
ON students USING gin (full_name_search gin_trgm_ops);
Step 4: The Query
The search term goes through the same function in SQL. There is no second copy of the rules in JavaScript that could drift away from the database:
TypeScript
function escapeLike(term: string) {
return term.replace(/[\\%_]/g, (c) => "\\" + c);
}
const { rows } = await pool.query(
`SELECT id, full_name
FROM students
WHERE full_name_search LIKE '%' || normalize_arabic($1) || '%'
ORDER BY similarity(full_name_search, normalize_arabic($1)) DESC
LIMIT 20`,
[escapeLike(term.trim())]
);
escapeLike matters: without it, a user who types % or _ gets wildcard behavior instead of a literal search. similarity() puts the closest names first.
Now every one of these finds the right student:
| User types | Stored name |
|---|---|
| محمد | مُحَمَّد الأمين |
| احمد | أحمد بنعلي |
| فاطمه | فاطمة الزهراء |
| مصطفي | مصطفى |
| اسماعيل | إسماعيل |
| ادم | آدم |
EXPLAIN confirms that the query uses the index:
TEXT
Bitmap Heap Scan on students
Recheck Cond: (full_name_search ~~ '%محمد%'::text)
-> Bitmap Index Scan on students_full_name_search_trgm
The Trap: Your Database Locale
This one cost me time. pg_trgm only builds trigrams from characters that the database locale considers letters. With the C locale, Arabic letters are not letters. Check it:
SQL
SELECT show_trgm('محمد');
With a UTF-8 locale such as C.UTF-8 or en_US.UTF-8, you get a list of trigrams.
With C, you get {}.
With empty trigrams, LIKE still returns correct results, but the index has nothing to narrow the search with, and similarity() and the % fuzzy operator return nothing for Arabic. The fix is to create the database with a UTF-8 locale:
The official Docker image uses en_US.utf8 by default, so check databases created by other tools or managed providers.
Multi-Tenant Bonus
Ratil serves many associations from one database, with Row Level Security keeping each association's data separate. The search query above has no WHERE association_id = .... It doesn't need one: the RLS policy adds that filter to every query, so search can never return another association's students.
What I Gained
Search works the way people type, with or without diacritics, hamza or ta marbuta.
The rules live in one SQL function, used by both the stored column and the query.
It's fast on large tables thanks to the trigram index, with no Elasticsearch or extra service to run.
Names are still shown exactly as they were entered.
Conclusion
Arabic search in PostgreSQL needs three pieces: a normalization function, a generated column and a trigram index, plus a database created with a UTF-8 locale. It is around 30 lines of SQL, and it turns "no results" into the student the teacher was looking for.
If you're building an Arabic or multilingual app and have questions about the setup, feel free to get in touch.
Deploying Next.js on a Single VM: Docker, PostgreSQL, nginx and GitHub Actions