PostgreSQL – How to Configure PG Text Search Dictionary

google-cloud-sqlpostgresql

I want to add terms to a text search dictionary without access to the file system shares/tsearch_data (specifically for google cloud SQL).

Is there a syntax to do this via SQL?

All I have found is "ALTER TEXT SEARCH DICTIONARY.." which can configure how a dictionary works, but cannot be used to add or remove lines in the dictionary .rules/.syn/.dict file.

Best Answer

By design (it's hardcoded) tsearch_data directory is in SHAREDIR. If you want to change SHAREDIR you should use your own compiled version of PostgreSQL. If you are interested, you can read the documentations' Installation from Source Code section and If you still want to change tsearch_data, well PostgreSQL is opensource, you can change Makefile and ts_utils.c and again have your own version of PostgreSQL by compiling it.

You can also use symbolic link.

Related Solutions

PostgreSQL Full-Text Search – How to Use an Ispell Dictionary with Postgres Text Search

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.

#!/bin/bash
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*

Postgresql – Error during pg_restore: text search dictionary “unaccent” does not exist

It came out that there was a stupid hack, unaccent function was marked immutable and there was an index that used this unaccent function. Dropping the index and recreating it after backup did the trick.

Do not use hacks, folks :)

Best Answer

Related Solutions

PostgreSQL Full-Text Search – How to Use an Ispell Dictionary with Postgres Text Search

Postgresql – Error during pg_restore: text search dictionary “unaccent” does not exist

Related Question