CREATE TEXT SEARCH DICTIONARY

The CREATE TEXT SEARCH DICTIONARY statement defines a text search dictionary — the analyzer that turns raw text into the tokens stored in an inverted index. The dictionary controls every stage of that transformation: how text is split into tokens, how each token is normalized (case folding, accent folding, stemming) and which extra information (term positions, frequencies) is recorded for searching and ranking. The same dictionary is applied both when a column is indexed and when a full-text query runs against that column, so the data and the query are always analyzed the same way.

Every dictionary is built from a single template. A template implements one analysis strategy — splitting text on word boundaries, cutting on a delimiter, emitting character n-grams, filtering stop words and so on — and exposes its own set of options. Templates can also be composed: pipeline chains several analyzers end to end, minhash wraps another analyzer to emit similarity signatures and copy_from derives a variant of an existing dictionary.

Examples

Create a dictionary that lower-cases its input, applies English stemming and stores the term frequencies and positions needed for relevance ranking and phrase search, then attach it to two columns with an inverted index:

Query

CREATE TEXT SEARCH DICTIONARY english_dict (    template = 'text',    locale = 'en_US.UTF-8',    case = 'lower',    stemming = true,    frequency = true,    position = true);
CREATE INDEX idx_docs ON documents    USING inverted (id, title english_dict, body english_dict);

The dictionary is referenced by name in the index column list (title english_dict, body english_dict). Once the index exists, full-text queries against those columns are analyzed with the same dictionary, so a search term matches the indexed tokens even when the surface forms differ:

Query

SELECT id FROM idx_docs WHERE body @@ 'searching';

Result

 id----  1

Because english_dict stems its input, the query term searching is reduced to search and matches every row whose body contains a form of that word. To see exactly how a dictionary tokenizes a string — invaluable when tuning options — pass it to ts_lexize:

Query

SELECT ts_lexize('english_dict', 'running engines');

Result

 ts_lexize------------- {run,engin}

Templates

A dictionary must name exactly one template through the TEMPLATE option. The available templates are grouped below by what they do; follow a link for the options each one accepts.

Text processing

These templates turn human language into searchable tokens.

Template	Description
text	Tokenize into words with stemming, stopwords and accent handling
ngram	Generate character n-grams for fuzzy and substring matching
sparse_ngram	Generate sparse variable-length n-grams for substring search over code and logs
wildcard	Generate boundary-marked n-grams for wildcard and prefix matching
stem	Apply stemming only
norm	Normalize case and accents without tokenization
keyword	Emit the whole input as one verbatim token
segmentation	Segment text by Unicode word boundaries

Splitting & filtering

These templates carve structured text into tokens or refine an existing token stream.

Template	Description
delimiter	Split on a single delimiter
multi_delimiter	Split on multiple delimiters
pattern	Match or split with a regular expression
path_hierarchy	Tokenize a path into its hierarchical prefixes
stopwords	Filter out stop words
collation	Produce collation keys for sorting

Composition

These templates build a dictionary out of other dictionaries.

Template	Description
pipeline	Chain multiple analyzers in sequence
union	Merge the tokens of several analyzers run in parallel
minhash	Generate MinHash signatures with a nested analyzer
copy_from	Copy and override an existing dictionary

Synonyms

These templates expand a token into its synonyms so a search finds related wording.

Template	Description
solr_synonyms	Expand tokens using a Solr-format synonyms map
wordnet_synonyms	Expand tokens using a WordNet synonyms database

Geospatial

These templates index geometries and coordinates for geospatial search.

Template	Description
geojson	Index GeoJSON geometries (points, lines, polygons)
geopoint	Index latitude/longitude points

Machine learning

These templates run a pre-trained model (for example fastText) to emit tokens.

Template	Description
classification	ML-based text classification
nearest_neighbors	ML-based nearest neighbor tokens

Feature flags

Independently of the template, the following flags control how much information the index records about each token. They are all off by default — enable only what your queries need, since each one increases the size of the index.

Flag	Default	Description
FREQUENCY	false	Store term frequency (needed for relevance scoring)
POSITION	false	Store term positions (needed for phrase queries)
NORM	false	Store the normalization factor
OFFSET	false	Store character offsets

Enable FREQUENCY when you rank results by relevance and POSITION when you run phrase or proximity queries. The dictionary in the example above sets both.

See also

• CREATE INDEX — attach a dictionary to a column with an inverted index
• DROP — remove a text search dictionary

Syntax