Updated on 2026-11-12 GMT+08:00

Text Search Types

In data warehouse applications, it is a common requirement to quickly retrieve records containing specific keywords from massive amounts of text data. For example, in log analysis, tsvector and tsquery can be used to efficiently retrieve log records containing specific keywords. In e-commerce systems, full-text search can be performed based on product descriptions to match user queries. In document management systems, keyword search and relevance ranking can be performed on a large number of documents. Traditional relational databases use LIKE or ~ (regular expression) for text matching. However, the performance is poor when the data volume is large, and stem matching and relevance ranking at the semantic level are not supported.

DWS provides two data types, tsvector and tsquery, to support full-text search. The tsvector type represents a document tokenized for text search, and the tsquery type represents a text query condition. The two types are used together and the @@ operator is used to perform matching, which can efficiently implement the full-text search function.

Concepts

  • Full-text search: A technology used to retrieve records containing specific keywords or semantics from text data. Different from the simple LIKE pattern matching, full-text search performs tokenization and stemming on text, and supports semantic matching and relevance ranking. It is applicable to scenarios where a large amount of text data needs to be retrieved.
  • Parser: A component that splits the original text into multiple lexemes. The parser splits the text by delimiters such as spaces and punctuations, and identifies the boundaries of lexemes. DWS has multiple built-in parsers (such as english and default). You can use to_tsvector('parser name', text) to specify the parser to be used.
  • Lexeme: The normalized form of a lexeme after stemming. For example, the stem of the English word "Rats" is "rat", and the stem of the word "running" is "run". Stemming enables the search to match different forms of the same word (such as singular and plural forms, and tense changes), improving the recall rate of the search.

tsvector

The tsvector type represents a retrieval unit, usually a row of text fields in a database table or a combination of these fields.

A tsvector value is a sorted list of distinct lexemes, which are words that have been normalized to merge different variants of the same word. Sorting and duplicate-elimination are done automatically during input.

The to_tsvector function is used to parse and normalize a document string.

Basic Lexical Analysis

The following example shows how to use tsvector to tokenize a string by spaces. The tokens are sorted by alphabet and length:

1
2
3
4
5
SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
                      tsvector                      
----------------------------------------------------
 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'
(1 row)

If a lexeme contains spaces or punctuation, enclose it in quotation marks:

1
2
3
4
5
SELECT $$the lexeme '    ' contains spaces$$::tsvector;
                 tsvector                  
-------------------------------------------
 '    ' 'contains' 'lexeme' 'spaces' 'the'
(1 row)

For a string enclosed in single quotation marks, the single quotation marks (') and backslashes (\) embedded in the string must be doubled for escaping:

1
2
3
4
5
SELECT $$the lexeme 'Joe''s' contains a quote$$::tsvector;
                    tsvector                    
------------------------------------------------
 'Joe''s' 'a' 'contains' 'lexeme' 'quote' 'the'
(1 row)

Position Constants

A lexeme position constant can also be placed in the lexicon to indicate the position of the lexeme in the original text:

1
2
3
4
5
SELECT 'a:1 fat:2 cat:3 sat:4 on:5 a:6 mat:7 and:8 ate:9 a:10 fat:11 rat:12'::tsvector;
                                   tsvector                                    
-------------------------------------------------------------------------------
 'a':1,6,10 'and':8 'ate':9 'cat':3 'fat':2,11 'mat':7 'on':5 'rat':12 'sat':4
(1 row)

Position constants typically indicate the position of the source word in the document. Position information can be used for ranking. The position constant ranges from 1 to 16383, with the maximum value being 16383 by default. Repeated positions of the same term are ignored.

Weight

A term with a position can be further marked with a weight, which can be A, B, C, or D. D is the default weight, and therefore it is not displayed in the output:

1
2
3
4
5
SELECT 'a:1A fat:2B,4C cat:5D'::tsvector;
          tsvector          
----------------------------
 'a':1A 'cat':5 'fat':2B,4C
(1 row)
Table 1 Weight parameter descriptions

Weight

Description

Displayed

Typical Use Case

A

Highest weight

Displayed (marked as A)

Title and important keywords

B

Medium and high weights

Displayed (marked as B)

Abstract and subtitle

C

Medium and low weights

Displayed (marked as C)

Important paragraphs in the body

D

Default weight

Not displayed

Common content in the body

Weights are typically used to reflect the document structure, for example, marking titles differently from body words. The text search ranking functions can assign different priorities to different weights.

Normalization

The following is an example of the standard usage of the tsvector type:

1
2
3
4
5
SELECT 'The Fat Rats'::tsvector;
      tsvector      
--------------------
 'Fat' 'Rats' 'The'
(1 row)

For most English-text-searching applications, the above words would be considered non-normalized, which should usually be passed through to_tsvector to normalize the words appropriately for searching:

1
2
3
4
5
SELECT to_tsvector('english', 'The Fat Rats');
   to_tsvector   
-----------------
 'fat':2 'rat':3
(1 row)

to_tsvector('english', ...) uses the English configuration to tokenize and stem the string "The Fat Rats". Stop word "the" is removed, "Fat" is converted to "fat", and "Rats" is stemmed to "rat". Positional constants 2 and 3 are retained.

tsquery

The tsquery type represents a search condition, storing words to be searched for and combining them using Boolean operators.

Boolean Operators

tsquery supports the following Boolean operators. The precedence rules are as follows: If there are no parentheses, the NOT operator has the highest precedence, followed by & (AND), and finally | (OR). Parentheses can be used to change the grouping precedence of operators.

Table 2 tsquery operators

Operator

Meaning

Description

Priority

&

AND

Both sides of the operator must match.

Medium

|

OR

At least one of the two sides of the operator must match.

Low

!

NOT

The following entries do not match.

High

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
SELECT 'fat & rat'::tsquery;
    tsquery    
---------------
 'fat' & 'rat'
(1 row)

SELECT 'fat & (rat | cat)'::tsquery;
          tsquery          
---------------------------
 'fat' & ( 'rat' | 'cat' )
(1 row)

SELECT 'fat & rat & ! cat'::tsquery;
        tsquery         
------------------------
 'fat' & 'rat' & !'cat'
(1 row)

Weighted Term

A term in a tsquery can be marked with one or more weight letters, which restricts the term to match only tsvector terms with the same weight.

1
2
3
4
5
6
7
SELECT 'fat:ab & cat'::tsquery;
     tsquery      
------------------
 'fat':AB & 'cat'
(1 row)

-- 'fat:ab' indicates that the term is matched only when the weight of the corresponding term in tsvector is A or B.

Prefix Matching

A term in a tsquery can be marked with an asterisk (*) to specify prefix matching. For example, this query can match any word in tsvector that starts with "super".

1
2
3
4
5
SELECT 'super:*'::tsquery;
  tsquery  
-----------
 'super':*
(1 row)

Note that prefix matching is first processed by the text search tokenizer. For example, the stem extracted from postgres is postgr, and it matches postgraduate, which means that the following result is true:

1
2
3
4
5
SELECT to_tsvector( 'postgraduate' ) @@ to_tsquery( 'postgres:*' ) AS RESULT;
  result  
----------
 t
(1 row)
1
2
3
4
5
SELECT to_tsquery('postgres:*');
 to_tsquery 
------------
 'postgr':*
(1 row)

@@ Operator (Full-Text Search Matching)

@@ is a full-text search matching operator, which is used to determine whether a tsquery query condition matches a tsvector document tokenization.

Syntax:

1
tsvector @@ tsquery

Return type: Boolean (true indicates a match, and false indicates no match).

Table 3 Parameter descriptions

Operand

Type

Description

Left operand

tsvector

A list of tokens from the document, typically generated by to_tsvector.

Right operand

tsquery

Query condition, which is typically generated by using to_tsquery or plainto_tsquery.

Matching rule: Determines whether the query conditions (combined by AND/OR/NOT) in tsquery can be satisfied by any lexemes in tsvector. If all conditions are satisfied, true is returned. Otherwise, false is returned.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- Check whether the document contains both "fat" and "rat".
SELECT to_tsvector('english', 'The Fat Rats') @@ to_tsquery('fat & rat') AS result;
-- Expected result:
--  result
-- --------
--  t
-- (1 row)
-- Check whether the document contains "cat". (If not, false is returned.)
SELECT to_tsvector('english', 'The Fat Rats') @@ to_tsquery('cat') AS result;
-- Expected result:
--  result
-- --------
--  f
-- (1 row)

Normalization

The to_tsquery function normalizes words before converting them to the tsquery type. The following is the result of converting 'Fat:ab & Cats' to the tsquery type:

1
2
3
4
5
6
--to_tsquery performs stemming and normalization on the input: Fat is converted to lowercase fat, Cats is stemmed to cat, and the weight label ab is converted to uppercase AB.
SELECT to_tsquery('Fat:ab & Cats');
    to_tsquery    
------------------
 'fat':AB & 'cat'
(1 row)