Advanced Full-Text Search

CREATE TEXT SEARCH DICTIONARY my_dict (
   TEMPLATE = snowball,
   Language = english,
   StopWords = english);

CREATE TEXT SEARCH DICTIONARY simple_lc (TEMPLATE = simple);
SELECT ts_lexize('simple_lc', 'Running');   -- {running}

SELECT ts_lexize('english_stem', 'jumping');   -- {jump}

-- $SHAREDIR/tsearch_data/my_syn.syn:  postgres postgresql
CREATE TEXT SEARCH DICTIONARY my_syn (TEMPLATE = synonym, SYNONYMS = my_syn);

CREATE TEXT SEARCH DICTIONARY my_thes (
   TEMPLATE = thesaurus,
   DictFile = my_thes,
   Dictionary = english_stem);

SELECT * FROM articles
 WHERE tsv @@ (to_tsquery('postgres') && !!to_tsquery('mysql'));

SELECT to_tsvector('quick brown fox') @@ phraseto_tsquery('quick brown');
SELECT to_tsvector('foo bar baz') @@ tsquery('foo <2> baz');     -- distance

SELECT * FROM articles WHERE tsv @@ to_tsquery('postgr:*');

UPDATE articles SET tsv =
   setweight(to_tsvector(title), 'A') ||
   setweight(to_tsvector(body),  'B');

SELECT * FROM articles
 WHERE tsv @@ to_tsquery('postgres:A');     -- only title hits

ALTER TABLE articles ADD COLUMN tsv tsvector
   GENERATED ALWAYS AS (
      setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
      setweight(to_tsvector('english', coalesce(body ,'')), 'B')
   ) STORED;
CREATE INDEX articles_tsv_idx ON articles USING gin (tsv);