This page in other versions: 8.4 / 9.0 / 9.1 / 9.2 / 9.3  |  Development versions: devel / 9.4  |  Unsupported versions: 8.3

9.13. Text Search Functions and Operators

Table 9-36, Table 9-37 and Table 9-38 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-36. 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

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-37. Text Search Functions

Function Return Type Description Example Result
get_current_ts_config() regconfig get default text search configuration get_current_ts_config() english
length(tsvector) integer number of lexemes in tsvector length('fat:2,4 cat:3 rat:5A'::tsvector) 3
numnode(tsquery) integer number of lexemes plus operators in tsquery numnode('(fat & rat) | cat'::tsquery) 5
plainto_tsquery([ config regconfig , ] query text) tsquery produce tsquery ignoring punctuation plainto_tsquery('english', 'The Fat Rats') 'fat' & 'rat'
querytree(query tsquery) text get indexable part of a tsquery querytree('foo & ! bar'::tsquery) 'foo'
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'
to_tsvector([ config regconfig , ] document text) tsvector reduce document text to tsvector to_tsvector('english', 'The Fat Rats') 'fat':2 'rat':3
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_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_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' )
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-38 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-38. 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) ...

Add Comment

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.

Proceed to the comment form.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group