Full Text Indexes in Postgresql

Chapter 12.
Full Text Search
12.1. Introduction
Full Text Searching (or just text search) provides the capability to identify natural-language documents
that satisfy a query, and optionally to sort them by relevance to the query. The most common type
of search is to find all documents containing given query terms and return them in order of their
similarity to the query. Notions of query and similarity are very flexible and depend on the
specific application. The simplest search considers query as a set of words and similarity as the
frequency of query words in the document.
Textual search operators have existed in databases for years. PostgreSQL has ~, ~*, LIKE, and ILIKE
operators for textual data types, but they lack many essential properties required by modern informa-
tion systems:
• There is no linguistic support, even for English. Regular expressions are not sufficient because
they cannot easily handle derived words, e.g., satisfies and satisfy. You might miss docu-
ments that contain satisfies, although you probably would like to find them when searching
for satisfy. It is possible to use OR to search for multiple derived forms, but this is tedious and
error-prone (some words can have several thousand derivatives).
• They provide no ordering (ranking) of search results, which makes them ineffective when thousands
of matching documents are found.
• They tend to be slow because there is no index support, so they must process all documents for
every search.
Full text indexing allows documents to be preprocessed and an index saved for later rapid searching.
Preprocessing includes:
Parsing documents into tokens. It is useful to identify various classes of tokens, e.g., numbers,
words, complex words, email addresses, so that they can be processed differently. In principle token
classes depend on the specific application, but for most purposes it is adequate to use a predefined
set of classes. PostgreSQL uses a parser to perform this step. A standard parser is provided, and
custom parsers can be created for specific needs.
Converting tokens into lexemes. A lexeme is a string, just like a token, but it has been normalized
so that different forms of the same word are made alike. For example, normalization almost always
includes folding upper-case letters to lower-case, and often involves removal of suffixes (such as s
or es in English). This allows searches to find variant forms of the same word, without tediously
entering all the possible variants. Also, this step typically eliminates stop words, which are words
that are so common that they are useless for searching. (In short, then, tokens are raw fragments of
the document text, while lexemes are words that are believed useful for indexing and searching.)
PostgreSQL uses dictionaries to perform this step. Various standard dictionaries are provided, and
custom ones can be created for specific needs.
Storing preprocessed documents optimized for searching. For example, each document can be rep-
resented as a sorted array of normalized lexemes. Along with the lexemes it is often desirable to
store positional information to use for proximity ranking, so that a document that contains a more
“dense” region of query words is assigned a higher rank than one with scattered query words.
Dictionaries allow fine-grained control over how tokens are normalized. With appropriate dictionaries,
you can:
291
Chapter 12. Full Text Search
• Define stop words that should not be indexed.

• Map synonyms to a single word using Ispell.
• Map phrases to a single word using a thesaurus.
• Map different variations of a word to a canonical form using an Ispell dictionary.
• Map different variations of a word to a canonical form using Snowball stemmer rules.
A data type tsvector is provided for storing preprocessed documents, along with a type tsquery
for representing processed queries (Section 8.11). There are many functions and operators available
for these data types (Section 9.13), the most important of which is the match operator @@, which we
introduce in Section 12.1.2. Full text searches can be accelerated using indexes (Section 12.9).
12.1.1. What Is a Document?

A document is the unit of searching in a full text search system; for example, a magazine article
or email message. The text search engine must be able to parse documents and store associations
of lexemes (key words) with their parent document. Later, these associations are used to search for
documents that contain query words.
For searches within PostgreSQL, a document is normally a textual field within a row of a database
table, or possibly a combination (concatenation) of such fields, perhaps stored in several tables or
obtained dynamically. In other words, a document can be constructed from different parts for indexing
and it might not be stored anywhere as a whole. For example:
SELECT title || ’ ’ || author || ’ ’ || abstract || ’ ’ || body AS document

FROM messages
WHERE mid = 12;
SELECT m.title || ’ ’ || m.author || ’ ’ || m.abstract || ’ ’ || d.body AS document

FROM messages m, docs d
WHERE mid = did AND mid = 12;
Note: Actually, in these example queries, coalesce should be used to prevent a single NULL
attribute from causing a NULL result for the whole document.
Another possibility is to store the documents as simple text files in the file system. In this case, the
database can be used to store the full text index and to execute searches, and some unique identifier
can be used to retrieve the document from the file system. However, retrieving files from outside the
database requires superuser permissions or special function support, so this is usually less convenient
than keeping all the data inside PostgreSQL. Also, keeping everything inside the database allows easy
access to document metadata to assist in indexing and display.
For text search purposes, each document must be reduced to the preprocessed tsvector format.
Searching and ranking are performed entirely on the tsvector representation of a document — the
original text need only be retrieved when the document has been selected for display to a user. We
therefore often speak of the tsvector as being the document, but of course it is only a compact
representation of the full document.
292
12.1.2. Basic Text Matching

Full text searching in PostgreSQL is based on the match operator @@, which returns true if a
tsvector (document) matches a tsquery (query). It doesn’t matter which data type is written first:
SELECT ’a fat cat sat on a mat and ate a fat rat’::tsvector @@ ’cat & rat’::tsquery;
?column?
----------
t
SELECT ’fat & cow’::tsquery @@ ’a fat cat sat on a mat and ate a fat rat’::tsvector;
?column?
----------
f
As the above example suggests, a tsquery is not just raw text, any more than a tsvector is. A
tsquery contains search terms, which must be already-normalized lexemes, and may combine mul-
tiple terms using AND, OR, and NOT operators. (For details see Section 8.11.) There are functions
to_tsquery and plainto_tsquery that are helpful in converting user-written text into a proper
tsquery, for example by normalizing words appearing in the text. Similarly, to_tsvector is used
to parse and normalize a document string. So in practice a text search match would look more like
this:
SELECT to_tsvector(’fat cats ate fat rats’) @@ to_tsquery(’fat & rat’);

?column?
----------
t
Observe that this match would not succeed if written as
SELECT ’fat cats ate fat rats’::tsvector @@ to_tsquery(’fat & rat’);

?column?
----------
f
since here no normalization of the word rats will occur. The elements of a tsvector are lexemes,
which are assumed already normalized, so rats does not match rat.
The @@ operator also supports text input, allowing explicit conversion of a text string to tsvector
or tsquery to be skipped in simple cases. The variants available are:
tsvector @@ tsquery
tsquery @@ tsvector
text @@ tsquery
text @@ text
The first two of these we saw already. The form text @@ tsquery is equivalent to to_tsvector(x)
@@ y. The form text @@ text is equivalent to to_tsvector(x) @@ plainto_tsquery(y).
293
12.1.3. Configurations
The above are all simple text search examples. As mentioned before, full text search functionality
includes the ability to do many more things: skip indexing certain words (stop words), process syn-
onyms, and use sophisticated parsing, e.g., parse based on more than just white space. This function-
ality is controlled by text search configurations. PostgreSQL comes with predefined configurations
for many languages, and you can easily create your own configurations. (psql’s \dF command shows
all available configurations.)
During installation an appropriate configuration is selected and default_text_search_config is set ac-
cordingly in postgresql.conf. If you are using the same text search configuration for the entire
cluster you can use the value in postgresql.conf. To use different configurations throughout the
cluster but the same configuration within any one database, use ALTER DATABASE ... SET. Other-
wise, you can set default_text_search_config in each session.
Each text search function that depends on a configuration has an optional regconfig argument, so
that the configuration to use can be specified explicitly. default_text_search_config is used
only when this argument is omitted.
To make it easier to build custom text search configurations, a configuration is built up from sim-
pler database objects. PostgreSQL’s text search facility provides four types of configuration-related
database objects:
• Text search parsers break documents into tokens and classify each token (for example, as words or
numbers).
• Text search dictionaries convert tokens to normalized form and reject stop words.
• Text search templates provide the functions underlying dictionaries. (A dictionary simply specifies
a template and a set of parameters for the template.)
• Text search configurations select a parser and a set of dictionaries to use to normalize the tokens
produced by the parser.
Text search parsers and templates are built from low-level C functions; therefore it requires C pro-
gramming ability to develop new ones, and superuser privileges to install one into a database. (There
are examples of add-on parsers and templates in the contrib/ area of the PostgreSQL distribution.)
Since dictionaries and configurations just parameterize and connect together some underlying parsers
and templates, no special privilege is needed to create a new dictionary or configuration. Examples of
creating custom dictionaries and configurations appear later in this chapter.
12.2. Tables and Indexes

The examples in the previous section illustrated full text matching using simple constant strings. This
section shows how to search table data, optionally using indexes.
12.2.1. Searching a Table

It is possible to do a full text search without an index. A simple query to print the title of each row
that contains the word friend in its body field is:
SELECT title
FROM pgweb
WHERE to_tsvector(’english’, body) @@ to_tsquery(’english’, ’friend’);
294
This will also find related words such as friends and friendly, since all these are reduced to the
same normalized lexeme.
The query above specifies that the english configuration is to be used to parse and normalize the
strings. Alternatively we could omit the configuration parameters:
SELECT title
FROM pgweb
WHERE to_tsvector(body) @@ to_tsquery(’friend’);
This query will use the configuration set by default_text_search_config.

A more complex example is to select the ten most recent documents that contain create and table
in the title or body:
SELECT title
FROM pgweb
WHERE to_tsvector(title || ’ ’ || body) @@ to_tsquery(’create & table’)
ORDER BY last_mod_date DESC
LIMIT 10;
For clarity we omitted the coalesce function calls which would be needed to find rows that contain
NULL in one of the two fields.
Although these queries will work without an index, most applications will find this approach too
slow, except perhaps for occasional ad-hoc searches. Practical use of text searching usually requires
creating an index.
12.2.2. Creating Indexes

We can create a GIN index (Section 12.9) to speed up text searches:
CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector(’english’, body));
Notice that the 2-argument version of to_tsvector is used. Only text search functions that specify
a configuration name can be used in expression indexes (Section 11.7). This is because the index
contents must be unaffected by default_text_search_config. If they were affected, the index contents
might be inconsistent because different entries could contain tsvectors that were created with dif-
ferent text search configurations, and there would be no way to guess which was which. It would be
impossible to dump and restore such an index correctly.
Because the two-argument version of to_tsvector was used in the index above, only a query ref-
erence that uses the 2-argument version of to_tsvector with the same configuration name will use
that index. That is, WHERE to_tsvector(’english’, body) @@ ’a & b’ can use the index,
but WHERE to_tsvector(body) @@ ’a & b’ cannot. This ensures that an index will be used only
with the same configuration used to create the index entries.
It is possible to set up more complex expression indexes wherein the configuration name is specified
by another column, e.g.:
CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector(config_name, body));
where config_name is a column in the pgweb table. This allows mixed configurations in the same
index while recording which configuration was used for each index entry. This would be useful, for
example, if the document collection contained documents in different languages. Again, queries that
are meant to use the index must be phrased to match, e.g., WHERE to_tsvector(config_name,
body) @@ ’a & b’.
295
Indexes can even concatenate columns:
CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector(’english’, title || ’ ’ || body));
Another approach is to create a separate tsvector column to hold the output of to_tsvector. This
example is a concatenation of title and body, using coalesce to ensure that one field will still be
indexed when the other is NULL:
ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;

UPDATE pgweb SET textsearchable_index_col =
to_tsvector(’english’, coalesce(title,”) || ’ ’ || coalesce(body,”));
Then we create a GIN index to speed up the search:
CREATE INDEX textsearch_idx ON pgweb USING gin(textsearchable_index_col);
Now we are ready to perform a fast full text search:
SELECT title
FROM pgweb
WHERE textsearchable_index_col @@ to_tsquery(’create & table’)
ORDER BY last_mod_date DESC
LIMIT 10;
When using a separate column to store the tsvector representation, it is necessary to create a trigger
to keep the tsvector column current anytime title or body changes. Section 12.4.3 explains how
to do that.
One advantage of the separate-column approach over an expression index is that it is not necessary
to explicitly specify the text search configuration in queries in order to make use of the index. As
shown in the example above, the query can depend on default_text_search_config. Another
advantage is that searches will be faster, since it will not be necessary to redo the to_tsvector calls
to verify index matches. (This is more important when using a GiST index than a GIN index; see
Section 12.9.) The expression-index approach is simpler to set up, however, and it requires less disk
space since the tsvector representation is not stored explicitly.
12.3. Controlling Text Search

To implement full text searching there must be a function to create a tsvector from a document and
a tsquery from a user query. Also, we need to return results in a useful order, so we need a function
that compares documents with respect to their relevance to the query. It’s also important to be able to
display the results nicely. PostgreSQL provides support for all of these functions.
12.3.1. Parsing Documents

PostgreSQL provides the function to_tsvector for converting a document to the tsvector data
type.
to_tsvector([ config regconfig, ] document text) returns tsvector
296
to_tsvector parses a textual document into tokens, reduces the tokens to lexemes, and returns a
tsvector which lists the lexemes together with their positions in the document. The document is
processed according to the specified or default text search configuration. Here is a simple example:
SELECT to_tsvector(’english’, ’a fat cat sat on a mat - it ate a fat rats’);

to_tsvector
-----------------------------------------------------
’ate’:9 ’cat’:3 ’fat’:2,11 ’mat’:7 ’rat’:12 ’sat’:4
In the example above we see that the resulting tsvector does not contain the words a, on, or it, the
word rats became rat, and the punctuation sign - was ignored.
The to_tsvector function internally calls a parser which breaks the document text into tokens and
assigns a type to each token. For each token, a list of dictionaries (Section 12.6) is consulted, where
the list can vary depending on the token type. The first dictionary that recognizes the token emits one
or more normalized lexemes to represent the token. For example, rats became rat because one of the
dictionaries recognized that the word rats is a plural form of rat. Some words are recognized as stop
words (Section 12.6.1), which causes them to be ignored since they occur too frequently to be useful
in searching. In our example these are a, on, and it. If no dictionary in the list recognizes the token
then it is also ignored. In this example that happened to the punctuation sign - because there are in
fact no dictionaries assigned for its token type (Space symbols), meaning space tokens will never be
indexed. The choices of parser, dictionaries and which types of tokens to index are determined by the
selected text search configuration (Section 12.7). It is possible to have many different configurations in
the same database, and predefined configurations are available for various languages. In our example
we used the default configuration english for the English language.
The function setweight can be used to label the entries of a tsvector with a given weight, where
a weight is one of the letters A, B, C, or D. This is typically used to mark entries coming from different
parts of a document, such as title versus body. Later, this information can be used for ranking of search
results.
Because to_tsvector(NULL) will return NULL, it is recommended to use coalesce whenever a
field might be null. Here is the recommended method for creating a tsvector from a structured
document:
UPDATE tt SET ti =
setweight(to_tsvector(coalesce(title,”)), ’A’) ||
setweight(to_tsvector(coalesce(keyword,”)), ’B’) ||
setweight(to_tsvector(coalesce(abstract,”)), ’C’) ||
setweight(to_tsvector(coalesce(body,”)), ’D’);
Here we have used setweight to label the source of each lexeme in the finished tsvector, and
then merged the labeled tsvector values using the tsvector concatenation operator ||. (Section
12.4.1 gives details about these operations.)
12.3.2. Parsing Queries

PostgreSQL provides the functions to_tsquery and plainto_tsquery for converting a query to
the tsquery data type. to_tsquery offers access to more features than plainto_tsquery, but is
less forgiving about its input.
to_tsquery([ config regconfig, ] querytext text) returns tsquery
297
to_tsquery creates a tsquery value from querytext, which must consist of single tokens sep-
arated by the Boolean operators & (AND), | (OR) and ! (NOT). These operators can be grouped
using parentheses. In other words, the input to to_tsquery must already follow the general rules
for tsquery input, as described in Section 8.11. The difference is that while basic tsquery input
takes the tokens at face value, to_tsquery normalizes each token to a lexeme using the specified or
default configuration, and discards any tokens that are stop words according to the configuration. For
example:
SELECT to_tsquery(’english’, ’The & Fat & Rats’);

to_tsquery
---------------
’fat’ & ’rat’
As in basic tsquery input, weight(s) can be attached to each lexeme to restrict it to match only
tsvector lexemes of those weight(s). For example:
SELECT to_tsquery(’english’, ’Fat | Rats:AB’);

to_tsquery
------------------
’fat’ | ’rat’:AB
Also, * can be attached to a lexeme to specify prefix matching:
SELECT to_tsquery(’supern:*A & star:A*B’);

to_tsquery
--------------------------
’supern’:*A & ’star’:*AB
Such a lexeme will match any word in a tsvector that begins with the given string.
to_tsquery can also accept single-quoted phrases. This is primarily useful when the configuration
includes a thesaurus dictionary that may trigger on such phrases. In the example below, a thesaurus
contains the rule supernovae stars : sn:
SELECT to_tsquery(”’supernovae stars” & !crab’);

to_tsquery
---------------
’sn’ & !’crab’
Without quotes, to_tsquery will generate a syntax error for tokens that are not separated by an
AND or OR operator.
plainto_tsquery([ config regconfig, ] querytext text) returns tsquery
plainto_tsquery transforms unformatted text querytext to tsquery. The text is parsed and nor-
malized much as for to_tsvector, then the & (AND) Boolean operator is inserted between surviving
words.
Example:
SELECT plainto_tsquery(’english’, ’The Fat Rats’);

plainto_tsquery
-----------------
’fat’ & ’rat’
Note that plainto_tsquery cannot recognize Boolean operators, weight labels, or prefix-match
labels in its input:
298
SELECT plainto_tsquery(’english’, ’The Fat & Rats:C’);

plainto_tsquery
---------------------
’fat’ & ’rat’ & ’c’
Here, all the input punctuation was discarded as being space symbols.
12.3.3. Ranking Search Results

Ranking attempts to measure how relevant documents are to a particular query, so that when there are
many matches the most relevant ones can be shown first. PostgreSQL provides two predefined ranking
functions, which take into account lexical, proximity, and structural information; that is, they consider
how often the query terms appear in the document, how close together the terms are in the document,
and how important is the part of the document where they occur. However, the concept of relevancy
is vague and very application-specific. Different applications might require additional information
for ranking, e.g., document modification time. The built-in ranking functions are only examples. You
can write your own ranking functions and/or combine their results with additional factors to fit your
specific needs.
The two ranking functions currently available are:
ts_rank([ weights float4[], ] vector tsvector,

query tsquery [, normalization integer ]) returns float4
Ranks vectors based on the frequency of their matching lexems.
ts_rank_cd([ weights float4[], ] vector tsvector,

query tsquery [, normalization integer ]) returns float4
This function computes the cover density ranking for the given document vector and query,
as described in Clarke, Cormack, and Tudhope’s "Relevance Ranking for One to Three Term
Queries" in the journal "Information Processing and Management", 1999.
This function requires positional information in its input. Therefore it will not work on “stripped”
tsvector values — it will always return zero.
For both these functions, the optional weights argument offers the ability to weigh word instances
more or less heavily depending on how they are labeled. The weight arrays specify how heavily to
weigh each category of word, in the order:
{D-weight, C-weight, B-weight, A-weight}
If no weights are provided, then these defaults are used:
{0.1, 0.2, 0.4, 1.0}
Typically weights are used to mark words from special areas of the document, like the title or an initial
abstract, so they can be treated with more or less importance than words in the document body.
Since a longer document has a greater chance of containing a query term it is reasonable to take
into account document size, e.g., a hundred-word document with five instances of a search word is
probably more relevant than a thousand-word document with five instances. Both ranking functions
take an integer normalization option that specifies whether and how a document’s length should
impact its rank. The integer option controls several behaviors, so it is a bit mask: you can specify one
or more behaviors using | (for example, 2|4).
299
• 0 (the default) ignores the document length

• 1 divides the rank by 1 + the logarithm of the document length
• 2 divides the rank by the document length
• 4 divides the rank by the mean harmonic distance between extents (this is implemented only by
ts_rank_cd)
• 8 divides the rank by the number of unique words in document
• 16 divides the rank by 1 + the logarithm of the number of unique words in document
• 32 divides the rank by itself + 1
If more than one flag bit is specified, the transformations are applied in the order listed.
It is important to note that the ranking functions do not use any global information, so it is impos-
sible to produce a fair normalization to 1% or 100% as sometimes desired. Normalization option 32
(rank/(rank+1)) can be applied to scale all ranks into the range zero to one, but of course this is
just a cosmetic change; it will not affect the ordering of the search results.
Here is an example that selects only the ten highest-ranked matches:
SELECT title, ts_rank_cd(textsearch, query) AS rank

FROM apod, to_tsquery(’neutrino|(dark & matter)’) query
WHERE query @@ textsearch
ORDER BY rank DESC
LIMIT 10;
title | rank
-----------------------------------------------+----------
Neutrinos in the Sun | 3.1
The Sudbury Neutrino Detector | 2.4
A MACHO View of Galactic Dark Matter | 2.01317
Hot Gas and Dark Matter | 1.91171
The Virgo Cluster: Hot Plasma and Dark Matter | 1.90953
Rafting for Solar Neutrinos | 1.9
NGC 4650A: Strange Galaxy and Dark Matter | 1.85774
Ice Fishing for Cosmic Neutrinos | 1.6
Weak Lensing Distorts the Universe | 0.818218
This is the same example using normalized ranking:
SELECT title, ts_rank_cd(textsearch, query, 32 /* rank/(rank+1) */ ) AS rank

FROM apod, to_tsquery(’neutrino|(dark & matter)’) query
WHERE query @@ textsearch
ORDER BY rank DESC
LIMIT 10;
title | rank
-----------------------------------------------+-------------------
Neutrinos in the Sun | 0.756097569485493
The Sudbury Neutrino Detector | 0.705882361190954
A MACHO View of Galactic Dark Matter | 0.668123210574724
The Virgo Cluster: Hot Plasma and Dark Matter | 0.656301290640973
Rafting for Solar Neutrinos | 0.655172410958162
NGC 4650A: Strange Galaxy and Dark Matter | 0.650072921219637
Ice Fishing for Cosmic Neutrinos | 0.615384618911517
Weak Lensing Distorts the Universe | 0.450010798361481
300
Ranking can be expensive since it requires consulting the tsvector of each matching document,
which can be I/O bound and therefore slow. Unfortunately, it is almost impossible to avoid since
practical queries often result in large numbers of matches.
12.3.4. Highlighting Results

To present search results it is ideal to show a part of each document and how it is related to the query.
Usually, search engines show fragments of the document with marked search terms. PostgreSQL
provides a function ts_headline that implements this functionality.
ts_headline([ config regconfig, ] document text, query tsquery [, options text ]) returns text
ts_headline accepts a document along with a query, and returns an excerpt from the document in
which terms from the query are highlighted. The configuration to be used to parse the document can
be specified by config ; if config is omitted, the default_text_search_config configuration
is used.
If an options string is specified it must consist of a comma-separated list of one or more
option=value pairs. The available options are:
• StartSel, StopSel: the strings with which to delimit query words appearing in the document, to
distinguish them from other excerpted words. You must double-quote these strings if they contain
spaces or commas.
• MaxWords, MinWords: these numbers determine the longest and shortest headlines to output.
• ShortWord: words of this length or less will be dropped at the start and end of a headline. The
default value of three eliminates common English articles.
• HighlightAll: Boolean flag; if true the whole document will be used as the headline, ignoring
the preceding three parameters.
• MaxFragments: maximum number of text excerpts or fragments to display. The default value of
zero selects a non-fragment-oriented headline generation method. A value greater than zero selects
fragment-based headline generation. This method finds text fragments with as many query words
as possible and stretches those fragments around the query words. As a result query words are
close to the middle of each fragment and have words on each side. Each fragment will be of at
most MaxWords and words of length ShortWord or less are dropped at the start and end of each
fragment. If not all query words are found in the document, then a single fragment of the first
MinWords in the document will be displayed.
• FragmentDelimiter: When more than one fragment is displayed, the fragments will be separated
by this string.
Any unspecified options receive these defaults:
StartSel=, StopSel=,
MaxWords=35, MinWords=15, ShortWord=3, HighlightAll=FALSE,
MaxFragments=0, FragmentDelimiter=" ... "
For example:
SELECT ts_headline(’english’,
’The most common type of search
is to find all documents containing given query terms
and return them in order of their similarity to the
query.’,
301
to_tsquery(’query & similarity’));

ts_headline
------------------------------------------------------------
containing given query terms
and return them in order of their similarity to the
query.
SELECT ts_headline(’english’,
’The most common type of search
is to find all documents containing given query terms
and return them in order of their similarity to the
query.’,
to_tsquery(’query & similarity’),
’StartSel = <, StopSel = >’);
ts_headline
-------------------------------------------------------
containing given <query> terms
and return them in order of their <similarity> to the
<query>.
ts_headline uses the original document, not a tsvector summary, so it can be slow and should
be used with care. A typical mistake is to call ts_headline for every matching document when only
ten documents are to be shown. SQL subqueries can help; here is an example:
SELECT id, ts_headline(body, q), rank

FROM (SELECT id, body, q, ts_rank_cd(ti, q) AS rank
FROM apod, to_tsquery(’stars’) q
WHERE ti @@ q
ORDER BY rank DESC
LIMIT 10) AS foo;
12.4. Additional Features

This section describes additional functions and operators that are useful in connection with text search.
12.4.1. Manipulating Documents

Section 12.3.1 showed how raw textual documents can be converted into tsvector values. Post-
greSQL also provides functions and operators that can be used to manipulate documents that are
already in tsvector form.
tsvector || tsvector
The tsvector concatenation operator returns a vector which combines the lexemes and posi-
tional information of the two vectors given as arguments. Positions and weight labels are retained
during the concatenation. Positions appearing in the right-hand vector are offset by the largest
position mentioned in the left-hand vector, so that the result is nearly equivalent to the result
of performing to_tsvector on the concatenation of the two original document strings. (The
302
equivalence is not exact, because any stop-words removed from the end of the left-hand argu-
ment will not affect the result, whereas they would have affected the positions of the lexemes in
the right-hand argument if textual concatenation were used.)
One advantage of using concatenation in the vector form, rather than concatenating text before
applying to_tsvector, is that you can use different configurations to parse different sections
of the document. Also, because the setweight function marks all lexemes of the given vector
the same way, it is necessary to parse the text and do setweight before concatenating if you
want to label different parts of the document with different weights.
setweight(vector tsvector, weight "char") returns tsvector
setweight returns a copy of the input vector in which every position has been labeled with the
given weight, either A, B, C, or D. (D is the default for new vectors and as such is not displayed on
output.) These labels are retained when vectors are concatenated, allowing words from different
parts of a document to be weighted differently by ranking functions.
Note that weight labels apply to positions, not lexemes. If the input vector has been stripped of
positions then setweight does nothing.
length(vector tsvector) returns integer
Returns the number of lexemes stored in the vector.
strip(vector tsvector) returns tsvector
Returns a vector which lists the same lexemes as the given vector, but which lacks any position
or weight information. While the returned vector is much less useful than an unstripped vector
for relevance ranking, it will usually be much smaller.
12.4.2. Manipulating Queries

Section 12.3.2 showed how raw textual queries can be converted into tsquery values. PostgreSQL
also provides functions and operators that can be used to manipulate queries that are already in
tsquery form.
tsquery && tsquery
Returns the AND-combination of the two given queries.
tsquery || tsquery
Returns the OR-combination of the two given queries.
!! tsquery
Returns the negation (NOT) of the given query.
numnode(query tsquery) returns integer
Returns the number of nodes (lexemes plus operators) in a tsquery. This function is useful to
determine if the query is meaningful (returns > 0), or contains only stop words (returns 0).
Examples:
SELECT numnode(plainto_tsquery(’the any’));
NOTICE: query contains only stopword(s) or doesn’t contain lexeme(s), ignored
numnode
---------
0
303
SELECT numnode(’foo & bar’::tsquery);

numnode
---------
3
querytree(query tsquery) returns text
Returns the portion of a tsquery that can be used for searching an index. This function is useful
for detecting unindexable queries, for example those containing only stop words or only negated
terms. For example:
SELECT querytree(to_tsquery(’!defined’));
querytree
-----------
12.4.2.1. Query Rewriting

The ts_rewrite family of functions search a given tsquery for occurrences of a target subquery,
and replace each occurrence with a substitute subquery. In essence this operation is a tsquery-
specific version of substring replacement. A target and substitute combination can be thought of as a
query rewrite rule. A collection of such rewrite rules can be a powerful search aid. For example, you
can expand the search using synonyms (e.g., new york, big apple, nyc, gotham) or narrow the
search to direct the user to some hot topic. There is some overlap in functionality between this feature
and thesaurus dictionaries (Section 12.6.4). However, you can modify a set of rewrite rules on-the-fly
without reindexing, whereas updating a thesaurus requires reindexing to be effective.
ts_rewrite (query tsquery, target tsquery, substitute tsquery) returns tsquery

This form of ts_rewrite simply applies a single rewrite rule: target is replaced by
substitute wherever it appears in query . For example:
SELECT ts_rewrite(’a & b’::tsquery, ’a’::tsquery, ’c’::tsquery);
ts_rewrite
------------
’b’ & ’c’
ts_rewrite (query tsquery, select text) returns tsquery
This form of ts_rewrite accepts a starting query and a SQL select command, which is
given as a text string. The select must yield two columns of tsquery type. For each row of
the select result, occurrences of the first column value (the target) are replaced by the second
column value (the substitute) within the current query value. For example:
CREATE TABLE aliases (t tsquery PRIMARY KEY, s tsquery);
INSERT INTO aliases VALUES(’a’, ’c’);
SELECT ts_rewrite(’a & b’::tsquery, ’SELECT t,s FROM aliases’);

ts_rewrite
------------
’b’ & ’c’
Note that when multiple rewrite rules are applied in this way, the order of application can be
important; so in practice you will want the source query to ORDER BY some ordering key.
Let’s consider a real-life astronomical example. We’ll expand query supernovae using table-driven
rewriting rules:
CREATE TABLE aliases (t tsquery primary key, s tsquery);
304
INSERT INTO aliases VALUES(to_tsquery(’supernovae’), to_tsquery(’supernovae|sn’));
SELECT ts_rewrite(to_tsquery(’supernovae & crab’), ’SELECT * FROM aliases’);

ts_rewrite
---------------------------------
’crab’ & ( ’supernova’ | ’sn’ )
We can change the rewriting rules just by updating the table:
UPDATE aliases
SET s = to_tsquery(’supernovae|sn & !nebulae’)
WHERE t = to_tsquery(’supernovae’);
SELECT ts_rewrite(to_tsquery(’supernovae & crab’), ’SELECT * FROM aliases’);

ts_rewrite
---------------------------------------------
’crab’ & ( ’supernova’ | ’sn’ & !’nebula’ )
Rewriting can be slow when there are many rewriting rules, since it checks every rule for a possi-
ble match. To filter out obvious non-candidate rules we can use the containment operators for the
tsquery type. In the example below, we select only those rules which might match the original
query:
SELECT ts_rewrite(’a & b’::tsquery,

’SELECT t,s FROM aliases WHERE ”a & b”::tsquery @> t’);
ts_rewrite
------------
’b’ & ’c’
12.4.3. Triggers for Automatic Updates

When using a separate column to store the tsvector representation of your documents, it is neces-
sary to create a trigger to update the tsvector column when the document content columns change.
Two built-in trigger functions are available for this, or you can write your own.
tsvector_update_trigger(tsvector_column_name, config_name, text_column_name [, ... ])

tsvector_update_trigger_column(tsvector_column_name, config_column_name, text_column_name [, .
These trigger functions automatically compute a tsvector column from one or more textual
columns, under the control of parameters specified in the CREATE TRIGGER command. An example
of their use is:
CREATE TABLE messages (

title text,
body text,
tsv tsvector
);
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE

ON messages FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(tsv, ’pg_catalog.english’, title, body);
305
INSERT INTO messages VALUES(’title here’, ’the body text is here’);
SELECT * FROM messages;

title | body | tsv
------------+-----------------------+----------------------------
title here | the body text is here | ’bodi’:4 ’text’:5 ’titl’:1
SELECT title, body FROM messages WHERE tsv @@ to_tsquery(’title & body’);
title | body
------------+-----------------------
title here | the body text is here
Having created this trigger, any change in title or body will automatically be reflected into tsv,
without the application having to worry about it.
The first trigger argument must be the name of the tsvector column to be updated. The second
argument specifies the text search configuration to be used to perform the conversion. For
tsvector_update_trigger, the configuration name is simply given as the second trigger
argument. It must be schema-qualified as shown above, so that the trigger behavior will not change
with changes in search_path. For tsvector_update_trigger_column, the second trigger
argument is the name of another table column, which must be of type regconfig. This allows a
per-row selection of configuration to be made. The remaining argument(s) are the names of textual
columns (of type text, varchar, or char). These will be included in the document in the order
given. NULL values will be skipped (but the other columns will still be indexed).
A limitation of these built-in triggers is that they treat all the input columns alike. To process columns
differently — for example, to weight title differently from body — it is necessary to write a custom
trigger. Here is an example using PL/pgSQL as the trigger language:
CREATE FUNCTION messages_trigger() RETURNS trigger AS $$

begin
new.tsv :=
setweight(to_tsvector(’pg_catalog.english’, coalesce(new.title,”)), ’A’) ||
setweight(to_tsvector(’pg_catalog.english’, coalesce(new.body,”)), ’D’);
return new;
end
$$ LANGUAGE plpgsql;
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE

ON messages FOR EACH ROW EXECUTE PROCEDURE messages_trigger();
Keep in mind that it is important to specify the configuration name explicitly when creating
tsvector values inside triggers, so that the column’s contents will not be affected by changes to
default_text_search_config. Failure to do this is likely to lead to problems such as search
results changing after a dump and reload.
12.4.4. Gathering Document Statistics

The function ts_stat is useful for checking your configuration and for finding stop-word candidates.
ts_stat(sqlquery text, [ weights text, ]

OUT word text, OUT ndoc integer,
OUT nentry integer) returns setof record
306
sqlquery is a text value containing an SQL query which must return a single tsvector column.
ts_stat executes the query and returns statistics about each distinct lexeme (word) contained in the
tsvector data. The columns returned are
• word text— the value of a lexeme

• ndoc integer— number of documents (tsvectors) the word occurred in
• nentry integer — total number of occurrences of the word
If weights is supplied, only occurrences having one of those weights are counted.
For example, to find the ten most frequent words in a document collection:
SELECT * FROM ts_stat(’SELECT vector FROM apod’)

ORDER BY nentry DESC, ndoc DESC, word
LIMIT 10;
The same, but counting only word occurrences with weight A or B:
SELECT * FROM ts_stat(’SELECT vector FROM apod’, ’ab’)

ORDER BY nentry DESC, ndoc DESC, word
LIMIT 10;
12.5. Parsers
Text search parsers are responsible for splitting raw document text into tokens and identifying each
token’s type, where the set of possible types is defined by the parser itself. Note that a parser does
not modify the text at all — it simply identifies plausible word boundaries. Because of this limited
scope, there is less need for application-specific custom parsers than there is for custom dictionaries.
At present PostgreSQL provides just one built-in parser, which has been found to be useful for a wide
range of applications.
The built-in parser is named pg_catalog.default. It recognizes 23 token types, shown in Table
12-1.
Table 12-1. Default Parser’s Token Types
Alias Description Example

asciiword Word, all ASCII letters elephant
word Word, all letters mañana
numword Word, letters and digits beta1
asciihword Hyphenated word, all ASCII up-to-date
hword Hyphenated word, all letters lógico-matemática
numhword Hyphenated word, letters and postgresql-beta1
digits
hword_asciipart Hyphenated word part, all postgresql in the context
ASCII postgresql-beta1
307
Alias Description Example

hword_part Hyphenated word part, all lógico or matemática in the
letters context lógico-matemática
hword_numpart Hyphenated word part, letters beta1 in the context
and digits postgresql-beta1
email Email address [email protected]
protocol Protocol head http://
url URL example.com/stuff/index.html
host Host example.com

url_path URL path /stuff/index.html, in the
context of a URL
file File or path name /usr/local/foo.txt, if not
within a URL
sfloat Scientific notation -1.234e56
float Decimal notation -1.234
int Signed integer -1234
uint Unsigned integer 1234
version Version number 8.3.0
tag XML tag <a
href="dictionaries.html">
entity XML entity &

blank Space symbols (any whitespace or punctuation
not otherwise recognized)
Note: The parser’s notion of a “letter” is determined by the database’s locale setting, specifically
lc_ctype. Words containing only the basic ASCII letters are reported as a separate token type,
since it is sometimes useful to distinguish them. In most European languages, token types word
and asciiword should be treated alike.
email does not support all valid email characters as defined by RFC 5322. Specifically, the only
non-alphanumeric characters supported for email user names are period, dash, and underscore.
It is possible for the parser to produce overlapping tokens from the same piece of text. As an example,
a hyphenated word will be reported both as the entire word and as each component:
SELECT alias, description, token FROM ts_debug(’foo-bar-beta1’);

alias | description | token
-----------------+------------------------------------------+---------------
numhword | Hyphenated word, letters and digits | foo-bar-beta1
hword_asciipart | Hyphenated word part, all ASCII | foo
blank | Space symbols | -
hword_asciipart | Hyphenated word part, all ASCII | bar
blank | Space symbols | -
hword_numpart | Hyphenated word part, letters and digits | beta1
This behavior is desirable since it allows searches to work for both the whole compound word and for
components. Here is another instructive example:
308
SELECT alias, description, token FROM ts_debug(’http://example.com/stuff/index.html’);

alias | description | token
----------+---------------+------------------------------
protocol | Protocol head | http://
url | URL | example.com/stuff/index.html
host | Host | example.com
url_path | URL path | /stuff/index.html
12.6. Dictionaries
Dictionaries are used to eliminate words that should not be considered in a search (stop words), and
to normalize words so that different derived forms of the same word will match. A successfully nor-
malized word is called a lexeme. Aside from improving search quality, normalization and removal of
stop words reduce the size of the tsvector representation of a document, thereby improving perfor-
mance. Normalization does not always have linguistic meaning and usually depends on application
semantics.
Some examples of normalization:
• Linguistic - Ispell dictionaries try to reduce input words to a normalized form; stemmer dictionaries
remove word endings
• URL locations can be canonicalized to make equivalent URLs match:
• http://www.pgsql.ru/db/mw/index.html
• http://www.pgsql.ru/db/mw/
• http://www.pgsql.ru/db/../db/mw/index.html
• Color names can be replaced by their hexadecimal values, e.g., red, green, blue, magenta
-> FF0000, 00FF00, 0000FF, FF00FF
• If indexing numbers, we can remove some fractional digits to reduce the range of possible numbers,
so for example 3.14159265359, 3.1415926, 3.14 will be the same after normalization if only two
digits are kept after the decimal point.
A dictionary is a program that accepts a token as input and returns:
• an array of lexemes if the input token is known to the dictionary (notice that one token can produce
more than one lexeme)
• a single lexeme with the TSL_FILTER flag set, to replace the original token with a new token to be
passed to subsequent dictionaries (a dictionary that does this is called a filtering dictionary)
• an empty array if the dictionary knows the token, but it is a stop word
• NULL if the dictionary does not recognize the input token
PostgreSQL provides predefined dictionaries for many languages. There are also several predefined
templates that can be used to create new dictionaries with custom parameters. Each predefined dictio-
nary template is described below. If no existing template is suitable, it is possible to create new ones;
see the contrib/ area of the PostgreSQL distribution for examples.
A text search configuration binds a parser together with a set of dictionaries to process the parser’s
output tokens. For each token type that the parser can return, a separate list of dictionaries is specified
by the configuration. When a token of that type is found by the parser, each dictionary in the list is
309
consulted in turn, until some dictionary recognizes it as a known word. If it is identified as a stop
word, or if no dictionary recognizes the token, it will be discarded and not indexed or searched for.
Normally, the first dictionary that returns a non-NULL output determines the result, and any remaining
dictionaries are not consulted; but a filtering dictionary can replace the given word with a modified
word, which is then passed to subsequent dictionaries.
The general rule for configuring a list of dictionaries is to place first the most narrow, most spe-
cific dictionary, then the more general dictionaries, finishing with a very general dictionary, like a
Snowball stemmer or simple, which recognizes everything. For example, for an astronomy-specific
search (astro_en configuration) one could bind token type asciiword (ASCII word) to a synonym
dictionary of astronomical terms, a general English dictionary and a Snowball English stemmer:
ALTER TEXT SEARCH CONFIGURATION astro_en

ADD MAPPING FOR asciiword WITH astrosyn, english_ispell, english_stem;
A filtering dictionary can be placed anywhere in the list, except at the end where it’d be useless.
Filtering dictionaries are useful to partially normalize words to simplify the task of later dictionaries.
For example, a filtering dictionary could be used to remove accents from accented letters, as is done
by the unaccent module.
12.6.1. Stop Words

Stop words are words that are very common, appear in almost every document, and have no discrim-
ination value. Therefore, they can be ignored in the context of full text searching. For example, every
English text contains words like a and the, so it is useless to store them in an index. However, stop
words do affect the positions in tsvector, which in turn affect ranking:
SELECT to_tsvector(’english’,’in the list of stop words’);

to_tsvector
----------------------------
’list’:3 ’stop’:5 ’word’:6
The missing positions 1,2,4 are because of stop words. Ranks calculated for documents with and
without stop words are quite different:
SELECT ts_rank_cd (to_tsvector(’english’,’in the list of stop words’), to_tsquery(’list &

ts_rank_cd
------------
0.05
SELECT ts_rank_cd (to_tsvector(’english’,’list stop words’), to_tsquery(’list & stop’));

ts_rank_cd
------------
0.1
It is up to the specific dictionary how it treats stop words. For example, ispell dictionaries first
normalize words and then look at the list of stop words, while Snowball stemmers first check the list
of stop words. The reason for the different behavior is an attempt to decrease noise.
310
12.6.2. Simple Dictionary

The simple dictionary template operates by converting the input token to lower case and checking
it against a file of stop words. If it is found in the file then an empty array is returned, causing the
token to be discarded. If not, the lower-cased form of the word is returned as the normalized lexeme.
Alternatively, the dictionary can be configured to report non-stop-words as unrecognized, allowing
them to be passed on to the next dictionary in the list.
Here is an example of a dictionary definition using the simple template:
CREATE TEXT SEARCH DICTIONARY public.simple_dict (

TEMPLATE = pg_catalog.simple,
STOPWORDS = english
);
Here, english is the base name of a file of stop words. The file’s full name will be
$SHAREDIR/tsearch_data/english.stop, where $SHAREDIR means the PostgreSQL
installation’s shared-data directory, often /usr/local/share/postgresql (use pg_config
--sharedir to determine it if you’re not sure). The file format is simply a list of words, one per
line. Blank lines and trailing spaces are ignored, and upper case is folded to lower case, but no other
processing is done on the file contents.
Now we can test our dictionary:
SELECT ts_lexize(’public.simple_dict’,’YeS’);
ts_lexize
-----------
{yes}
SELECT ts_lexize(’public.simple_dict’,’The’);
ts_lexize
-----------
{}
We can also choose to return NULL, instead of the lower-cased word, if it is not found in the stop words
file. This behavior is selected by setting the dictionary’s Accept parameter to false. Continuing the
example:
ALTER TEXT SEARCH DICTIONARY public.simple_dict ( Accept = false );
SELECT ts_lexize(’public.simple_dict’,’YeS’);
ts_lexize
-----------
SELECT ts_lexize(’public.simple_dict’,’The’);
ts_lexize
-----------
{}
With the default setting of Accept = true, it is only useful to place a simple dictionary at the end
of a list of dictionaries, since it will never pass on any token to a following dictionary. Conversely,
Accept = false is only useful when there is at least one following dictionary.
311
Caution
Most types of dictionaries rely on configuration files, such as files of stop words.
These files must be stored in UTF-8 encoding. They will be translated to the
actual database encoding, if that is different, when they are read into the server.
Caution
Normally, a database session will read a dictionary configuration file only once,
when it is first used within the session. If you modify a configuration file and
want to force existing sessions to pick up the new contents, issue an ALTER
TEXT SEARCH DICTIONARY command on the dictionary. This can be a “dummy”
update that doesn’t actually change any parameter values.
12.6.3. Synonym Dictionary

This dictionary template is used to create dictionaries that replace a word with a synonym. Phrases
are not supported (use the thesaurus template (Section 12.6.4) for that). A synonym dictionary can
be used to overcome linguistic problems, for example, to prevent an English stemmer dictionary
from reducing the word ’Paris’ to ’pari’. It is enough to have a Paris paris line in the synonym
dictionary and put it before the english_stem dictionary. For example:
SELECT * FROM ts_debug(’english’, ’Paris’);

alias | description | token | dictionaries | dictionary | lexemes
-----------+-----------------+-------+----------------+--------------+---------
asciiword | Word, all ASCII | Paris | {english_stem} | english_stem | {pari}
CREATE TEXT SEARCH DICTIONARY my_synonym (

TEMPLATE = synonym,
SYNONYMS = my_synonyms
);
ALTER TEXT SEARCH CONFIGURATION english

ALTER MAPPING FOR asciiword
WITH my_synonym, english_stem;
SELECT * FROM ts_debug(’english’, ’Paris’);

-----------+-----------------+-------+---------------------------+------------+---------
asciiword | Word, all ASCII | Paris | {my_synonym,english_stem} | my_synonym | {paris}
The only parameter required by the synonym template is SYNONYMS, which is the base name
of its configuration file — my_synonyms in the above example. The file’s full name will be
$SHAREDIR/tsearch_data/my_synonyms.syn (where $SHAREDIR means the PostgreSQL
installation’s shared-data directory). The file format is just one line per word to be substituted, with
the word followed by its synonym, separated by white space. Blank lines and trailing spaces are
ignored.
The synonym template also has an optional parameter CaseSensitive, which defaults to false.
When CaseSensitive is false, words in the synonym file are folded to lower case, as are input
tokens. When it is true, words and tokens are not folded to lower case, but are compared as-is.
312
An asterisk (*) can be placed at the end of a synonym in the configuration file. This
indicates that the synonym is a prefix. The asterisk is ignored when the entry is used in
to_tsvector(), but when it is used in to_tsquery(), the result will be a query item with
the prefix match marker (see Section 12.3.2). For example, suppose we have these entries in
$SHAREDIR/tsearch_data/synonym_sample.syn:
postgres pgsql
postgresql pgsql
postgre pgsql
gogle googl
indices index*
Then we will get these results:
mydb=# CREATE TEXT SEARCH DICTIONARY syn (template=synonym, synonyms=’synonym_sample’);

mydb=# SELECT ts_lexize(’syn’,’indices’);
ts_lexize
-----------
{index}
(1 row)
mydb=# CREATE TEXT SEARCH CONFIGURATION tst (copy=simple);

mydb=# ALTER TEXT SEARCH CONFIGURATION tst ALTER MAPPING FOR asciiword WITH syn;
mydb=# SELECT to_tsvector(’tst’,’indices’);
to_tsvector
-------------
’index’:1
(1 row)
mydb=# SELECT to_tsquery(’tst’,’indices’);

to_tsquery
------------
’index’:*
(1 row)
mydb=# SELECT ’indexes are very useful’::tsvector;

tsvector
---------------------------------
’are’ ’indexes’ ’useful’ ’very’
(1 row)
mydb=# SELECT ’indexes are very useful’::tsvector @@ to_tsquery(’tst’,’indices’);

?column?
----------
t
(1 row)
12.6.4. Thesaurus Dictionary

A thesaurus dictionary (sometimes abbreviated as TZ) is a collection of words that includes infor-
mation about the relationships of words and phrases, i.e., broader terms (BT), narrower terms (NT),
preferred terms, non-preferred terms, related terms, etc.
313
Basically a thesaurus dictionary replaces all non-preferred terms by one preferred term and, option-
ally, preserves the original terms for indexing as well. PostgreSQL’s current implementation of the
thesaurus dictionary is an extension of the synonym dictionary with added phrase support. A the-
saurus dictionary requires a configuration file of the following format:
# this is a comment
sample word(s) : indexed word(s)
more sample word(s) : more indexed word(s)
...
where the colon (:) symbol acts as a delimiter between a a phrase and its replacement.
A thesaurus dictionary uses a subdictionary (which is specified in the dictionary’s configuration) to
normalize the input text before checking for phrase matches. It is only possible to select one subdic-
tionary. An error is reported if the subdictionary fails to recognize a word. In that case, you should
remove the use of the word or teach the subdictionary about it. You can place an asterisk (*) at the
beginning of an indexed word to skip applying the subdictionary to it, but all sample words must be
known to the subdictionary.
The thesaurus dictionary chooses the longest match if there are multiple phrases matching the input,
and ties are broken by using the last definition.
Specific stop words recognized by the subdictionary cannot be specified; instead use ? to mark the
location where any stop word can appear. For example, assuming that a and the are stop words
according to the subdictionary:
? one ? two : swsw
matches a one the two and the one a two; both would be replaced by swsw.
Since a thesaurus dictionary has the capability to recognize phrases it must remember its state and
interact with the parser. A thesaurus dictionary uses these assignments to check if it should handle
the next word or stop accumulation. The thesaurus dictionary must be configured carefully. For ex-
ample, if the thesaurus dictionary is assigned to handle only the asciiword token, then a thesaurus
dictionary definition like one 7 will not work since token type uint is not assigned to the thesaurus
dictionary.
Caution
Thesauruses are used during indexing so any change in the thesaurus dictio-
nary’s parameters requires reindexing. For most other dictionary types, small
changes such as adding or removing stopwords does not force reindexing.
12.6.4.1. Thesaurus Configuration

To define a new thesaurus dictionary, use the thesaurus template. For example:
CREATE TEXT SEARCH DICTIONARY thesaurus_simple (

TEMPLATE = thesaurus,
DictFile = mythesaurus,
Dictionary = pg_catalog.english_stem
);
Here:
314
• thesaurus_simple is the new dictionary’s name

• mythesaurus is the base name of the thesaurus configuration file. (Its full name will be
$SHAREDIR/tsearch_data/mythesaurus.ths, where $SHAREDIR means the installation
shared-data directory.)
• pg_catalog.english_stem is the subdictionary (here, a Snowball English stemmer) to use for
thesaurus normalization. Notice that the subdictionary will have its own configuration (for example,
stop words), which is not shown here.
Now it is possible to bind the thesaurus dictionary thesaurus_simple to the desired token types in
a configuration, for example:
ALTER TEXT SEARCH CONFIGURATION russian

ALTER MAPPING FOR asciiword, asciihword, hword_asciipart
WITH thesaurus_simple;
12.6.4.2. Thesaurus Example

Consider a simple astronomical thesaurus thesaurus_astro, which contains some astronomical
word combinations:
supernovae stars : sn
crab nebulae : crab
Below we create a dictionary and bind some token types to an astronomical thesaurus and English
stemmer:
CREATE TEXT SEARCH DICTIONARY thesaurus_astro (

TEMPLATE = thesaurus,
DictFile = thesaurus_astro,
Dictionary = english_stem
);
ALTER TEXT SEARCH CONFIGURATION russian

ALTER MAPPING FOR asciiword, asciihword, hword_asciipart
WITH thesaurus_astro, english_stem;
Now we can see how it works. ts_lexize is not very useful for testing a thesaurus, because it treats
its input as a single token. Instead we can use plainto_tsquery and to_tsvector which will
break their input strings into multiple tokens:
SELECT plainto_tsquery(’supernova star’);

plainto_tsquery
-----------------
’sn’
SELECT to_tsvector(’supernova star’);

to_tsvector
-------------
’sn’:1
In principle, one can use to_tsquery if you quote the argument:
SELECT to_tsquery(”’supernova star”’);

to_tsquery
315
------------
’sn’
Notice that supernova star matches supernovae stars in thesaurus_astro because we

specified the english_stem stemmer in the thesaurus definition. The stemmer removed the e and s.
To index the original phrase as well as the substitute, just include it in the right-hand part of the
definition:
supernovae stars : sn supernovae stars
SELECT plainto_tsquery(’supernova star’);

plainto_tsquery
-----------------------------
’sn’ & ’supernova’ & ’star’
12.6.5. Ispell Dictionary

The Ispell dictionary template supports morphological dictionaries, which can normalize many dif-
ferent linguistic forms of a word into the same lexeme. For example, an English Ispell dictionary can
match all declensions and conjugations of the search term bank, e.g., banking, banked, banks,
banks’, and bank’s.
The standard PostgreSQL distribution does not include any Ispell configuration files. Dictionaries
for a large number of languages are available from Ispell1. Also, some more modern dictionary file
formats are supported — MySpell2 (OO < 2.0.1) and Hunspell3 (OO >= 2.0.2). A large list of dictio-
naries is available on the OpenOffice Wiki4.
To create an Ispell dictionary, use the built-in ispell template and specify several parameters:
CREATE TEXT SEARCH DICTIONARY english_ispell (

TEMPLATE = ispell,
DictFile = english,
AffFile = english,
StopWords = english
);
Here, DictFile, AffFile, and StopWords specify the base names of the dictionary, affixes, and
stop-words files. The stop-words file has the same format explained above for the simple dictionary
type. The format of the other files is not specified here but is available from the above-mentioned web
sites.
Ispell dictionaries usually recognize a limited set of words, so they should be followed by another
broader dictionary; for example, a Snowball dictionary, which recognizes everything.
Ispell dictionaries support splitting compound words; a useful feature. Notice that the affix file should
specify a special flag using the compoundwords controlled statement that marks dictionary words
that can participate in compound formation:
compoundwords controlled z
1. http://ficus-www.cs.ucla.edu/geoff/ispell.html
2. http://en.wikipedia.org/wiki/MySpell
3. http://sourceforge.net/projects/hunspell/
4. http://wiki.services.openoffice.org/wiki/Dictionaries
316
Here are some examples for the Norwegian language:
SELECT ts_lexize(’norwegian_ispell’, ’overbuljongterningpakkmesterassistent’);

{over,buljong,terning,pakk,mester,assistent}
SELECT ts_lexize(’norwegian_ispell’, ’sjokoladefabrikk’);
{sjokoladefabrikk,sjokolade,fabrikk}
Note: MySpell does not support compound words. Hunspell has sophisticated support for com-
pound words. At present, PostgreSQL implements only the basic compound word operations of
Hunspell.
12.6.6. Snowball Dictionary

The Snowball dictionary template is based on a project by Martin Porter, inventor of the popular
Porter’s stemming algorithm for the English language. Snowball now provides stemming algorithms
for many languages (see the Snowball site5 for more information). Each algorithm understands how
to reduce common variant forms of words to a base, or stem, spelling within its language. A Snowball
dictionary requires a language parameter to identify which stemmer to use, and optionally can spec-
ify a stopword file name that gives a list of words to eliminate. (PostgreSQL’s standard stopword
lists are also provided by the Snowball project.) For example, there is a built-in definition equivalent
to
CREATE TEXT SEARCH DICTIONARY english_stem (

TEMPLATE = snowball,
Language = english,
StopWords = english
);
The stopword file format is the same as already explained.

A Snowball dictionary recognizes everything, whether or not it is able to simplify the word, so it
should be placed at the end of the dictionary list. It is useless to have it before any other dictionary
because a token will never pass through it to the next dictionary.
12.7. Configuration Example

A text search configuration specifies all options necessary to transform a document into a tsvector:
the parser to use to break text into tokens, and the dictionaries to use to transform each token into a
lexeme. Every call of to_tsvector or to_tsquery needs a text search configuration to perform its
processing. The configuration parameter default_text_search_config specifies the name of the default
configuration, which is the one used by text search functions if an explicit configuration parameter is
omitted. It can be set in postgresql.conf, or set for an individual session using the SET command.
Several predefined text search configurations are available, and you can create custom configurations
easily. To facilitate management of text search objects, a set of SQL commands is available, and there
are several psql commands that display information about text search objects (Section 12.10).
5. http://snowball.tartarus.org
317
As an example we will create a configuration pg, starting by duplicating the built-in english con-
figuration:
CREATE TEXT SEARCH CONFIGURATION public.pg ( COPY = pg_catalog.english );
We will use a PostgreSQL-specific synonym list and store it in

$SHAREDIR/tsearch_data/pg_dict.syn. The file contents look like:
postgres pg
pgsql pg
postgresql pg
We define the synonym dictionary like this:
CREATE TEXT SEARCH DICTIONARY pg_dict (

TEMPLATE = synonym,
SYNONYMS = pg_dict
);
Next we register the Ispell dictionary english_ispell, which has its own configuration files:

TEMPLATE = ispell,
DictFile = english,
AffFile = english,
StopWords = english
);
Now we can set up the mappings for words in configuration pg:
ALTER TEXT SEARCH CONFIGURATION pg

ALTER MAPPING FOR asciiword, asciihword, hword_asciipart,
word, hword, hword_part
WITH pg_dict, english_ispell, english_stem;
We choose not to index or search some token types that the built-in configuration does handle:
ALTER TEXT SEARCH CONFIGURATION pg

DROP MAPPING FOR email, url, url_path, sfloat, float;
Now we can test our configuration:
SELECT * FROM ts_debug(’public.pg’, ’

PostgreSQL, the highly scalable, SQL compliant, open source object-relational
database management system, is now undergoing beta testing of the next
version of our software.
’);
The next step is to set the session to use the new configuration, which was created in the public
schema:
=> \dF
List of text search configurations
Schema | Name | Description
318
---------+------+-------------
public | pg |
SET default_text_search_config = ’public.pg’;

SET
SHOW default_text_search_config;
default_text_search_config
----------------------------
public.pg
12.8. Testing and Debugging Text Search

The behavior of a custom text search configuration can easily become confusing. The functions de-
scribed in this section are useful for testing text search objects. You can test a complete configuration,
or test parsers and dictionaries separately.
12.8.1. Configuration Testing

The function ts_debug allows easy testing of a text search configuration.
ts_debug([ config regconfig, ] document text,

OUT alias text,
OUT description text,
OUT token text,
OUT dictionaries regdictionary[],
OUT dictionary regdictionary,
OUT lexemes text[])
returns setof record
ts_debug displays information about every token of document as produced by the parser
and processed by the configured dictionaries. It uses the configuration specified by config , or
default_text_search_config if that argument is omitted.
ts_debug returns one row for each token identified in the text by the parser. The columns returned
are
• alias text — short name of the token type

• description text — description of the token type
• token text — text of the token
• dictionaries regdictionary[] — the dictionaries selected by the configuration for this token
type
• dictionary regdictionary — the dictionary that recognized the token, or NULL if none did
• lexemes text[] — the lexeme(s) produced by the dictionary that recognized the token, or NULL
if none did; an empty array ({}) means it was recognized as a stop word
Here is a simple example:
SELECT * FROM ts_debug(’english’,’a fat cat sat on a mat - it ate a fat rats’);
319
-----------+-----------------+-------+----------------+--------------+---------
asciiword | Word, all ASCII | a | {english_stem} | english_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | fat | {english_stem} | english_stem | {fat}
asciiword | Word, all ASCII | cat | {english_stem} | english_stem | {cat}
asciiword | Word, all ASCII | sat | {english_stem} | english_stem | {sat}
asciiword | Word, all ASCII | on | {english_stem} | english_stem | {}
asciiword | Word, all ASCII | mat | {english_stem} | english_stem | {mat}
blank | Space symbols | - | {} | |
asciiword | Word, all ASCII | it | {english_stem} | english_stem | {}
asciiword | Word, all ASCII | ate | {english_stem} | english_stem | {ate}
asciiword | Word, all ASCII | fat | {english_stem} | english_stem | {fat}
asciiword | Word, all ASCII | rats | {english_stem} | english_stem | {rat}
For a more extensive demonstration, we first create a public.english configuration and Ispell
dictionary for the English language:
CREATE TEXT SEARCH CONFIGURATION public.english ( COPY = pg_catalog.english );

TEMPLATE = ispell,
DictFile = english,
AffFile = english,
StopWords = english
);
ALTER TEXT SEARCH CONFIGURATION public.english

ALTER MAPPING FOR asciiword WITH english_ispell, english_stem;
SELECT * FROM ts_debug(’public.english’,’The Brightest supernovaes’);

alias | description | token | dictionaries | dictionary
-----------+-----------------+-------------+-------------------------------+-------------
asciiword | Word, all ASCII | The | {english_ispell,english_stem} | english_ispe
blank | Space symbols | | {} |
asciiword | Word, all ASCII | Brightest | {english_ispell,english_stem} | english_ispe
blank | Space symbols | | {} |
asciiword | Word, all ASCII | supernovaes | {english_ispell,english_stem} | english_stem
In this example, the word Brightest was recognized by the parser as an ASCII word (alias
asciiword). For this token type the dictionary list is english_ispell and english_stem.
The word was recognized by english_ispell, which reduced it to the noun bright. The word
supernovaes is unknown to the english_ispell dictionary so it was passed to the next
320
dictionary, and, fortunately, was recognized (in fact, english_stem is a Snowball dictionary which
recognizes everything; that is why it was placed at the end of the dictionary list).
The word The was recognized by the english_ispell dictionary as a stop word (Section 12.6.1)
and will not be indexed. The spaces are discarded too, since the configuration provides no dictionaries
at all for them.
You can reduce the width of the output by explicitly specifying which columns you want to see:
SELECT alias, token, dictionary, lexemes

FROM ts_debug(’public.english’,’The Brightest supernovaes’);
alias | token | dictionary | lexemes
-----------+-------------+----------------+-------------
asciiword | The | english_ispell | {}
blank | | |
asciiword | Brightest | english_ispell | {bright}
blank | | |
asciiword | supernovaes | english_stem | {supernova}
12.8.2. Parser Testing

The following functions allow direct testing of a text search parser.
ts_parse(parser_name text, document text,

OUT tokid integer, OUT token text) returns setof record
ts_parse(parser_oid oid, document text,
OUT tokid integer, OUT token text) returns setof record
ts_parse parses the given document and returns a series of records, one for each token produced
by parsing. Each record includes a tokid showing the assigned token type and a token which is the
text of the token. For example:
SELECT * FROM ts_parse(’default’, ’123 - a number’);

tokid | token
-------+--------
22 | 123
12 |
12 | -
1 | a
12 |
1 | number
ts_token_type(parser_name text, OUT tokid integer,

OUT alias text, OUT description text) returns setof record
ts_token_type(parser_oid oid, OUT tokid integer,
OUT alias text, OUT description text) returns setof record
ts_token_type returns a table which describes each type of token the specified parser can recog-
nize. For each token type, the table gives the integer tokid that the parser uses to label a token of that
type, the alias that names the token type in configuration commands, and a short description.
For example:
SELECT * FROM ts_token_type(’default’);
321
tokid | alias | description

-------+-----------------+------------------------------------------
1 | asciiword | Word, all ASCII
2 | word | Word, all letters
3 | numword | Word, letters and digits
4 | email | Email address
5 | url | URL
6 | host | Host
7 | sfloat | Scientific notation
8 | version | Version number
9 | hword_numpart | Hyphenated word part, letters and digits
10 | hword_part | Hyphenated word part, all letters
11 | hword_asciipart | Hyphenated word part, all ASCII
12 | blank | Space symbols
13 | tag | XML tag
14 | protocol | Protocol head
15 | numhword | Hyphenated word, letters and digits
16 | asciihword | Hyphenated word, all ASCII
17 | hword | Hyphenated word, all letters
18 | url_path | URL path
19 | file | File or path name
20 | float | Decimal notation
21 | int | Signed integer
22 | uint | Unsigned integer
23 | entity | XML entity
12.8.3. Dictionary Testing

The ts_lexize function facilitates dictionary testing.
ts_lexize(dict regdictionary, token text) returns text[]
ts_lexize returns an array of lexemes if the input token is known to the dictionary, or an empty
array if the token is known to the dictionary but it is a stop word, or NULL if it is an unknown word.
Examples:
SELECT ts_lexize(’english_stem’, ’stars’);

ts_lexize
-----------
{star}
SELECT ts_lexize(’english_stem’, ’a’);

ts_lexize
-----------
{}
Note: The ts_lexize function expects a single token, not text. Here is a case where this can be
confusing:
SELECT ts_lexize(’thesaurus_astro’,’supernovae stars’) is null;

?column?
----------
322
The thesaurus dictionary thesaurus_astro does know the phrase supernovae stars, but
ts_lexize fails since it does not parse the input text but treats it as a single token. Use
plainto_tsquery or to_tsvector to test thesaurus dictionaries, for example:
SELECT plainto_tsquery(’supernovae stars’);

plainto_tsquery
-----------------
’sn’
12.9. GiST and GIN Index Types

There are two kinds of indexes that can be used to speed up full text searches. Note that indexes are
not mandatory for full text searching, but in cases where a column is searched on a regular basis, an
index is usually desirable.
CREATE INDEX name ON table USING gist(column);
Creates a GiST (Generalized Search Tree)-based index. The column can be of tsvector or
tsquery type.
CREATE INDEX name ON table USING gin(column);
Creates a GIN (Generalized Inverted Index)-based index. The column must be of tsvector
type.
There are substantial performance differences between the two index types, so it is important to un-
derstand their characteristics.
A GiST index is lossy, meaning that the index may produce false matches, and it is necessary to
check the actual table row to eliminate such false matches. (PostgreSQL does this automatically when
needed.) GiST indexes are lossy because each document is represented in the index by a fixed-length
signature. The signature is generated by hashing each word into a single bit in an n-bit string, with all
these bits OR-ed together to produce an n-bit document signature. When two words hash to the same
bit position there will be a false match. If all words in the query have matches (real or false) then the
table row must be retrieved to see if the match is correct.
Lossiness causes performance degradation due to unnecessary fetches of table records that turn out
to be false matches. Since random access to table records is slow, this limits the usefulness of GiST
indexes. The likelihood of false matches depends on several factors, in particular the number of unique
words, so using dictionaries to reduce this number is recommended.
GIN indexes are not lossy for standard queries, but their performance depends logarithmically on
the number of unique words. (However, GIN indexes store only the words (lexemes) of tsvector
values, and not their weight labels. Thus a table row recheck is needed when using a query that
involves weights.)
In choosing which index type to use, GiST or GIN, consider these performance differences:
323
• GIN index lookups are about three times faster than GiST
• GIN indexes take about three times longer to build than GiST
• GIN indexes are moderately slower to update than GiST indexes, but about 10 times slower if
fast-update support was disabled (see Section 54.3.1 for details)
• GIN indexes are two-to-three times larger than GiST indexes
As a rule of thumb, GIN indexes are best for static data because lookups are faster. For dynamic data,
GiST indexes are faster to update. Specifically, GiST indexes are very good for dynamic data and fast
if the number of unique words (lexemes) is under 100,000, while GIN indexes will handle 100,000+
lexemes better but are slower to update.
Note that GIN index build time can often be improved by increasing maintenance_work_mem, while
GiST index build time is not sensitive to that parameter.
Partitioning of big collections and the proper use of GiST and GIN indexes allows the implementation
of very fast searches with online update. Partitioning can be done at the database level using table
inheritance, or by distributing documents over servers and collecting search results using the dblink
module. The latter is possible because ranking functions use only local information.
12.10. psql Support

Information about text search configuration objects can be obtained in psql using a set of commands:
\dF{d,p,t}[+] [PATTERN]
An optional + produces more details.

The optional parameter PATTERN can be the name of a text search object, optionally schema-qualified.
If PATTERN is omitted then information about all visible objects will be displayed. PATTERN can be a
regular expression and can provide separate patterns for the schema and object names. The following
examples illustrate this:
=> \dF *fulltext*

--------+--------------+-------------
public | fulltext_cfg |
=> \dF *.fulltext*

----------+----------------------------
fulltext | fulltext_cfg |
public | fulltext_cfg |
The available commands are:
\dF[+] [PATTERN]
List text search configurations (add + for more detail).

=> \dF russian
------------+---------+------------------------------------
324
pg_catalog | russian | configuration for russian language
=> \dF+ russian

Text search configuration "pg_catalog.russian"
Parser: "pg_catalog.default"
Token | Dictionaries
-----------------+--------------
asciihword | english_stem
asciiword | english_stem
email | simple
file | simple
float | simple
host | simple
hword | russian_stem
hword_asciipart | english_stem
hword_numpart | simple
hword_part | russian_stem
int | simple
numhword | simple
numword | simple
sfloat | simple
uint | simple
url | simple
url_path | simple
version | simple
word | russian_stem
\dFd[+] [PATTERN]
List text search dictionaries (add + for more detail).

=> \dFd
List of text search dictionaries
------------+-----------------+------------------------------------------------------
pg_catalog | danish_stem | snowball stemmer for danish language
pg_catalog | dutch_stem | snowball stemmer for dutch language
pg_catalog | english_stem | snowball stemmer for english language
pg_catalog | finnish_stem | snowball stemmer for finnish language
pg_catalog | french_stem | snowball stemmer for french language
pg_catalog | german_stem | snowball stemmer for german language
pg_catalog | hungarian_stem | snowball stemmer for hungarian language
pg_catalog | italian_stem | snowball stemmer for italian language
pg_catalog | norwegian_stem | snowball stemmer for norwegian language
pg_catalog | portuguese_stem | snowball stemmer for portuguese language
pg_catalog | romanian_stem | snowball stemmer for romanian language
pg_catalog | russian_stem | snowball stemmer for russian language
pg_catalog | simple | simple dictionary: just lower case and check for stop
pg_catalog | spanish_stem | snowball stemmer for spanish language
pg_catalog | swedish_stem | snowball stemmer for swedish language
pg_catalog | turkish_stem | snowball stemmer for turkish language
325
\dFp[+] [PATTERN]
List text search parsers (add + for more detail).
=> \dFp
List of text search parsers
------------+---------+---------------------
pg_catalog | default | default word parser
=> \dFp+
Text search parser "pg_catalog.default"
Method | Function | Description
-----------------+----------------+-------------
Start parse | prsd_start |
Get next token | prsd_nexttoken |
End parse | prsd_end |
Get headline | prsd_headline |
Get token types | prsd_lextype |
Token types for parser "pg_catalog.default"

Token name | Description
-----------------+------------------------------------------
asciihword | Hyphenated word, all ASCII
asciiword | Word, all ASCII
blank | Space symbols
email | Email address
entity | XML entity
file | File or path name
float | Decimal notation
host | Host
hword | Hyphenated word, all letters
hword_asciipart | Hyphenated word part, all ASCII
hword_numpart | Hyphenated word part, letters and digits
hword_part | Hyphenated word part, all letters
int | Signed integer
numhword | Hyphenated word, letters and digits
numword | Word, letters and digits
protocol | Protocol head
sfloat | Scientific notation
tag | XML tag
uint | Unsigned integer
url | URL
url_path | URL path
version | Version number
word | Word, all letters
(23 rows)
\dFt[+] [PATTERN]
List text search templates (add + for more detail).
=> \dFt
List of text search templates
------------+-----------+-----------------------------------------------------------
pg_catalog | ispell | ispell dictionary
pg_catalog | simple | simple dictionary: just lower case and check for stopword
pg_catalog | snowball | snowball stemmer
pg_catalog | synonym | synonym dictionary: replace word by its synonym
326
pg_catalog | thesaurus | thesaurus dictionary: phrase by phrase substitution
12.11. Limitations
The current limitations of PostgreSQL’s text search features are:
• The length of each lexeme must be less than 2K bytes

• The length of a tsvector (lexemes + positions) must be less than 1 megabyte
• The number of lexemes must be less than 264
• Position values in tsvector must be greater than 0 and no more than 16,383
• No more than 256 positions per lexeme
• The number of nodes (lexemes + operators) in a tsquery must be less than 32,768
For comparison, the PostgreSQL 8.1 documentation contained 10,441 unique words, a total of
335,420 words, and the most frequent word “postgresql” was mentioned 6,127 times in 655
documents.
Another example — the PostgreSQL mailing list archives contained 910,989 unique words with
57,491,343 lexemes in 461,020 messages.
12.12. Migration from Pre-8.3 Text Search

Applications that use the tsearch2 module for text searching will need some adjustments to work with
the built-in features:
• Some functions have been renamed or had small adjustments in their argument lists, and all of
them are now in the pg_catalog schema, whereas in a previous installation they would have
been in public or another non-system schema. There is a new version of tsearch2 that provides a
compatibility layer to solve most problems in this area.
• The old tsearch2 functions and other objects must be suppressed when loading pg_dump output
from a pre-8.3 database. While many of them won’t load anyway, a few will and then cause prob-
lems. One simple way to deal with this is to load the new tsearch2 module before restoring the
dump; then it will block the old objects from being loaded.
• Text search configuration setup is completely different now. Instead of manually inserting rows into
configuration tables, search is configured through the specialized SQL commands shown earlier in
this chapter. There is no automated support for converting an existing custom configuration for 8.3;
you’re on your own here.
• Most types of dictionaries rely on some outside-the-database configuration files. These are largely
compatible with pre-8.3 usage, but note the following differences:
• Configuration files now must be placed in a single specified directory
($SHAREDIR/tsearch_data), and must have a specific extension depending on the type of
file, as noted previously in the descriptions of the various dictionary types. This restriction was
added to forestall security problems.
• Configuration files must be encoded in UTF-8 encoding, regardless of what database encoding
is used.
• In thesaurus configuration files, stop words must be marked with ?.
327

Full Text Indexes in Postgresql

Uploaded by

Copyright:

Available Formats

Full Text Indexes in Postgresql

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Full Text Indexes in Postgresql

Uploaded by

Copyright:

Available Formats

Chapter 12.

Full Text Search

• Deﬁne stop words that should not be indexed.

12.1.1. What Is a Document?

SELECT title || ’ ’ || author || ’ ’ || abstract || ’ ’ || body AS document

SELECT m.title || ’ ’ || m.author || ’ ’ || m.abstract || ’ ’ || d.body AS document

12.1.2. Basic Text Matching

SELECT to_tsvector(’fat cats ate fat rats’) @@ to_tsquery(’fat & rat’);

Observe that this match would not succeed if written as

SELECT ’fat cats ate fat rats’::tsvector @@ to_tsquery(’fat & rat’);

12.2. Tables and Indexes

12.2.1. Searching a Table

This query will use the conﬁguration set by default_text_search_conﬁg.

12.2.2. Creating Indexes

CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector(’english’, body));

CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector(config_name, body));

Indexes can even concatenate columns:

CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector(’english’, title || ’ ’ || body));

ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;

Then we create a GIN index to speed up the search:

CREATE INDEX textsearch_idx ON pgweb USING gin(textsearchable_index_col);

Now we are ready to perform a fast full text search:

12.3. Controlling Text Search

12.3.1. Parsing Documents

to_tsvector([ config regconfig, ] document text) returns tsvector

SELECT to_tsvector(’english’, ’a fat cat sat on a mat - it ate a fat rats’);

12.3.2. Parsing Queries

to_tsquery([ config regconfig, ] querytext text) returns tsquery

SELECT to_tsquery(’english’, ’The & Fat & Rats’);

SELECT to_tsquery(’english’, ’Fat | Rats:AB’);

Also, * can be attached to a lexeme to specify preﬁx matching:

SELECT to_tsquery(’supern:*A & star:A*B’);

SELECT to_tsquery(”’supernovae stars” & !crab’);

plainto_tsquery([ config regconfig, ] querytext text) returns tsquery

SELECT plainto_tsquery(’english’, ’The Fat Rats’);

SELECT plainto_tsquery(’english’, ’The Fat & Rats:C’);

12.3.3. Ranking Search Results

ts_rank([ weights float4[], ] vector tsvector,

Ranks vectors based on the frequency of their matching lexems.

ts_rank_cd([ weights float4[], ] vector tsvector,

{D-weight, C-weight, B-weight, A-weight}

If no weights are provided, then these defaults are used:

{0.1, 0.2, 0.4, 1.0}

• 0 (the default) ignores the document length

SELECT title, ts_rank_cd(textsearch, query) AS rank

This is the same example using normalized ranking:

SELECT title, ts_rank_cd(textsearch, query, 32 /* rank/(rank+1) */ ) AS rank

12.3.4. Highlighting Results

to_tsquery(’query & similarity’));

SELECT id, ts_headline(body, q), rank

12.4. Additional Features

12.4.1. Manipulating Documents

setweight(vector tsvector, weight "char") returns tsvector

length(vector tsvector) returns integer

Returns the number of lexemes stored in the vector.

strip(vector tsvector) returns tsvector

12.4.2. Manipulating Queries

tsquery && tsquery

SELECT to_tsquery(’supern:A & star:AB’);

entity XML entity &