Sign up ×
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

3 Answers 3

up vote 4 down vote accepted

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
  2. Select all of the text, copy it, and paste it to Text Mechanic: Add a line break at the end.
  3. Open
  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 , 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
+10, great answer. –  George Chalhoub May 4 at 15:47

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 -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*
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.