Table 9.37, “Text Search Operators”, Table 9.38, “Text Search Functions” and Table 9.39, “Text Search Debugging Functions” summarize the functions and operators that are provided for full text searching. See Chapter 12, Full Text Search 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 tsvectors | 'a:1 b:2'::tsvector || 'c:1 d:2 b:3'::tsvector | 'a':1 'b':2,5 'c':3 'd':4 |
&& | AND tsquerys together | 'fat | rat'::tsquery && 'cat'::tsquery | ( 'fat' | 'rat' ) & 'cat' |
|| | OR tsquerys 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 |
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
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, “Text Search Debugging Functions” 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