The TSQUERY
data type stores a list of lexemes separated by operators. TSQUERY
values are used in full-text search.
Syntax
A TSQUERY
comprises individual lexemes and operators in the form: 'These' & 'lexemes' & 'are' & 'not' & 'normalized' & 'lexemes.'
.
The operators in a TSQUERY
are used to match a TSQUERY
to a TSVECTOR
. Valid TSQUERY
operators are:
&
(AND). Given'one' & 'two'
, bothone
andtwo
must be present in the matchingTSVECTOR
.|
(OR). Given'one' | 'two'
, eitherone
ortwo
must be present in the matchingTSVECTOR
.!
(NOT). Given'one' & ! 'two'
,one
must be present andtwo
must not be present in the matchingTSVECTOR
.<->
(FOLLOWED BY). Given'one' <-> 'two'
,one
must be followed bytwo
in the matchingTSVECTOR
.<->
is equivalent to<1>
. You can specify an integer<n>
to indicate that lexemes must be separated byn-1
other lexemes. Given'one' <4> 'two'
,one
must be followed by three lexemes and then followed bytwo
in the matchingTSVECTOR
.
You can optionally add the following to each lexeme:
One or more weight letters (
A
,B
,C
, orD
):'These' & 'lexemes':B & 'are' & 'not' & 'normalized':A & 'lexemes':B
If not specified, a lexeme's weight defaults to
D
. It is only necessary to specify weights in aTSQUERY
if they are also specified in aTSVECTOR
to be used in a comparison. The lexemes in aTSQUERY
andTSVECTOR
will only match if they have matching weights. For more information about weights, see the PostgreSQL documentation.
To be usable in full-text search, the lexemes must be normalized. You can do this by using the to_tsquery()
, plainto_tsquery()
, or phraseto_tsquery()
built-in functions to convert a string input to TSQUERY
:
SELECT to_tsquery('These & lexemes & are & not & normalized & lexemes.');
to_tsquery
--------------------------------
'lexem' & 'normal' & 'lexem'
Normalization removes the following from the input:
- Derivatives of words, which are reduced using a stemming algorithm.
- Stop words. These are words that are considered not useful for indexing and searching, based on the text search configuration. In the preceding example, "These", "are", and "not" are identified as stop words.
- Punctuation and capitalization.
Examples
For usage examples, see Full-Text Search.