Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 1 down vote accepted

This example uses the Canadian English dictionary, but you can try it with others as well. These steps are 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.com/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.

share|improve this answer

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.

share|improve this answer
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
@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 posting your answer, you agree to the privacy policy and terms of service.

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