TSQUERY

On this page

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', both one and two must be present in the matching TSVECTOR.
• | (OR). Given 'one' | 'two', either one or two must be present in the matching TSVECTOR.
• ! (NOT). Given 'one' & ! 'two', one must be present and two must not be present in the matching TSVECTOR.
• <-> (FOLLOWED BY). Given 'one' <-> 'two', one must be followed by two in the matching TSVECTOR.
  • <-> is equivalent to <1>. You can specify an integer <n> to indicate that lexemes must be separated by n-1 other lexemes. Given 'one' <4> 'two', one must be followed by three lexemes and then followed by two in the matching TSVECTOR.

You can optionally add the following to each lexeme:

• One or more weight letters (A, B, C, or D):

  '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 a TSQUERY if they are also specified in a TSVECTOR to be used in a comparison. The lexemes in a TSQUERY and TSVECTOR 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.

See also

• Full-Text Search
• TSVECTOR
• Data Types