Adding Synonyms

In this example, we will add synonyms for 'postgresql', 'postgres', and 'pgsql'. Searching for any of these words will return results matching any of the other words.

Create a synonym file with 2 columns - the original word and its synonym.

cat /usr/local/pgsql/share/contrib/english.syn

postgresql pg
postgres pg
pgsql pg

Start a psql session.

psql ts_db

Add an english synonym dictionary to the pg_ts_dict table. Reference the new synonym file and copy some column values from the example synonym dictionary.

INSERT INTO pg_ts_dict (
SELECT 'en_syn', dict_init, '/usr/local/pgsql/share/contrib/english.syn', dict_lexize, 'English Synonyms'
        FROM pg_ts_dict
        WHERE dict_name='synonym');

Update the default configuration in table pg_ts_cfgmap to first try and use the new en_syn dictionary for latin word tokens.

UPDATE pg_ts_cfgmap 
SET dict_name = '{en_syn,en_stem}'
WHERE ts_name = 'default' AND tok_alias in ('lhword', 'lpart_hword', 'lword');

ts_db=# select set_curcfg('default');

 set_curcfg
------------

(1 row)

SELECT to_tsvector('Other names for PostgreSQL are Postgres and PGSQL.');
     
     to_tsvector
---------------------
 'pg':4,6,8 'name':2
(1 row)

SELECT to_tsvector('PostgreSQL') @@ to_tsquery('Postgres') 
UNION ALL
SELECT to_tsvector('PostgreSQL') @@ to_tsquery('PGSQL') 
UNION ALL 
SELECT to_tsvector('PostgreSQL') @@ to_tsquery('psql');
 
 ?column?
----------
 t
 t
 f
(3 rows)