9.13. Text Search Functions and Operators
Table 9.41 , Table 9.42 and Table 9.43 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.41. Text Search Operators
Operator | Return Type | Description | Example | Result |
---|---|---|---|---|
@@
|
boolean
|
tsvector
matches
tsquery
?
|
to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat')
|
t
|
@@@
|
boolean
|
deprecated synonym for
@@
|
to_tsvector('fat cats ate rats') @@@ to_tsquery('cat & rat')
|
t
|
||
|
tsvector
|
concatenate
tsvector
s
|
'a:1 b:2'::tsvector || 'c:1 d:2 b:3'::tsvector
|
'a':1 'b':2,5 'c':3 'd':4
|
&&
|
tsquery
|
AND
tsquery
s together
|
'fat | rat'::tsquery && 'cat'::tsquery
|
( 'fat' | 'rat' ) & 'cat'
|
||
|
tsquery
|
OR
tsquery
s together
|
'fat | rat'::tsquery || 'cat'::tsquery
|
( 'fat' | 'rat' ) | 'cat'
|
!!
|
tsquery
|
negate a
tsquery
|
!! 'cat'::tsquery
|
!'cat'
|
<->
|
tsquery
|
tsquery
followed by
tsquery
|
to_tsquery('fat') <-> to_tsquery('rat')
|
'fat' <-> 'rat'
|
@>
|
boolean
|
tsquery
contains another ?
|
'cat'::tsquery @> 'cat & rat'::tsquery
|
f
|
<@
|
boolean
|
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.42. 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.43 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.43. Text Search Debugging Functions