Postgresql – How to use full text search in Persian using Postgresql

full-text-searchpostgresql

I'm using Postgresql 9.6 and I'd like to run a query like this:

select post_id 
from comments 
where to_tsvector('pg_catalog.persian', comments.body) @@ to_tsquery('pg_catalog.persian', 'کتاب') ;

But Persian is not among the languages supported in postgresql dictionary:

postgres=# \dF
               List of text search configurations
   Schema   |    Name    |              Description              
------------+------------+---------------------------------------
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | italian    | configuration for italian language
 pg_catalog | norwegian  | configuration for norwegian language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | turkish    | configuration for turkish language
(16 rows)

So I get error:

ERROR: text search configuration "pg_catalog.persian" does not exist

My default DB config is for English:

postgres=# show default_text_search_config;
 default_text_search_config 
----------------------------
 pg_catalog.english
(1 row)

I've tried pg_catalog.simple but it returns anything, while there are results for کتاب.

So I'm wondering how can I make a Full text Search in this situation to get relevant Persian results?

Best Answer

use this.

select post_id 
from comments 
where to_tsvector('simple', comments.body) @@ to_tsquery('simple', 'کتاب') ;

OR

select post_id 
from comments 
where to_tsvector('simple', comments.body) @@ to_tsquery('کتاب') ;

Or use this extension:

https://pgroonga.github.io/tutorial/