Postgres can use Ispell-compatible dictionaries in text search, but does not provide the required files.


I've written the following script to install an en_us dictionary on Ubuntu 14.04 running PostgreSQL 9.4. It should be fairly easy to modify for most situations.

cd /usr/share/postgresql/9.4/tsearch_data

wget http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_US.dic
wget http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_US.dic_delta
wget http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_US.aff -O en_us.affix

# Remove first line
sed -i 1d en_US.dic

# Concat the dic and dic_delta, sort alphabetically and remove the leading blank line (leaves the ending newline intact)
cat en_US.dic en_US.dic_delta | sort > en_us.dict
sed -i 1d en_us.dict

# Set permissions
chown -R postgres:postgres *

sudo -u postgres psql -c "CREATE TEXT SEARCH DICTIONARY ispell_en_us (template  = ispell, dictfile = en_us, afffile = en_us, stopwords = english);"

# Clean up source files
rm en_US*

This example uses the Canadian English dictionary, but you can try it with others as well.

These are the steps needed for Windows:

  1. Open http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_CA.dic
  2. Select all of the text, copy it, and paste it to Text Mechanic: http://textmechanic.co/Sort-Text-Lines.html. Add a line break at the end.
  3. Open http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_CA.dic_delta
  4. Select all of the text, copy it, and paste it below the previously pasted text in Text Mechanic.
  5. Scroll to the top, and select and cut the first line (should be a five digit number), and get rid of the line break.
  6. Click the Alphabetical button, and wait for the text to sort.
  7. Select all of the text and copy it to the clipboard
  8. Open Windows Notepad as an administrator
  9. Paste the text from Step 7 into Notepad
  10. Save the file as en_ca.dict (with UTF-8 encoding) to your Postgres text search folder. Mine is C:\Program Files\PostgreSQL\9.3\share\tsearch_data .
  11. Open http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_CA.aff , select all, copy, and paste to Notepad. Save the file as en_ca.affix to your Postgres text search folder.

In PgAdmin, run the following SQL:

create text search dictionary ispell_en_ca (
  template  =   ispell,
  dictfile  =   en_ca,
  afffile   =   en_ca,
  stopwords =   english

--make sure it works:
select * from ts_lexize('ispell_en_ca', 'colours');


You will need to create a new text search configuration to use the dictionary.


This is covered in detail in the PostgreSQL manual, under ISpell Dictionaries.

Essentially you just CREATE TEXT SEARCH DICTIONARY (...) with the dictionary file name, stopword list, etc.

  • It's a little tricky actually . Manual points to dictionary wiki which goes nowhere. Hard to find good list of files for many langs. Files encoded in iso but need to be in utf. Need to chg file extensions. Answered my own question so it's easy to find if I need to look it up again. – Neil McGuigan Jan 20 '14 at 4:48
  • 1
    @NeilMcGuigan In that case, it would be greatly appreciated if you were able to propose an update to the documentation on the mailing list, to make this easier for future users. – Craig Ringer Jan 20 '14 at 5:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.