PostgreSQL unaccent and full text search for Arabic/Persian

Question

I'm using an application that uses PostgreSQL as its database and it uses the unaccent extension to normalize text. I want to improve its search functionality by modifying the unaccent.rules file.

I edit /usr/share/postgresql/16/tsearch_data/unaccent.rules and add some rules for Arabic unicode block (U+0600 to U+06ff):

۰   0
۱   1
۲   2
۳   3
۴   4
۵   5
۶   6
۷   7
۸   8
۹   9
َ

and it's working fine.

SELECT unaccent('سَلام ۱۳۲');
 unaccent
----------
 سلام 123
(1 row)

Problem 1:

The problem is with Zero Width Non-Joiner (ZWNJ - U+200C), It should be replaced with space (U+0020).

سَلام‌علیکم -> سلام علیکم

What I Tried:

I tried this rows, but ether not working or giving error:

• "‌" " ": invalid syntax: more than two strings in unaccent rule (warning) + it didn't work.
• ‌ " ": invalid syntax: more than two strings in unaccent rule (warning) + it didn't work.
• \u200C \u0020: Suggested by ChatGPT, but it didn't work.
• \u200C " ": Suggested by ChatGPT, but it didn't work.

Note.1:

In the first two lines above, there is an invisible ZWNJ character, which is shown as <200c> in VIM, but it's not visible in this post.

Note.2:

I did't add all these lines at the same time, I try them one by one.

Note.3:

There is no other rule for ZWNJ in the unaccent.rules

Problem 2:

Is there a way to add new rule file instead of editing the default one? I can't edit the application source code and change the queries.

Does adding something like /usr/share/postgresql/16/tsearch_data/arabic.stop or /usr/share/postgresql/16/tsearch_data/arabic.rules and restarting the service, make PostgreSQL to understand it?

Is it required to run some query to reload the file?

Is it required to change the way search is requested from the application?

Answer 1

Problem #1 is addressed by PostgreSQL 17, as mentioned in the release notes and the commit message:

Allow unaccent character translation rules to contain whitespace and quotes (Michael Paquier)
The syntax for the unaccent.rules file has changed.

Problem #2:

• just adding an arabic.rules file will be ineffective. The rules files correspond to dictionaries, see specifically these declarations in the SQL part of the unaccent extension:

  CREATE TEXT SEARCH TEMPLATE unaccent (
     INIT = unaccent_init,
     LEXIZE = unaccent_lexize
  );
  
  CREATE TEXT SEARCH DICTIONARY unaccent (
     TEMPLATE = unaccent,
     RULES    = 'unaccent'
  );
  

  You may create other dictionaries, but the unaccent(text) function as shipped with Postgres searches for the unaccent dictionary.

• Is it required to run some query to reload the file?
  The file is loaded at first use by each backend and then kept in memory for the rest of the session. So reconnecting reloads the file. There doesn't seem to be any other way.

• Is it required to change the way search is requested from the application?
  You mention that you can't change the application. But if it just calls unaccent() without a schema qualification, possibly you can provide your own unaccent(text) function in a schema coming first in the search_path order, thus shadowing the other function.