Help Center/ GaussDB(DWS)/ SQL Syntax Reference (8.1.3.x)/ Functions and Operators/ Text Search Functions and Operators/ Text Search Operators
Updated on 2024-09-30 GMT+08:00
View PDF

Text Search Operators

@@

Description: Specifies whether the tsvector-typed words match the tsquery-typed words.

Example:

1
2
3
4
5
 
SELECT to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat') AS RESULT;
 result 
--------
 t
(1 row)

@@@

Description: Synonym for @@

Example:

1
2
3
4
5
 
SELECT to_tsvector('fat cats ate rats') @@@ to_tsquery('cat & rat') AS RESULT;
 result 
--------
 t
(1 row)

&&

Description: Performs the AND operation on two tsquery-typed words.

Example:

1
2
3
4
5
 
SELECT 'fat | rat'::tsquery && 'cat'::tsquery AS RESULT;
          result           
---------------------------
 ( 'fat' | 'rat' ) & 'cat'
(1 row)

||

Description: Performs the OR operation on two tsquery-typed words.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT 'fat | rat'::tsquery || 'cat'::tsquery AS RESULT;
          result           
---------------------------
 ( 'fat' | 'rat' ) | 'cat'
(1 row)
SELECT 'a:1 b:2'::tsvector || 'c:1 d:2 b:3'::tsvector AS RESULT;
          result           
---------------------------
 'a':1 'b':2,5 'c':3 'd':4
(1 row)

!!

Description: NOT a tsquery

Example:

1
2
3
4
5
 
SELECT !! 'cat'::tsquery AS RESULT;
 result 
--------
 !'cat'
(1 row)

@>

Description: Specifies whether a tsquery-typed word contains another tsquery-typed word.

Example:

1
2
3
4
5
 
SELECT 'cat'::tsquery @> 'cat & rat'::tsquery AS RESULT;
 result 
--------
 f
(1 row)

<@

Description: Specifies whether a tsquery-typed word is contained in another tsquery-typed word.

Example:

1
2
3
4
5
 
SELECT 'cat'::tsquery <@ 'cat & rat'::tsquery AS RESULT;
 result 
--------
 t
(1 row)

In addition to the preceding operators, the ordinary B-tree comparison operators (including = and <) are defined for types tsvector and tsquery.