- publishing free software manuals
The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
ISBN 9781906966041
RRP £14.95 ($19.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

10.1 Introduction

Full Text Searching (or just text search) provides the capability to identify natural-language documents that satisfy a query, and optionally to sort them by relevance to the query. The most common type of search is to find all documents containing given query terms and return them in order of their similarity to the query. Notions of query and similarity are very flexible and depend on the specific application. The simplest search considers query as a set of words and similarity as the frequency of query words in the document.

Textual search operators have existed in databases for years. PostgreSQL has ~, ~*, LIKE, and ILIKE operators for textual data types, but they lack many essential properties required by modern information systems:

Full text indexing allows documents to be preprocessed and an index saved for later rapid searching. Preprocessing includes:

Dictionaries allow fine-grained control over how tokens are normalized. With appropriate dictionaries, you can:

A data type tsvector is provided for storing preprocessed documents, along with a type tsquery for representing processed queries (section 6.11 Text Search Types). There are many functions and operators available for these data types (section 7.13 Text Search Functions and Operators), the most important of which is the match operator @@, which we introduce in section 10.1.2 Basic Text Matching. Full text searches can be accelerated using indexes (section 10.9 GiST and GIN Index Types).

ISBN 9781906966041The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language ReferenceSee the print edition