| PostgreSQL 8.3.3 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 9. Functions and Operators | Fast Forward | Next |
Table 9-35, Table 9-36 and Table 9-37 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-35. Text Search Operators
| Operator | Description | Example | Result |
|---|---|---|---|
| @@ | tsvector matches tsquery ? | to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat') | t |
| @@@ | same as @@, but see Section 12.9 | 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 |
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-36. Text Search Functions
| Function | Return Type | Description | Example | Result | |
|---|---|---|---|---|---|
to_tsvector([ config regconfig , ] document text) | tsvector | reduce document text to tsvector | to_tsvector('english', 'The Fat Rats') | 'fat':2 'rat':3 | |
length(tsvector) | integer | number of lexemes in tsvector | length('fat:2,4 cat:3 rat:5A'::tsvector) | 3 | |
setweight(tsvector, "char") | tsvector | assign weight to each element of tsvector | setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A') | 'cat':3A 'fat':2A,4A 'rat':5A | |
strip(tsvector) | tsvector | remove positions and weights from tsvector | strip('fat:2,4 cat:3 rat:5A'::tsvector) | 'cat' 'fat' 'rat' | |
to_tsquery([ config regconfig , ] query text) | tsquery | normalize words and convert to tsquery | to_tsquery('english', 'The & Fat & Rats') | 'fat' & 'rat' | |
plainto_tsquery([ config regconfig , ] query text) | tsquery | produce tsquery ignoring punctuation | plainto_tsquery('english', 'The Fat Rats') | 'fat' & 'rat' | |
numnode(tsquery) | integer | number of lexemes plus operators in tsquery | numnode('(fat & rat) | cat'::tsquery) | 5 | |
querytree(query tsquery) | text | get indexable part of a tsquery | querytree('foo & ! bar'::tsquery) | 'foo' | |
ts_rank([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) | float4 | rank document for query | ts_rank(textsearch, query) | 0.818 | |
ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) | float4 | rank document for query using cover density | ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', textsearch, query) | 2.01317 | |
ts_headline([ config regconfig, ] document text, query tsquery [, options text ]) | text | display a query match | ts_headline('x y z', 'z'::tsquery) | x y <b>z</b> | |
ts_rewrite(query tsquery, target tsquery, substitute tsquery) | tsquery | replace target with substitute within query | ts_rewrite('a & b'::tsquery, 'a'::tsquery, 'foo|bar'::tsquery) | 'b' & ( 'foo' | 'bar' ) | |
ts_rewrite(query tsquery, select text) | tsquery | replace using targets and substitutes from a SELECT command | SELECT ts_rewrite('a & b'::tsquery, 'SELECT t,s FROM aliases') | 'b' & ( 'foo' | 'bar' ) | |
get_current_ts_config() | regconfig | get default text search configuration | get_current_ts_config() | english | |
tsvector_update_trigger() | trigger | trigger function for automatic tsvector column update | CREATE TRIGGER ... tsvector_update_trigger(tsvcol, 'pg_catalog.swedish', title, body) | ||
tsvector_update_trigger_column() | trigger | trigger function for automatic tsvector column update | CREATE TRIGGER ... tsvector_update_trigger_column(tsvcol, configcol, title, body) |
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-37 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-37. Text Search Debugging Functions
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
ts_debug([ config regconfig, ] document text, OUT alias text, OUT description text, OUT token text, OUT dictionaries regdictionary[], OUT dictionary regdictionary, OUT lexemes text[]) | setof record | test a configuration | ts_debug('english', 'The Brightest supernovaes') | (asciiword,"Word, all ASCII",The,{english_stem},english_stem,{}) ... |
ts_lexize(dict regdictionary, token text) | text[] | test a dictionary | ts_lexize('english_stem', 'stars') | {star} |
ts_parse(parser_name text, document text, OUT tokid integer, OUT token text) | setof record | test a parser | ts_parse('default', 'foo - bar') | (1,foo) ... |
ts_parse(parser_oid oid, document text, OUT tokid integer, OUT token text) | setof record | test a parser | ts_parse(3722, 'foo - bar') | (1,foo) ... |
ts_token_type(parser_name text, OUT tokid integer, OUT alias text, OUT description text) | setof record | get token types defined by parser | ts_token_type('default') | (1,asciiword,"Word, all ASCII") ... |
ts_token_type(parser_oid oid, OUT tokid integer, OUT alias text, OUT description text) | setof record | get token types defined by parser | ts_token_type(3722) | (1,asciiword,"Word, all ASCII") ... |
ts_stat(sqlquery text, [ weights text, ] OUT word text, OUT ndoc integer, OUT nentry integer) | setof record | get statistics of a tsvector column | ts_stat('SELECT vector from apod') | (foo,10,15) ... |
No comments could be found for this page.
Please use this form to add your own comments regarding your experience with particular features of PostgreSQL, clarifications of the documentation, or hints for other users. Please note, this is not a support forum, and your IP address will be logged. If you have a question or need help, please see the faq, try a mailing list, or join us on IRC. Note that submissions containing URLs or other keywords commonly found in 'spam' comments may be silently discarded. Please contact the webmaster if you think this is happening to you in error.
In order to submit a comment, you must have a community account.
* denotes required field