Hunspell dictionaries for full-text search

PostgreSQL uses Dictionaries to configure full-text search in its documents. Managed Service for PostgreSQL clusters come with pre-installed Hunspell spell checker dictionaries supporting multiple languages.

The following examples show how to add an English dictionary.

Adding a dictionaryAdding a dictionary

1. Connect to the database via psql.

2. To see which languages are available, get a list of pre-installed full-text search configurations:

   SELECT cfgname FROM pg_catalog.pg_ts_config;
   

   The result will show configurations named after their respective languages.

3. Create the public.my_config full-text search configuration:

   CREATE TEXT SEARCH CONFIGURATION public.my_config ( COPY = pg_catalog.<configuration> );
   

   In your SQL query, specify the language configuration from the previous step.

4. Create a dictionary called my_dictionary in your database:

   CREATE TEXT SEARCH DICTIONARY my_dictionary (
      TEMPLATE = ispell,
      DictFile = <dictionary_words>,
      AffFile = <affixes>,
      Stopwords = <stop_words>
   );
   

   SQL query parameters:

   • TEMPLATE: Dictionary template. Learn more about Ispell dictionaries here.
   • DictFile: Pre-installed dictionary file.
   • AffFile: Pre-installed affix file containing prefixes, suffixes, and endings for expanding dictionary words.
   • Stopwords: Words to ignore in full-text searches, such as articles, prepositions, and interjections.

   Dictionary and affix files pre-installed in Managed Service for PostgreSQL clusters:

   Language	Dictionary (DictFile)	Affixes (AffFile)
   English	en_gb.dict	en_GB.affix
   Danish	da_dk.dict	da_DK.affix
   Spanish	es_es.dict	es_ES.affix
   Italian	it_it.dict	it_IT.affix
   German	de_de_frami.dict	de_de_frami.affix
   Polish	pl_pl.dict	pl_PL.affix
   Russian	ru_ru.dict	ru_RU.affix
   Ukrainian	uk_ua.dict	uk_UA.affix
   Czech	cs_cz.dict	cs_CZ.affix

   There are also examples of PostgreSQL dictionaries:

   Dictionary (DictFile)	Affixes (AffFile)
   hunspell_sample_long.dict	hunspell_sample_long.affix
   hunspell_sample_num.dict	hunspell_sample_num.affix
   ispell_sample.dict	ispell_sample.affix

   Do not specify the .dict and .affix file extensions in the CREATE TEXT SEARCH DICTIONARY SQL query.

5. Link my_dictionary and other dictionaries to the word token type.

   A token is a search word or phrase. You specify it in the search query and it appears in the full-text search results.

   ALTER TEXT SEARCH CONFIGURATION public.my_config
      ALTER MAPPING FOR word
      WITH my_dictionary,<dictionary_list>;
   

   Use the WITH clause to specify publicly available dictionaries with more extensive vocabulary, such as english_ispell or english_stem. Dictionaries should be ordered in the WITH clause from more specific to more general.

6. Set public.my_config as the default configuration:

   SET default_text_search_config = 'public.my_config';
   

7. Verify that the default configuration is public.my_config:

   SHOW default_text_search_config;
   

   Result:

    default_text_search_config
   ----------------------------
    public.my_config
   (1 row)
   

8. Verify that the full-text search is working:

   SELECT * FROM ts_debug('<token>');
   

   For the token, specify the word that can be found in the documents within your database.

   Result:

    alias |    description    |  token  |       dictionaries       |           dictionary           +   lexemes
   -------+-------------------+---------+--------------------------+--------------------------------+------------
    word  | Word, all letters | <token> | {<dictionaries_used>} | <dictionary_with_the_lexem_found> | {<lexemes>}
   (1 row)
   

   Here, a lexeme is a word that replaces all its morphological variants in full-text searches. For example, if a document in your database contains the words flying, flies, and flew, PostgreSQL will recognize them as a single lexeme, fly.

ExamplesExamples

Adding an English dictionaryAdding an English dictionary

1. Connect to the database via psql.

2. Create the public.my_english_config full-text search configuration:

   CREATE TEXT SEARCH CONFIGURATION public.my_english_config ( COPY = pg_catalog.english );
   

3. Create a dictionary in your database:

   CREATE TEXT SEARCH DICTIONARY english_hunspell (
      TEMPLATE = ispell,
      DictFile = en_gb,
      AffFile = en_GB,
      Stopwords = english
   );
   

4. Link english_hunspell and english_stem dictionaries to the word token type:

   ALTER TEXT SEARCH CONFIGURATION public.my_english_config
      ALTER MAPPING FOR word
      WITH english_hunspell, english_stem;
   

5. Set public.my_english_config as the default configuration:

   SET default_text_search_config = 'public.my_english_config';
   

6. Verify that the default configuration is public.my_english_config:

   SHOW default_text_search_config;
   

   Result:

    default_text_search_config
   ----------------------------
    public.my_english_config
   (1 row)
   

7. Verify that the full-text search is working:

   SELECT * FROM ts_debug('<token>');
   

   For the token, specify the word that can be found in the documents within your database.

   Result:

    alias |    description    |  token  |          dictionaries           |    dictionary    +   lexemes
   -------+-------------------+---------+---------------------------------+------------------+------------
    word  | Word, all letters | <token> | {english_hunspell,english_stem} | english_hunspell | {<lexemes>}
   (1 row)