9.13. Text Search Functions and Operators
Table 9.37, Table 9.38 and Table 9.39 summarize the functions and operators that are provided for full text searching. See Chapter 12 for a detailed explanation of PostgreSQL's text search facility.
Table 9.37. Text Search Operators
Operator | Description | Example | Result |
---|---|---|---|
@@ | tsvector matches tsquery ? | to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat') | t |
@@@ | deprecated synonym for @@ | to_tsvector('fat cats ate rats') @@@ to_tsquery('cat & rat') | t |
|| | concatenate tsvector s | 'a:1 b:2'::tsvector || 'c:1 d:2 b:3'::tsvector | 'a':1 'b':2,5 'c':3 'd':4 |
&& | AND tsquery s together | 'fat | rat'::tsquery && 'cat'::tsquery | ( 'fat' | 'rat' ) & 'cat' |
|| | OR tsquery s together | 'fat | rat'::tsquery || 'cat'::tsquery | ( 'fat' | 'rat' ) | 'cat' |
!! | negate a tsquery | !! 'cat'::tsquery | !'cat' |
@> | tsquery contains another ? | 'cat'::tsquery @> 'cat & rat'::tsquery | f |
<@ | tsquery is contained in ? | 'cat'::tsquery <@ 'cat & rat'::tsquery | t |
Note
The tsquery
containment operators consider only the lexemes listed in the two queries, ignoring the combining operators.
In addition to the operators shown in the table, the ordinary B-tree comparison operators (=
, <
, etc) are defined for types tsvector
and tsquery
. These are not very useful for text searching but allow, for example, unique indexes to be built on columns of these types.
Table 9.38. Text Search Functions
Note
All the text search functions that accept an optional regconfig
argument will use the configuration specified by default_text_search_config when that argument is omitted.
The functions in Table 9.39 are listed separately because they are not usually used in everyday text searching operations. They are helpful for development and debugging of new text search configurations.
Table 9.39. Text Search Debugging Functions