1 <!-- doc/src/sgml/textsearch.sgml -->
3 <chapter id="textsearch">
4 <title>Full Text Search</title>
6 <indexterm zone="textsearch">
7 <primary>full text search</primary>
10 <indexterm zone="textsearch">
11 <primary>text search</primary>
14 <sect1 id="textsearch-intro">
15 <title>Introduction</title>
18 Full Text Searching (or just <firstterm>text search</firstterm>) provides
19 the capability to identify natural-language <firstterm>documents</> that
20 satisfy a <firstterm>query</firstterm>, and optionally to sort them by
21 relevance to the query. The most common type of search
22 is to find all documents containing given <firstterm>query terms</firstterm>
23 and return them in order of their <firstterm>similarity</firstterm> to the
24 query. Notions of <varname>query</varname> and
25 <varname>similarity</varname> are very flexible and depend on the specific
26 application. The simplest search considers <varname>query</varname> as a
27 set of words and <varname>similarity</varname> as the frequency of query
28 words in the document.
32 Textual search operators have existed in databases for years.
33 <productname>PostgreSQL</productname> has
34 <literal>~</literal>, <literal>~*</literal>, <literal>LIKE</literal>, and
35 <literal>ILIKE</literal> operators for textual data types, but they lack
36 many essential properties required by modern information systems:
39 <itemizedlist spacing="compact" mark="bullet">
42 There is no linguistic support, even for English. Regular expressions
43 are not sufficient because they cannot easily handle derived words, e.g.,
44 <literal>satisfies</literal> and <literal>satisfy</literal>. You might
45 miss documents that contain <literal>satisfies</literal>, although you
46 probably would like to find them when searching for
47 <literal>satisfy</literal>. It is possible to use <literal>OR</literal>
48 to search for multiple derived forms, but this is tedious and error-prone
49 (some words can have several thousand derivatives).
55 They provide no ordering (ranking) of search results, which makes them
56 ineffective when thousands of matching documents are found.
62 They tend to be slow because there is no index support, so they must
63 process all documents for every search.
69 Full text indexing allows documents to be <emphasis>preprocessed</emphasis>
70 and an index saved for later rapid searching. Preprocessing includes:
73 <itemizedlist mark="none">
76 <emphasis>Parsing documents into <firstterm>tokens</></emphasis>. It is
77 useful to identify various classes of tokens, e.g., numbers, words,
78 complex words, email addresses, so that they can be processed
79 differently. In principle token classes depend on the specific
80 application, but for most purposes it is adequate to use a predefined
82 <productname>PostgreSQL</productname> uses a <firstterm>parser</> to
83 perform this step. A standard parser is provided, and custom parsers
84 can be created for specific needs.
90 <emphasis>Converting tokens into <firstterm>lexemes</></emphasis>.
91 A lexeme is a string, just like a token, but it has been
92 <firstterm>normalized</> so that different forms of the same word
93 are made alike. For example, normalization almost always includes
94 folding upper-case letters to lower-case, and often involves removal
95 of suffixes (such as <literal>s</> or <literal>es</> in English).
96 This allows searches to find variant forms of the
97 same word, without tediously entering all the possible variants.
98 Also, this step typically eliminates <firstterm>stop words</>, which
99 are words that are so common that they are useless for searching.
100 (In short, then, tokens are raw fragments of the document text, while
101 lexemes are words that are believed useful for indexing and searching.)
102 <productname>PostgreSQL</productname> uses <firstterm>dictionaries</> to
103 perform this step. Various standard dictionaries are provided, and
104 custom ones can be created for specific needs.
110 <emphasis>Storing preprocessed documents optimized for
111 searching</emphasis>. For example, each document can be represented
112 as a sorted array of normalized lexemes. Along with the lexemes it is
113 often desirable to store positional information to use for
114 <firstterm>proximity ranking</firstterm>, so that a document that
115 contains a more <quote>dense</> region of query words is
116 assigned a higher rank than one with scattered query words.
122 Dictionaries allow fine-grained control over how tokens are normalized.
123 With appropriate dictionaries, you can:
126 <itemizedlist spacing="compact" mark="bullet">
129 Define stop words that should not be indexed.
135 Map synonyms to a single word using <application>Ispell</>.
141 Map phrases to a single word using a thesaurus.
147 Map different variations of a word to a canonical form using
148 an <application>Ispell</> dictionary.
154 Map different variations of a word to a canonical form using
155 <application>Snowball</> stemmer rules.
161 A data type <type>tsvector</type> is provided for storing preprocessed
162 documents, along with a type <type>tsquery</type> for representing processed
163 queries (<xref linkend="datatype-textsearch">). There are many
164 functions and operators available for these data types
165 (<xref linkend="functions-textsearch">), the most important of which is
166 the match operator <literal>@@</literal>, which we introduce in
167 <xref linkend="textsearch-matching">. Full text searches can be accelerated
168 using indexes (<xref linkend="textsearch-indexes">).
172 <sect2 id="textsearch-document">
173 <title>What Is a Document?</title>
175 <indexterm zone="textsearch-document">
176 <primary>document</primary>
177 <secondary>text search</secondary>
181 A <firstterm>document</> is the unit of searching in a full text search
182 system; for example, a magazine article or email message. The text search
183 engine must be able to parse documents and store associations of lexemes
184 (key words) with their parent document. Later, these associations are
185 used to search for documents that contain query words.
189 For searches within <productname>PostgreSQL</productname>,
190 a document is normally a textual field within a row of a database table,
191 or possibly a combination (concatenation) of such fields, perhaps stored
192 in several tables or obtained dynamically. In other words, a document can
193 be constructed from different parts for indexing and it might not be
194 stored anywhere as a whole. For example:
197 SELECT title || ' ' || author || ' ' || abstract || ' ' || body AS document
201 SELECT m.title || ' ' || m.author || ' ' || m.abstract || ' ' || d.body AS document
202 FROM messages m, docs d
203 WHERE mid = did AND mid = 12;
209 Actually, in these example queries, <function>coalesce</function>
210 should be used to prevent a single <literal>NULL</literal> attribute from
211 causing a <literal>NULL</literal> result for the whole document.
216 Another possibility is to store the documents as simple text files in the
217 file system. In this case, the database can be used to store the full text
218 index and to execute searches, and some unique identifier can be used to
219 retrieve the document from the file system. However, retrieving files
220 from outside the database requires superuser permissions or special
221 function support, so this is usually less convenient than keeping all
222 the data inside <productname>PostgreSQL</productname>. Also, keeping
223 everything inside the database allows easy access
224 to document metadata to assist in indexing and display.
228 For text search purposes, each document must be reduced to the
229 preprocessed <type>tsvector</> format. Searching and ranking
230 are performed entirely on the <type>tsvector</> representation
231 of a document — the original text need only be retrieved
232 when the document has been selected for display to a user.
233 We therefore often speak of the <type>tsvector</> as being the
234 document, but of course it is only a compact representation of
239 <sect2 id="textsearch-matching">
240 <title>Basic Text Matching</title>
243 Full text searching in <productname>PostgreSQL</productname> is based on
244 the match operator <literal>@@</literal>, which returns
245 <literal>true</literal> if a <type>tsvector</type>
246 (document) matches a <type>tsquery</type> (query).
247 It doesn't matter which data type is written first:
250 SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery;
255 SELECT 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector;
263 As the above example suggests, a <type>tsquery</type> is not just raw
264 text, any more than a <type>tsvector</type> is. A <type>tsquery</type>
265 contains search terms, which must be already-normalized lexemes, and
266 may combine multiple terms using AND, OR, and NOT operators.
267 (For details see <xref linkend="datatype-textsearch">.) There are
268 functions <function>to_tsquery</> and <function>plainto_tsquery</>
269 that are helpful in converting user-written text into a proper
270 <type>tsquery</type>, for example by normalizing words appearing in
271 the text. Similarly, <function>to_tsvector</> is used to parse and
272 normalize a document string. So in practice a text search match would
276 SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat');
282 Observe that this match would not succeed if written as
285 SELECT 'fat cats ate fat rats'::tsvector @@ to_tsquery('fat & rat');
291 since here no normalization of the word <literal>rats</> will occur.
292 The elements of a <type>tsvector</> are lexemes, which are assumed
293 already normalized, so <literal>rats</> does not match <literal>rat</>.
297 The <literal>@@</literal> operator also
298 supports <type>text</type> input, allowing explicit conversion of a text
299 string to <type>tsvector</type> or <type>tsquery</> to be skipped
300 in simple cases. The variants available are:
311 The first two of these we saw already.
312 The form <type>text</type> <literal>@@</literal> <type>tsquery</type>
313 is equivalent to <literal>to_tsvector(x) @@ y</literal>.
314 The form <type>text</type> <literal>@@</literal> <type>text</type>
315 is equivalent to <literal>to_tsvector(x) @@ plainto_tsquery(y)</literal>.
319 <sect2 id="textsearch-intro-configurations">
320 <title>Configurations</title>
323 The above are all simple text search examples. As mentioned before, full
324 text search functionality includes the ability to do many more things:
325 skip indexing certain words (stop words), process synonyms, and use
326 sophisticated parsing, e.g., parse based on more than just white space.
327 This functionality is controlled by <firstterm>text search
328 configurations</>. <productname>PostgreSQL</> comes with predefined
329 configurations for many languages, and you can easily create your own
330 configurations. (<application>psql</>'s <command>\dF</> command
331 shows all available configurations.)
335 During installation an appropriate configuration is selected and
336 <xref linkend="guc-default-text-search-config"> is set accordingly
337 in <filename>postgresql.conf</>. If you are using the same text search
338 configuration for the entire cluster you can use the value in
339 <filename>postgresql.conf</>. To use different configurations
340 throughout the cluster but the same configuration within any one database,
341 use <command>ALTER DATABASE ... SET</>. Otherwise, you can set
342 <varname>default_text_search_config</varname> in each session.
346 Each text search function that depends on a configuration has an optional
347 <type>regconfig</> argument, so that the configuration to use can be
348 specified explicitly. <varname>default_text_search_config</varname>
349 is used only when this argument is omitted.
353 To make it easier to build custom text search configurations, a
354 configuration is built up from simpler database objects.
355 <productname>PostgreSQL</>'s text search facility provides
356 four types of configuration-related database objects:
359 <itemizedlist spacing="compact" mark="bullet">
362 <firstterm>Text search parsers</> break documents into tokens
363 and classify each token (for example, as words or numbers).
369 <firstterm>Text search dictionaries</> convert tokens to normalized
370 form and reject stop words.
376 <firstterm>Text search templates</> provide the functions underlying
377 dictionaries. (A dictionary simply specifies a template and a set
378 of parameters for the template.)
384 <firstterm>Text search configurations</> select a parser and a set
385 of dictionaries to use to normalize the tokens produced by the parser.
391 Text search parsers and templates are built from low-level C functions;
392 therefore it requires C programming ability to develop new ones, and
393 superuser privileges to install one into a database. (There are examples
394 of add-on parsers and templates in the <filename>contrib/</> area of the
395 <productname>PostgreSQL</> distribution.) Since dictionaries and
396 configurations just parameterize and connect together some underlying
397 parsers and templates, no special privilege is needed to create a new
398 dictionary or configuration. Examples of creating custom dictionaries and
399 configurations appear later in this chapter.
406 <sect1 id="textsearch-tables">
407 <title>Tables and Indexes</title>
410 The examples in the previous section illustrated full text matching using
411 simple constant strings. This section shows how to search table data,
412 optionally using indexes.
415 <sect2 id="textsearch-tables-search">
416 <title>Searching a Table</title>
419 It is possible to do a full text search without an index. A simple query
420 to print the <structname>title</> of each row that contains the word
421 <literal>friend</> in its <structfield>body</> field is:
426 WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');
429 This will also find related words such as <literal>friends</>
430 and <literal>friendly</>, since all these are reduced to the same
435 The query above specifies that the <literal>english</> configuration
436 is to be used to parse and normalize the strings. Alternatively we
437 could omit the configuration parameters:
442 WHERE to_tsvector(body) @@ to_tsquery('friend');
445 This query will use the configuration set by <xref
446 linkend="guc-default-text-search-config">.
450 A more complex example is to
451 select the ten most recent documents that contain <literal>create</> and
452 <literal>table</> in the <structname>title</> or <structname>body</>:
457 WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('create & table')
458 ORDER BY last_mod_date DESC
462 For clarity we omitted the <function>coalesce</function> function calls
463 which would be needed to find rows that contain <literal>NULL</literal>
464 in one of the two fields.
468 Although these queries will work without an index, most applications
469 will find this approach too slow, except perhaps for occasional ad-hoc
470 searches. Practical use of text searching usually requires creating
476 <sect2 id="textsearch-tables-index">
477 <title>Creating Indexes</title>
480 We can create a <acronym>GIN</acronym> index (<xref
481 linkend="textsearch-indexes">) to speed up text searches:
484 CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', body));
487 Notice that the 2-argument version of <function>to_tsvector</function> is
488 used. Only text search functions that specify a configuration name can
489 be used in expression indexes (<xref linkend="indexes-expressional">).
490 This is because the index contents must be unaffected by <xref
491 linkend="guc-default-text-search-config">. If they were affected, the
492 index contents might be inconsistent because different entries could
493 contain <type>tsvector</>s that were created with different text search
494 configurations, and there would be no way to guess which was which. It
495 would be impossible to dump and restore such an index correctly.
499 Because the two-argument version of <function>to_tsvector</function> was
500 used in the index above, only a query reference that uses the 2-argument
501 version of <function>to_tsvector</function> with the same configuration
502 name will use that index. That is, <literal>WHERE
503 to_tsvector('english', body) @@ 'a & b'</> can use the index,
504 but <literal>WHERE to_tsvector(body) @@ 'a & b'</> cannot.
505 This ensures that an index will be used only with the same configuration
506 used to create the index entries.
510 It is possible to set up more complex expression indexes wherein the
511 configuration name is specified by another column, e.g.:
514 CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector(config_name, body));
517 where <literal>config_name</> is a column in the <literal>pgweb</>
518 table. This allows mixed configurations in the same index while
519 recording which configuration was used for each index entry. This
520 would be useful, for example, if the document collection contained
521 documents in different languages. Again,
522 queries that are meant to use the index must be phrased to match, e.g.,
523 <literal>WHERE to_tsvector(config_name, body) @@ 'a & b'</>.
527 Indexes can even concatenate columns:
530 CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', title || ' ' || body));
535 Another approach is to create a separate <type>tsvector</> column
536 to hold the output of <function>to_tsvector</>. This example is a
537 concatenation of <literal>title</literal> and <literal>body</literal>,
538 using <function>coalesce</> to ensure that one field will still be
539 indexed when the other is <literal>NULL</>:
542 ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;
543 UPDATE pgweb SET textsearchable_index_col =
544 to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));
547 Then we create a <acronym>GIN</acronym> index to speed up the search:
550 CREATE INDEX textsearch_idx ON pgweb USING gin(textsearchable_index_col);
553 Now we are ready to perform a fast full text search:
558 WHERE textsearchable_index_col @@ to_tsquery('create & table')
559 ORDER BY last_mod_date DESC
565 When using a separate column to store the <type>tsvector</>
567 it is necessary to create a trigger to keep the <type>tsvector</>
568 column current anytime <literal>title</> or <literal>body</> changes.
569 <xref linkend="textsearch-update-triggers"> explains how to do that.
573 One advantage of the separate-column approach over an expression index
574 is that it is not necessary to explicitly specify the text search
575 configuration in queries in order to make use of the index. As shown
576 in the example above, the query can depend on
577 <varname>default_text_search_config</>. Another advantage is that
578 searches will be faster, since it will not be necessary to redo the
579 <function>to_tsvector</> calls to verify index matches. (This is more
580 important when using a GiST index than a GIN index; see <xref
581 linkend="textsearch-indexes">.) The expression-index approach is
582 simpler to set up, however, and it requires less disk space since the
583 <type>tsvector</> representation is not stored explicitly.
590 <sect1 id="textsearch-controls">
591 <title>Controlling Text Search</title>
594 To implement full text searching there must be a function to create a
595 <type>tsvector</type> from a document and a <type>tsquery</type> from a
596 user query. Also, we need to return results in a useful order, so we need
597 a function that compares documents with respect to their relevance to
598 the query. It's also important to be able to display the results nicely.
599 <productname>PostgreSQL</productname> provides support for all of these
603 <sect2 id="textsearch-parsing-documents">
604 <title>Parsing Documents</title>
607 <productname>PostgreSQL</productname> provides the
608 function <function>to_tsvector</function> for converting a document to
609 the <type>tsvector</type> data type.
613 <primary>to_tsvector</primary>
617 to_tsvector(<optional> <replaceable class="PARAMETER">config</replaceable> <type>regconfig</>, </optional> <replaceable class="PARAMETER">document</replaceable> <type>text</>) returns <type>tsvector</>
621 <function>to_tsvector</function> parses a textual document into tokens,
622 reduces the tokens to lexemes, and returns a <type>tsvector</type> which
623 lists the lexemes together with their positions in the document.
624 The document is processed according to the specified or default
625 text search configuration.
626 Here is a simple example:
629 SELECT to_tsvector('english', 'a fat cat sat on a mat - it ate a fat rats');
631 -----------------------------------------------------
632 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4
637 In the example above we see that the resulting <type>tsvector</type> does not
638 contain the words <literal>a</literal>, <literal>on</literal>, or
639 <literal>it</literal>, the word <literal>rats</literal> became
640 <literal>rat</literal>, and the punctuation sign <literal>-</literal> was
645 The <function>to_tsvector</function> function internally calls a parser
646 which breaks the document text into tokens and assigns a type to
647 each token. For each token, a list of
648 dictionaries (<xref linkend="textsearch-dictionaries">) is consulted,
649 where the list can vary depending on the token type. The first dictionary
650 that <firstterm>recognizes</> the token emits one or more normalized
651 <firstterm>lexemes</firstterm> to represent the token. For example,
652 <literal>rats</literal> became <literal>rat</literal> because one of the
653 dictionaries recognized that the word <literal>rats</literal> is a plural
654 form of <literal>rat</literal>. Some words are recognized as
655 <firstterm>stop words</> (<xref linkend="textsearch-stopwords">), which
656 causes them to be ignored since they occur too frequently to be useful in
657 searching. In our example these are
658 <literal>a</literal>, <literal>on</literal>, and <literal>it</literal>.
659 If no dictionary in the list recognizes the token then it is also ignored.
660 In this example that happened to the punctuation sign <literal>-</literal>
661 because there are in fact no dictionaries assigned for its token type
662 (<literal>Space symbols</literal>), meaning space tokens will never be
663 indexed. The choices of parser, dictionaries and which types of tokens to
664 index are determined by the selected text search configuration (<xref
665 linkend="textsearch-configuration">). It is possible to have
666 many different configurations in the same database, and predefined
667 configurations are available for various languages. In our example
668 we used the default configuration <literal>english</literal> for the
673 The function <function>setweight</function> can be used to label the
674 entries of a <type>tsvector</type> with a given <firstterm>weight</>,
675 where a weight is one of the letters <literal>A</>, <literal>B</>,
676 <literal>C</>, or <literal>D</>.
677 This is typically used to mark entries coming from
678 different parts of a document, such as title versus body. Later, this
679 information can be used for ranking of search results.
683 Because <function>to_tsvector</function>(<literal>NULL</literal>) will
684 return <literal>NULL</literal>, it is recommended to use
685 <function>coalesce</function> whenever a field might be null.
686 Here is the recommended method for creating
687 a <type>tsvector</type> from a structured document:
691 setweight(to_tsvector(coalesce(title,'')), 'A') ||
692 setweight(to_tsvector(coalesce(keyword,'')), 'B') ||
693 setweight(to_tsvector(coalesce(abstract,'')), 'C') ||
694 setweight(to_tsvector(coalesce(body,'')), 'D');
697 Here we have used <function>setweight</function> to label the source
698 of each lexeme in the finished <type>tsvector</type>, and then merged
699 the labeled <type>tsvector</type> values using the <type>tsvector</>
700 concatenation operator <literal>||</>. (<xref
701 linkend="textsearch-manipulate-tsvector"> gives details about these
707 <sect2 id="textsearch-parsing-queries">
708 <title>Parsing Queries</title>
711 <productname>PostgreSQL</productname> provides the
712 functions <function>to_tsquery</function> and
713 <function>plainto_tsquery</function> for converting a query to
714 the <type>tsquery</type> data type. <function>to_tsquery</function>
715 offers access to more features than <function>plainto_tsquery</function>,
716 but is less forgiving about its input.
720 <primary>to_tsquery</primary>
724 to_tsquery(<optional> <replaceable class="PARAMETER">config</replaceable> <type>regconfig</>, </optional> <replaceable class="PARAMETER">querytext</replaceable> <type>text</>) returns <type>tsquery</>
728 <function>to_tsquery</function> creates a <type>tsquery</> value from
729 <replaceable>querytext</replaceable>, which must consist of single tokens
730 separated by the Boolean operators <literal>&</literal> (AND),
731 <literal>|</literal> (OR) and <literal>!</literal> (NOT). These operators
732 can be grouped using parentheses. In other words, the input to
733 <function>to_tsquery</function> must already follow the general rules for
734 <type>tsquery</> input, as described in <xref
735 linkend="datatype-textsearch">. The difference is that while basic
736 <type>tsquery</> input takes the tokens at face value,
737 <function>to_tsquery</function> normalizes each token to a lexeme using
738 the specified or default configuration, and discards any tokens that are
739 stop words according to the configuration. For example:
742 SELECT to_tsquery('english', 'The & Fat & Rats');
748 As in basic <type>tsquery</> input, weight(s) can be attached to each
749 lexeme to restrict it to match only <type>tsvector</> lexemes of those
750 weight(s). For example:
753 SELECT to_tsquery('english', 'Fat | Rats:AB');
759 Also, <literal>*</> can be attached to a lexeme to specify prefix matching:
762 SELECT to_tsquery('supern:*A & star:A*B');
764 --------------------------
765 'supern':*A & 'star':*AB
768 Such a lexeme will match any word in a <type>tsvector</> that begins
769 with the given string.
773 <function>to_tsquery</function> can also accept single-quoted
774 phrases. This is primarily useful when the configuration includes a
775 thesaurus dictionary that may trigger on such phrases.
776 In the example below, a thesaurus contains the rule <literal>supernovae
777 stars : sn</literal>:
780 SELECT to_tsquery('''supernovae stars'' & !crab');
786 Without quotes, <function>to_tsquery</function> will generate a syntax
787 error for tokens that are not separated by an AND or OR operator.
791 <primary>plainto_tsquery</primary>
795 plainto_tsquery(<optional> <replaceable class="PARAMETER">config</replaceable> <type>regconfig</>, </optional> <replaceable class="PARAMETER">querytext</replaceable> <type>text</>) returns <type>tsquery</>
799 <function>plainto_tsquery</> transforms unformatted text
800 <replaceable>querytext</replaceable> to <type>tsquery</type>.
801 The text is parsed and normalized much as for <function>to_tsvector</>,
802 then the <literal>&</literal> (AND) Boolean operator is inserted
803 between surviving words.
810 SELECT plainto_tsquery('english', 'The Fat Rats');
816 Note that <function>plainto_tsquery</> cannot
817 recognize Boolean operators, weight labels, or prefix-match labels
821 SELECT plainto_tsquery('english', 'The Fat & Rats:C');
823 ---------------------
824 'fat' & 'rat' & 'c'
827 Here, all the input punctuation was discarded as being space symbols.
832 <sect2 id="textsearch-ranking">
833 <title>Ranking Search Results</title>
836 Ranking attempts to measure how relevant documents are to a particular
837 query, so that when there are many matches the most relevant ones can be
838 shown first. <productname>PostgreSQL</productname> provides two
839 predefined ranking functions, which take into account lexical, proximity,
840 and structural information; that is, they consider how often the query
841 terms appear in the document, how close together the terms are in the
842 document, and how important is the part of the document where they occur.
843 However, the concept of relevancy is vague and very application-specific.
844 Different applications might require additional information for ranking,
845 e.g., document modification time. The built-in ranking functions are only
846 examples. You can write your own ranking functions and/or combine their
847 results with additional factors to fit your specific needs.
851 The two ranking functions currently available are:
858 <primary>ts_rank</primary>
863 ts_rank(<optional> <replaceable class="PARAMETER">weights</replaceable> <type>float4[]</>, </optional> <replaceable class="PARAMETER">vector</replaceable> <type>tsvector</>,
864 <replaceable class="PARAMETER">query</replaceable> <type>tsquery</> <optional>, <replaceable class="PARAMETER">normalization</replaceable> <type>integer</> </optional>) returns <type>float4</>
870 Ranks vectors based on the frequency of their matching lexems.
878 <primary>ts_rank_cd</primary>
883 ts_rank_cd(<optional> <replaceable class="PARAMETER">weights</replaceable> <type>float4[]</>, </optional> <replaceable class="PARAMETER">vector</replaceable> <type>tsvector</>,
884 <replaceable class="PARAMETER">query</replaceable> <type>tsquery</> <optional>, <replaceable class="PARAMETER">normalization</replaceable> <type>integer</> </optional>) returns <type>float4</>
890 This function computes the <firstterm>cover density</firstterm>
891 ranking for the given document vector and query, as described in
892 Clarke, Cormack, and Tudhope's "Relevance Ranking for One to Three
893 Term Queries" in the journal "Information Processing and Management",
898 This function requires positional information in its input.
899 Therefore it will not work on <quote>stripped</> <type>tsvector</>
900 values — it will always return zero.
910 For both these functions,
911 the optional <replaceable class="PARAMETER">weights</replaceable>
912 argument offers the ability to weigh word instances more or less
913 heavily depending on how they are labeled. The weight arrays specify
914 how heavily to weigh each category of word, in the order:
917 {D-weight, C-weight, B-weight, A-weight}
920 If no <replaceable class="PARAMETER">weights</replaceable> are provided,
921 then these defaults are used:
927 Typically weights are used to mark words from special areas of the
928 document, like the title or an initial abstract, so they can be
929 treated with more or less importance than words in the document body.
933 Since a longer document has a greater chance of containing a query term
934 it is reasonable to take into account document size, e.g., a hundred-word
935 document with five instances of a search word is probably more relevant
936 than a thousand-word document with five instances. Both ranking functions
937 take an integer <replaceable>normalization</replaceable> option that
938 specifies whether and how a document's length should impact its rank.
939 The integer option controls several behaviors, so it is a bit mask:
940 you can specify one or more behaviors using
941 <literal>|</literal> (for example, <literal>2|4</literal>).
943 <itemizedlist spacing="compact" mark="bullet">
946 0 (the default) ignores the document length
951 1 divides the rank by 1 + the logarithm of the document length
956 2 divides the rank by the document length
961 4 divides the rank by the mean harmonic distance between extents
962 (this is implemented only by <function>ts_rank_cd</>)
967 8 divides the rank by the number of unique words in document
972 16 divides the rank by 1 + the logarithm of the number
973 of unique words in document
978 32 divides the rank by itself + 1
983 If more than one flag bit is specified, the transformations are
984 applied in the order listed.
988 It is important to note that the ranking functions do not use any global
989 information, so it is impossible to produce a fair normalization to 1% or
990 100% as sometimes desired. Normalization option 32
991 (<literal>rank/(rank+1)</literal>) can be applied to scale all ranks
992 into the range zero to one, but of course this is just a cosmetic change;
993 it will not affect the ordering of the search results.
997 Here is an example that selects only the ten highest-ranked matches:
1000 SELECT title, ts_rank_cd(textsearch, query) AS rank
1001 FROM apod, to_tsquery('neutrino|(dark & matter)') query
1002 WHERE query @@ textsearch
1006 -----------------------------------------------+----------
1007 Neutrinos in the Sun | 3.1
1008 The Sudbury Neutrino Detector | 2.4
1009 A MACHO View of Galactic Dark Matter | 2.01317
1010 Hot Gas and Dark Matter | 1.91171
1011 The Virgo Cluster: Hot Plasma and Dark Matter | 1.90953
1012 Rafting for Solar Neutrinos | 1.9
1013 NGC 4650A: Strange Galaxy and Dark Matter | 1.85774
1014 Hot Gas and Dark Matter | 1.6123
1015 Ice Fishing for Cosmic Neutrinos | 1.6
1016 Weak Lensing Distorts the Universe | 0.818218
1019 This is the same example using normalized ranking:
1022 SELECT title, ts_rank_cd(textsearch, query, 32 /* rank/(rank+1) */ ) AS rank
1023 FROM apod, to_tsquery('neutrino|(dark & matter)') query
1024 WHERE query @@ textsearch
1028 -----------------------------------------------+-------------------
1029 Neutrinos in the Sun | 0.756097569485493
1030 The Sudbury Neutrino Detector | 0.705882361190954
1031 A MACHO View of Galactic Dark Matter | 0.668123210574724
1032 Hot Gas and Dark Matter | 0.65655958650282
1033 The Virgo Cluster: Hot Plasma and Dark Matter | 0.656301290640973
1034 Rafting for Solar Neutrinos | 0.655172410958162
1035 NGC 4650A: Strange Galaxy and Dark Matter | 0.650072921219637
1036 Hot Gas and Dark Matter | 0.617195790024749
1037 Ice Fishing for Cosmic Neutrinos | 0.615384618911517
1038 Weak Lensing Distorts the Universe | 0.450010798361481
1043 Ranking can be expensive since it requires consulting the
1044 <type>tsvector</type> of each matching document, which can be I/O bound and
1045 therefore slow. Unfortunately, it is almost impossible to avoid since
1046 practical queries often result in large numbers of matches.
1051 <sect2 id="textsearch-headline">
1052 <title>Highlighting Results</title>
1055 To present search results it is ideal to show a part of each document and
1056 how it is related to the query. Usually, search engines show fragments of
1057 the document with marked search terms. <productname>PostgreSQL</>
1058 provides a function <function>ts_headline</function> that
1059 implements this functionality.
1063 <primary>ts_headline</primary>
1067 ts_headline(<optional> <replaceable class="PARAMETER">config</replaceable> <type>regconfig</>, </optional> <replaceable class="PARAMETER">document</replaceable> <type>text</>, <replaceable class="PARAMETER">query</replaceable> <type>tsquery</> <optional>, <replaceable class="PARAMETER">options</replaceable> <type>text</> </optional>) returns <type>text</>
1071 <function>ts_headline</function> accepts a document along
1072 with a query, and returns an excerpt from
1073 the document in which terms from the query are highlighted. The
1074 configuration to be used to parse the document can be specified by
1075 <replaceable>config</replaceable>; if <replaceable>config</replaceable>
1077 <varname>default_text_search_config</varname> configuration is used.
1081 If an <replaceable>options</replaceable> string is specified it must
1082 consist of a comma-separated list of one or more
1083 <replaceable>option</><literal>=</><replaceable>value</> pairs.
1084 The available options are:
1086 <itemizedlist spacing="compact" mark="bullet">
1089 <literal>StartSel</>, <literal>StopSel</literal>: the strings with
1090 which to delimit query words appearing in the document, to distinguish
1091 them from other excerpted words. You must double-quote these strings
1092 if they contain spaces or commas.
1097 <literal>MaxWords</>, <literal>MinWords</literal>: these numbers
1098 determine the longest and shortest headlines to output.
1103 <literal>ShortWord</literal>: words of this length or less will be
1104 dropped at the start and end of a headline. The default
1105 value of three eliminates common English articles.
1110 <literal>HighlightAll</literal>: Boolean flag; if
1111 <literal>true</literal> the whole document will be used as the
1112 headline, ignoring the preceding three parameters.
1117 <literal>MaxFragments</literal>: maximum number of text excerpts
1118 or fragments to display. The default value of zero selects a
1119 non-fragment-oriented headline generation method. A value greater than
1120 zero selects fragment-based headline generation. This method
1121 finds text fragments with as many query words as possible and
1122 stretches those fragments around the query words. As a result
1123 query words are close to the middle of each fragment and have words on
1124 each side. Each fragment will be of at most <literal>MaxWords</> and
1125 words of length <literal>ShortWord</> or less are dropped at the start
1126 and end of each fragment. If not all query words are found in the
1127 document, then a single fragment of the first <literal>MinWords</>
1128 in the document will be displayed.
1133 <literal>FragmentDelimiter</literal>: When more than one fragment is
1134 displayed, the fragments will be separated by this string.
1139 Any unspecified options receive these defaults:
1142 StartSel=<b>, StopSel=</b>,
1143 MaxWords=35, MinWords=15, ShortWord=3, HighlightAll=FALSE,
1144 MaxFragments=0, FragmentDelimiter=" ... "
1152 SELECT ts_headline('english',
1153 'The most common type of search
1154 is to find all documents containing given query terms
1155 and return them in order of their similarity to the
1157 to_tsquery('query & similarity'));
1159 ------------------------------------------------------------
1160 containing given <b>query</b> terms
1161 and return them in order of their <b>similarity</b> to the
1162 <b>query</b>.
1164 SELECT ts_headline('english',
1165 'The most common type of search
1166 is to find all documents containing given query terms
1167 and return them in order of their similarity to the
1169 to_tsquery('query & similarity'),
1170 'StartSel = <, StopSel = >');
1172 -------------------------------------------------------
1173 containing given <query> terms
1174 and return them in order of their <similarity> to the
1180 <function>ts_headline</> uses the original document, not a
1181 <type>tsvector</type> summary, so it can be slow and should be used with
1182 care. A typical mistake is to call <function>ts_headline</function> for
1183 <emphasis>every</emphasis> matching document when only ten documents are
1184 to be shown. <acronym>SQL</acronym> subqueries can help; here is an
1188 SELECT id, ts_headline(body, q), rank
1189 FROM (SELECT id, body, q, ts_rank_cd(ti, q) AS rank
1190 FROM apod, to_tsquery('stars') q
1201 <sect1 id="textsearch-features">
1202 <title>Additional Features</title>
1205 This section describes additional functions and operators that are
1206 useful in connection with text search.
1209 <sect2 id="textsearch-manipulate-tsvector">
1210 <title>Manipulating Documents</title>
1213 <xref linkend="textsearch-parsing-documents"> showed how raw textual
1214 documents can be converted into <type>tsvector</> values.
1215 <productname>PostgreSQL</productname> also provides functions and
1216 operators that can be used to manipulate documents that are already
1217 in <type>tsvector</> form.
1225 <primary>tsvector concatenation</primary>
1230 <type>tsvector</> || <type>tsvector</>
1236 The <type>tsvector</> concatenation operator
1237 returns a vector which combines the lexemes and positional information
1238 of the two vectors given as arguments. Positions and weight labels
1239 are retained during the concatenation.
1240 Positions appearing in the right-hand vector are offset by the largest
1241 position mentioned in the left-hand vector, so that the result is
1242 nearly equivalent to the result of performing <function>to_tsvector</>
1243 on the concatenation of the two original document strings. (The
1244 equivalence is not exact, because any stop-words removed from the
1245 end of the left-hand argument will not affect the result, whereas
1246 they would have affected the positions of the lexemes in the
1247 right-hand argument if textual concatenation were used.)
1251 One advantage of using concatenation in the vector form, rather than
1252 concatenating text before applying <function>to_tsvector</>, is that
1253 you can use different configurations to parse different sections
1254 of the document. Also, because the <function>setweight</> function
1255 marks all lexemes of the given vector the same way, it is necessary
1256 to parse the text and do <function>setweight</> before concatenating
1257 if you want to label different parts of the document with different
1266 <primary>setweight</primary>
1271 setweight(<replaceable class="PARAMETER">vector</replaceable> <type>tsvector</>, <replaceable class="PARAMETER">weight</replaceable> <type>"char"</>) returns <type>tsvector</>
1277 <function>setweight</> returns a copy of the input vector in which every
1278 position has been labeled with the given <replaceable>weight</>, either
1279 <literal>A</literal>, <literal>B</literal>, <literal>C</literal>, or
1280 <literal>D</literal>. (<literal>D</literal> is the default for new
1281 vectors and as such is not displayed on output.) These labels are
1282 retained when vectors are concatenated, allowing words from different
1283 parts of a document to be weighted differently by ranking functions.
1287 Note that weight labels apply to <emphasis>positions</>, not
1288 <emphasis>lexemes</>. If the input vector has been stripped of
1289 positions then <function>setweight</> does nothing.
1296 <primary>length(tsvector)</primary>
1301 length(<replaceable class="PARAMETER">vector</replaceable> <type>tsvector</>) returns <type>integer</>
1307 Returns the number of lexemes stored in the vector.
1315 <primary>strip</primary>
1320 strip(<replaceable class="PARAMETER">vector</replaceable> <type>tsvector</>) returns <type>tsvector</>
1326 Returns a vector which lists the same lexemes as the given vector, but
1327 which lacks any position or weight information. While the returned
1328 vector is much less useful than an unstripped vector for relevance
1329 ranking, it will usually be much smaller.
1339 <sect2 id="textsearch-manipulate-tsquery">
1340 <title>Manipulating Queries</title>
1343 <xref linkend="textsearch-parsing-queries"> showed how raw textual
1344 queries can be converted into <type>tsquery</> values.
1345 <productname>PostgreSQL</productname> also provides functions and
1346 operators that can be used to manipulate queries that are already
1347 in <type>tsquery</> form.
1356 <type>tsquery</> && <type>tsquery</>
1362 Returns the AND-combination of the two given queries.
1372 <type>tsquery</> || <type>tsquery</>
1378 Returns the OR-combination of the two given queries.
1394 Returns the negation (NOT) of the given query.
1403 <primary>numnode</primary>
1408 numnode(<replaceable class="PARAMETER">query</replaceable> <type>tsquery</>) returns <type>integer</>
1414 Returns the number of nodes (lexemes plus operators) in a
1415 <type>tsquery</>. This function is useful
1416 to determine if the <replaceable>query</replaceable> is meaningful
1417 (returns > 0), or contains only stop words (returns 0).
1421 SELECT numnode(plainto_tsquery('the any'));
1422 NOTICE: query contains only stopword(s) or doesn't contain lexeme(s), ignored
1427 SELECT numnode('foo & bar'::tsquery);
1439 <primary>querytree</primary>
1444 querytree(<replaceable class="PARAMETER">query</replaceable> <type>tsquery</>) returns <type>text</>
1450 Returns the portion of a <type>tsquery</> that can be used for
1451 searching an index. This function is useful for detecting
1452 unindexable queries, for example those containing only stop words
1453 or only negated terms. For example:
1456 SELECT querytree(to_tsquery('!defined'));
1467 <sect3 id="textsearch-query-rewriting">
1468 <title>Query Rewriting</title>
1470 <indexterm zone="textsearch-query-rewriting">
1471 <primary>ts_rewrite</primary>
1475 The <function>ts_rewrite</function> family of functions search a
1476 given <type>tsquery</> for occurrences of a target
1477 subquery, and replace each occurrence with a
1478 substitute subquery. In essence this operation is a
1479 <type>tsquery</>-specific version of substring replacement.
1480 A target and substitute combination can be
1481 thought of as a <firstterm>query rewrite rule</>. A collection
1482 of such rewrite rules can be a powerful search aid.
1483 For example, you can expand the search using synonyms
1484 (e.g., <literal>new york</>, <literal>big apple</>, <literal>nyc</>,
1485 <literal>gotham</>) or narrow the search to direct the user to some hot
1486 topic. There is some overlap in functionality between this feature
1487 and thesaurus dictionaries (<xref linkend="textsearch-thesaurus">).
1488 However, you can modify a set of rewrite rules on-the-fly without
1489 reindexing, whereas updating a thesaurus requires reindexing to be
1499 ts_rewrite (<replaceable class="PARAMETER">query</replaceable> <type>tsquery</>, <replaceable class="PARAMETER">target</replaceable> <type>tsquery</>, <replaceable class="PARAMETER">substitute</replaceable> <type>tsquery</>) returns <type>tsquery</>
1505 This form of <function>ts_rewrite</> simply applies a single
1506 rewrite rule: <replaceable class="PARAMETER">target</replaceable>
1507 is replaced by <replaceable class="PARAMETER">substitute</replaceable>
1508 wherever it appears in <replaceable
1509 class="PARAMETER">query</replaceable>. For example:
1512 SELECT ts_rewrite('a & b'::tsquery, 'a'::tsquery, 'c'::tsquery);
1525 ts_rewrite (<replaceable class="PARAMETER">query</> <type>tsquery</>, <replaceable class="PARAMETER">select</> <type>text</>) returns <type>tsquery</>
1531 This form of <function>ts_rewrite</> accepts a starting
1532 <replaceable>query</> and a SQL <replaceable>select</> command, which
1533 is given as a text string. The <replaceable>select</> must yield two
1534 columns of <type>tsquery</> type. For each row of the
1535 <replaceable>select</> result, occurrences of the first column value
1536 (the target) are replaced by the second column value (the substitute)
1537 within the current <replaceable>query</> value. For example:
1540 CREATE TABLE aliases (t tsquery PRIMARY KEY, s tsquery);
1541 INSERT INTO aliases VALUES('a', 'c');
1543 SELECT ts_rewrite('a & b'::tsquery, 'SELECT t,s FROM aliases');
1551 Note that when multiple rewrite rules are applied in this way,
1552 the order of application can be important; so in practice you will
1553 want the source query to <literal>ORDER BY</> some ordering key.
1561 Let's consider a real-life astronomical example. We'll expand query
1562 <literal>supernovae</literal> using table-driven rewriting rules:
1565 CREATE TABLE aliases (t tsquery primary key, s tsquery);
1566 INSERT INTO aliases VALUES(to_tsquery('supernovae'), to_tsquery('supernovae|sn'));
1568 SELECT ts_rewrite(to_tsquery('supernovae & crab'), 'SELECT * FROM aliases');
1570 ---------------------------------
1571 'crab' & ( 'supernova' | 'sn' )
1574 We can change the rewriting rules just by updating the table:
1578 SET s = to_tsquery('supernovae|sn & !nebulae')
1579 WHERE t = to_tsquery('supernovae');
1581 SELECT ts_rewrite(to_tsquery('supernovae & crab'), 'SELECT * FROM aliases');
1583 ---------------------------------------------
1584 'crab' & ( 'supernova' | 'sn' & !'nebula' )
1589 Rewriting can be slow when there are many rewriting rules, since it
1590 checks every rule for a possible match. To filter out obvious non-candidate
1591 rules we can use the containment operators for the <type>tsquery</type>
1592 type. In the example below, we select only those rules which might match
1596 SELECT ts_rewrite('a & b'::tsquery,
1597 'SELECT t,s FROM aliases WHERE ''a & b''::tsquery @> t');
1608 <sect2 id="textsearch-update-triggers">
1609 <title>Triggers for Automatic Updates</title>
1612 <primary>trigger</primary>
1613 <secondary>for updating a derived tsvector column</secondary>
1617 When using a separate column to store the <type>tsvector</> representation
1618 of your documents, it is necessary to create a trigger to update the
1619 <type>tsvector</> column when the document content columns change.
1620 Two built-in trigger functions are available for this, or you can write
1625 tsvector_update_trigger(<replaceable class="PARAMETER">tsvector_column_name</replaceable>, <replaceable class="PARAMETER">config_name</replaceable>, <replaceable class="PARAMETER">text_column_name</replaceable> <optional>, ... </optional>)
1626 tsvector_update_trigger_column(<replaceable class="PARAMETER">tsvector_column_name</replaceable>, <replaceable class="PARAMETER">config_column_name</replaceable>, <replaceable class="PARAMETER">text_column_name</replaceable> <optional>, ... </optional>)
1630 These trigger functions automatically compute a <type>tsvector</>
1631 column from one or more textual columns, under the control of
1632 parameters specified in the <command>CREATE TRIGGER</> command.
1633 An example of their use is:
1636 CREATE TABLE messages (
1642 CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
1643 ON messages FOR EACH ROW EXECUTE PROCEDURE
1644 tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);
1646 INSERT INTO messages VALUES('title here', 'the body text is here');
1648 SELECT * FROM messages;
1650 ------------+-----------------------+----------------------------
1651 title here | the body text is here | 'bodi':4 'text':5 'titl':1
1653 SELECT title, body FROM messages WHERE tsv @@ to_tsquery('title & body');
1655 ------------+-----------------------
1656 title here | the body text is here
1659 Having created this trigger, any change in <structfield>title</> or
1660 <structfield>body</> will automatically be reflected into
1661 <structfield>tsv</>, without the application having to worry about it.
1665 The first trigger argument must be the name of the <type>tsvector</>
1666 column to be updated. The second argument specifies the text search
1667 configuration to be used to perform the conversion. For
1668 <function>tsvector_update_trigger</>, the configuration name is simply
1669 given as the second trigger argument. It must be schema-qualified as
1670 shown above, so that the trigger behavior will not change with changes
1671 in <varname>search_path</>. For
1672 <function>tsvector_update_trigger_column</>, the second trigger argument
1673 is the name of another table column, which must be of type
1674 <type>regconfig</>. This allows a per-row selection of configuration
1675 to be made. The remaining argument(s) are the names of textual columns
1676 (of type <type>text</>, <type>varchar</>, or <type>char</>). These
1677 will be included in the document in the order given. NULL values will
1678 be skipped (but the other columns will still be indexed).
1682 A limitation of these built-in triggers is that they treat all the
1683 input columns alike. To process columns differently — for
1684 example, to weight title differently from body — it is necessary
1685 to write a custom trigger. Here is an example using
1686 <application>PL/pgSQL</application> as the trigger language:
1689 CREATE FUNCTION messages_trigger() RETURNS trigger AS $$
1692 setweight(to_tsvector('pg_catalog.english', coalesce(new.title,'')), 'A') ||
1693 setweight(to_tsvector('pg_catalog.english', coalesce(new.body,'')), 'D');
1696 $$ LANGUAGE plpgsql;
1698 CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
1699 ON messages FOR EACH ROW EXECUTE PROCEDURE messages_trigger();
1704 Keep in mind that it is important to specify the configuration name
1705 explicitly when creating <type>tsvector</> values inside triggers,
1706 so that the column's contents will not be affected by changes to
1707 <varname>default_text_search_config</>. Failure to do this is likely to
1708 lead to problems such as search results changing after a dump and reload.
1713 <sect2 id="textsearch-statistics">
1714 <title>Gathering Document Statistics</title>
1717 <primary>ts_stat</primary>
1721 The function <function>ts_stat</> is useful for checking your
1722 configuration and for finding stop-word candidates.
1726 ts_stat(<replaceable class="PARAMETER">sqlquery</replaceable> <type>text</>, <optional> <replaceable class="PARAMETER">weights</replaceable> <type>text</>, </optional>
1727 OUT <replaceable class="PARAMETER">word</replaceable> <type>text</>, OUT <replaceable class="PARAMETER">ndoc</replaceable> <type>integer</>,
1728 OUT <replaceable class="PARAMETER">nentry</replaceable> <type>integer</>) returns <type>setof record</>
1732 <replaceable>sqlquery</replaceable> is a text value containing an SQL
1733 query which must return a single <type>tsvector</type> column.
1734 <function>ts_stat</> executes the query and returns statistics about
1735 each distinct lexeme (word) contained in the <type>tsvector</type>
1736 data. The columns returned are
1738 <itemizedlist spacing="compact" mark="bullet">
1741 <replaceable>word</> <type>text</> — the value of a lexeme
1746 <replaceable>ndoc</> <type>integer</> — number of documents
1747 (<type>tsvector</>s) the word occurred in
1752 <replaceable>nentry</> <type>integer</> — total number of
1753 occurrences of the word
1758 If <replaceable>weights</replaceable> is supplied, only occurrences
1759 having one of those weights are counted.
1763 For example, to find the ten most frequent words in a document collection:
1766 SELECT * FROM ts_stat('SELECT vector FROM apod')
1767 ORDER BY nentry DESC, ndoc DESC, word
1771 The same, but counting only word occurrences with weight <literal>A</>
1775 SELECT * FROM ts_stat('SELECT vector FROM apod', 'ab')
1776 ORDER BY nentry DESC, ndoc DESC, word
1785 <sect1 id="textsearch-parsers">
1786 <title>Parsers</title>
1789 Text search parsers are responsible for splitting raw document text
1790 into <firstterm>tokens</> and identifying each token's type, where
1791 the set of possible types is defined by the parser itself.
1792 Note that a parser does not modify the text at all — it simply
1793 identifies plausible word boundaries. Because of this limited scope,
1794 there is less need for application-specific custom parsers than there is
1795 for custom dictionaries. At present <productname>PostgreSQL</productname>
1796 provides just one built-in parser, which has been found to be useful for a
1797 wide range of applications.
1801 The built-in parser is named <literal>pg_catalog.default</>.
1802 It recognizes 23 token types, shown in <xref linkend="textsearch-default-parser">.
1805 <table id="textsearch-default-parser">
1806 <title>Default Parser's Token Types</title>
1810 <entry>Alias</entry>
1811 <entry>Description</entry>
1812 <entry>Example</entry>
1817 <entry><literal>asciiword</></entry>
1818 <entry>Word, all ASCII letters</entry>
1819 <entry><literal>elephant</literal></entry>
1822 <entry><literal>word</></entry>
1823 <entry>Word, all letters</entry>
1824 <entry><literal>mañana</literal></entry>
1827 <entry><literal>numword</></entry>
1828 <entry>Word, letters and digits</entry>
1829 <entry><literal>beta1</literal></entry>
1832 <entry><literal>asciihword</></entry>
1833 <entry>Hyphenated word, all ASCII</entry>
1834 <entry><literal>up-to-date</literal></entry>
1837 <entry><literal>hword</></entry>
1838 <entry>Hyphenated word, all letters</entry>
1839 <entry><literal>lógico-matemática</literal></entry>
1842 <entry><literal>numhword</></entry>
1843 <entry>Hyphenated word, letters and digits</entry>
1844 <entry><literal>postgresql-beta1</literal></entry>
1847 <entry><literal>hword_asciipart</></entry>
1848 <entry>Hyphenated word part, all ASCII</entry>
1849 <entry><literal>postgresql</literal> in the context <literal>postgresql-beta1</literal></entry>
1852 <entry><literal>hword_part</></entry>
1853 <entry>Hyphenated word part, all letters</entry>
1854 <entry><literal>lógico</literal> or <literal>matemática</literal>
1855 in the context <literal>lógico-matemática</literal></entry>
1858 <entry><literal>hword_numpart</></entry>
1859 <entry>Hyphenated word part, letters and digits</entry>
1860 <entry><literal>beta1</literal> in the context
1861 <literal>postgresql-beta1</literal></entry>
1864 <entry><literal>email</></entry>
1865 <entry>Email address</entry>
1866 <entry><literal>foo@example.com</literal></entry>
1869 <entry><literal>protocol</></entry>
1870 <entry>Protocol head</entry>
1871 <entry><literal>http://</literal></entry>
1874 <entry><literal>url</></entry>
1876 <entry><literal>example.com/stuff/index.html</literal></entry>
1879 <entry><literal>host</></entry>
1881 <entry><literal>example.com</literal></entry>
1884 <entry><literal>url_path</></entry>
1885 <entry>URL path</entry>
1886 <entry><literal>/stuff/index.html</literal>, in the context of a URL</entry>
1889 <entry><literal>file</></entry>
1890 <entry>File or path name</entry>
1891 <entry><literal>/usr/local/foo.txt</literal>, if not within a URL</entry>
1894 <entry><literal>sfloat</></entry>
1895 <entry>Scientific notation</entry>
1896 <entry><literal>-1.234e56</literal></entry>
1899 <entry><literal>float</></entry>
1900 <entry>Decimal notation</entry>
1901 <entry><literal>-1.234</literal></entry>
1904 <entry><literal>int</></entry>
1905 <entry>Signed integer</entry>
1906 <entry><literal>-1234</literal></entry>
1909 <entry><literal>uint</></entry>
1910 <entry>Unsigned integer</entry>
1911 <entry><literal>1234</literal></entry>
1914 <entry><literal>version</></entry>
1915 <entry>Version number</entry>
1916 <entry><literal>8.3.0</literal></entry>
1919 <entry><literal>tag</></entry>
1920 <entry>XML tag</entry>
1921 <entry><literal><a href="dictionaries.html"></literal></entry>
1924 <entry><literal>entity</></entry>
1925 <entry>XML entity</entry>
1926 <entry><literal>&amp;</literal></entry>
1929 <entry><literal>blank</></entry>
1930 <entry>Space symbols</entry>
1931 <entry>(any whitespace or punctuation not otherwise recognized)</entry>
1939 The parser's notion of a <quote>letter</> is determined by the database's
1940 locale setting, specifically <varname>lc_ctype</>. Words containing
1941 only the basic ASCII letters are reported as a separate token type,
1942 since it is sometimes useful to distinguish them. In most European
1943 languages, token types <literal>word</> and <literal>asciiword</>
1944 should be treated alike.
1948 <literal>email</> does not support all valid email characters as
1949 defined by RFC 5322. Specifically, the only non-alphanumeric
1950 characters supported for email user names are period, dash, and
1956 It is possible for the parser to produce overlapping tokens from the same
1957 piece of text. As an example, a hyphenated word will be reported both
1958 as the entire word and as each component:
1961 SELECT alias, description, token FROM ts_debug('foo-bar-beta1');
1962 alias | description | token
1963 -----------------+------------------------------------------+---------------
1964 numhword | Hyphenated word, letters and digits | foo-bar-beta1
1965 hword_asciipart | Hyphenated word part, all ASCII | foo
1966 blank | Space symbols | -
1967 hword_asciipart | Hyphenated word part, all ASCII | bar
1968 blank | Space symbols | -
1969 hword_numpart | Hyphenated word part, letters and digits | beta1
1972 This behavior is desirable since it allows searches to work for both
1973 the whole compound word and for components. Here is another
1974 instructive example:
1977 SELECT alias, description, token FROM ts_debug('http://example.com/stuff/index.html');
1978 alias | description | token
1979 ----------+---------------+------------------------------
1980 protocol | Protocol head | http://
1981 url | URL | example.com/stuff/index.html
1982 host | Host | example.com
1983 url_path | URL path | /stuff/index.html
1989 <sect1 id="textsearch-dictionaries">
1990 <title>Dictionaries</title>
1993 Dictionaries are used to eliminate words that should not be considered in a
1994 search (<firstterm>stop words</>), and to <firstterm>normalize</> words so
1995 that different derived forms of the same word will match. A successfully
1996 normalized word is called a <firstterm>lexeme</>. Aside from
1997 improving search quality, normalization and removal of stop words reduce the
1998 size of the <type>tsvector</type> representation of a document, thereby
1999 improving performance. Normalization does not always have linguistic meaning
2000 and usually depends on application semantics.
2004 Some examples of normalization:
2006 <itemizedlist spacing="compact" mark="bullet">
2010 Linguistic - Ispell dictionaries try to reduce input words to a
2011 normalized form; stemmer dictionaries remove word endings
2016 <acronym>URL</acronym> locations can be canonicalized to make
2017 equivalent URLs match:
2019 <itemizedlist spacing="compact" mark="bullet">
2022 http://www.pgsql.ru/db/mw/index.html
2027 http://www.pgsql.ru/db/mw/
2032 http://www.pgsql.ru/db/../db/mw/index.html
2040 Color names can be replaced by their hexadecimal values, e.g.,
2041 <literal>red, green, blue, magenta -> FF0000, 00FF00, 0000FF, FF00FF</literal>
2046 If indexing numbers, we can
2047 remove some fractional digits to reduce the range of possible
2048 numbers, so for example <emphasis>3.14</emphasis>159265359,
2049 <emphasis>3.14</emphasis>15926, <emphasis>3.14</emphasis> will be the same
2050 after normalization if only two digits are kept after the decimal point.
2058 A dictionary is a program that accepts a token as
2060 <itemizedlist spacing="compact" mark="bullet">
2063 an array of lexemes if the input token is known to the dictionary
2064 (notice that one token can produce more than one lexeme)
2069 a single lexeme with the <literal>TSL_FILTER</> flag set, to replace
2070 the original token with a new token to be passed to subsequent
2071 dictionaries (a dictionary that does this is called a
2072 <firstterm>filtering dictionary</>)
2077 an empty array if the dictionary knows the token, but it is a stop word
2082 <literal>NULL</literal> if the dictionary does not recognize the input token
2089 <productname>PostgreSQL</productname> provides predefined dictionaries for
2090 many languages. There are also several predefined templates that can be
2091 used to create new dictionaries with custom parameters. Each predefined
2092 dictionary template is described below. If no existing
2093 template is suitable, it is possible to create new ones; see the
2094 <filename>contrib/</> area of the <productname>PostgreSQL</> distribution
2099 A text search configuration binds a parser together with a set of
2100 dictionaries to process the parser's output tokens. For each token
2101 type that the parser can return, a separate list of dictionaries is
2102 specified by the configuration. When a token of that type is found
2103 by the parser, each dictionary in the list is consulted in turn,
2104 until some dictionary recognizes it as a known word. If it is identified
2105 as a stop word, or if no dictionary recognizes the token, it will be
2106 discarded and not indexed or searched for.
2107 Normally, the first dictionary that returns a non-<literal>NULL</>
2108 output determines the result, and any remaining dictionaries are not
2109 consulted; but a filtering dictionary can replace the given word
2110 with a modified word, which is then passed to subsequent dictionaries.
2114 The general rule for configuring a list of dictionaries
2115 is to place first the most narrow, most specific dictionary, then the more
2116 general dictionaries, finishing with a very general dictionary, like
2117 a <application>Snowball</> stemmer or <literal>simple</>, which
2118 recognizes everything. For example, for an astronomy-specific search
2119 (<literal>astro_en</literal> configuration) one could bind token type
2120 <type>asciiword</type> (ASCII word) to a synonym dictionary of astronomical
2121 terms, a general English dictionary and a <application>Snowball</> English
2125 ALTER TEXT SEARCH CONFIGURATION astro_en
2126 ADD MAPPING FOR asciiword WITH astrosyn, english_ispell, english_stem;
2131 A filtering dictionary can be placed anywhere in the list, except at the
2132 end where it'd be useless. Filtering dictionaries are useful to partially
2133 normalize words to simplify the task of later dictionaries. For example,
2134 a filtering dictionary could be used to remove accents from accented
2135 letters, as is done by the <xref linkend="unaccent"> module.
2138 <sect2 id="textsearch-stopwords">
2139 <title>Stop Words</title>
2142 Stop words are words that are very common, appear in almost every
2143 document, and have no discrimination value. Therefore, they can be ignored
2144 in the context of full text searching. For example, every English text
2145 contains words like <literal>a</literal> and <literal>the</>, so it is
2146 useless to store them in an index. However, stop words do affect the
2147 positions in <type>tsvector</type>, which in turn affect ranking:
2150 SELECT to_tsvector('english','in the list of stop words');
2152 ----------------------------
2153 'list':3 'stop':5 'word':6
2156 The missing positions 1,2,4 are because of stop words. Ranks
2157 calculated for documents with and without stop words are quite different:
2160 SELECT ts_rank_cd (to_tsvector('english','in the list of stop words'), to_tsquery('list & stop'));
2165 SELECT ts_rank_cd (to_tsvector('english','list stop words'), to_tsquery('list & stop'));
2174 It is up to the specific dictionary how it treats stop words. For example,
2175 <literal>ispell</literal> dictionaries first normalize words and then
2176 look at the list of stop words, while <literal>Snowball</literal> stemmers
2177 first check the list of stop words. The reason for the different
2178 behavior is an attempt to decrease noise.
2183 <sect2 id="textsearch-simple-dictionary">
2184 <title>Simple Dictionary</title>
2187 The <literal>simple</> dictionary template operates by converting the
2188 input token to lower case and checking it against a file of stop words.
2189 If it is found in the file then an empty array is returned, causing
2190 the token to be discarded. If not, the lower-cased form of the word
2191 is returned as the normalized lexeme. Alternatively, the dictionary
2192 can be configured to report non-stop-words as unrecognized, allowing
2193 them to be passed on to the next dictionary in the list.
2197 Here is an example of a dictionary definition using the <literal>simple</>
2201 CREATE TEXT SEARCH DICTIONARY public.simple_dict (
2202 TEMPLATE = pg_catalog.simple,
2207 Here, <literal>english</literal> is the base name of a file of stop words.
2208 The file's full name will be
2209 <filename>$SHAREDIR/tsearch_data/english.stop</>,
2210 where <literal>$SHAREDIR</> means the
2211 <productname>PostgreSQL</productname> installation's shared-data directory,
2212 often <filename>/usr/local/share/postgresql</> (use <command>pg_config
2213 --sharedir</> to determine it if you're not sure).
2214 The file format is simply a list
2215 of words, one per line. Blank lines and trailing spaces are ignored,
2216 and upper case is folded to lower case, but no other processing is done
2217 on the file contents.
2221 Now we can test our dictionary:
2224 SELECT ts_lexize('public.simple_dict','YeS');
2229 SELECT ts_lexize('public.simple_dict','The');
2237 We can also choose to return <literal>NULL</>, instead of the lower-cased
2238 word, if it is not found in the stop words file. This behavior is
2239 selected by setting the dictionary's <literal>Accept</> parameter to
2240 <literal>false</>. Continuing the example:
2243 ALTER TEXT SEARCH DICTIONARY public.simple_dict ( Accept = false );
2245 SELECT ts_lexize('public.simple_dict','YeS');
2250 SELECT ts_lexize('public.simple_dict','The');
2258 With the default setting of <literal>Accept</> = <literal>true</>,
2259 it is only useful to place a <literal>simple</> dictionary at the end
2260 of a list of dictionaries, since it will never pass on any token to
2261 a following dictionary. Conversely, <literal>Accept</> = <literal>false</>
2262 is only useful when there is at least one following dictionary.
2267 Most types of dictionaries rely on configuration files, such as files of
2268 stop words. These files <emphasis>must</> be stored in UTF-8 encoding.
2269 They will be translated to the actual database encoding, if that is
2270 different, when they are read into the server.
2276 Normally, a database session will read a dictionary configuration file
2277 only once, when it is first used within the session. If you modify a
2278 configuration file and want to force existing sessions to pick up the
2279 new contents, issue an <command>ALTER TEXT SEARCH DICTIONARY</> command
2280 on the dictionary. This can be a <quote>dummy</> update that doesn't
2281 actually change any parameter values.
2287 <sect2 id="textsearch-synonym-dictionary">
2288 <title>Synonym Dictionary</title>
2291 This dictionary template is used to create dictionaries that replace a
2292 word with a synonym. Phrases are not supported (use the thesaurus
2293 template (<xref linkend="textsearch-thesaurus">) for that). A synonym
2294 dictionary can be used to overcome linguistic problems, for example, to
2295 prevent an English stemmer dictionary from reducing the word <quote>Paris</quote> to
2296 <quote>pari</quote>. It is enough to have a <literal>Paris paris</literal> line in the
2297 synonym dictionary and put it before the <literal>english_stem</>
2298 dictionary. For example:
2301 SELECT * FROM ts_debug('english', 'Paris');
2302 alias | description | token | dictionaries | dictionary | lexemes
2303 -----------+-----------------+-------+----------------+--------------+---------
2304 asciiword | Word, all ASCII | Paris | {english_stem} | english_stem | {pari}
2306 CREATE TEXT SEARCH DICTIONARY my_synonym (
2308 SYNONYMS = my_synonyms
2311 ALTER TEXT SEARCH CONFIGURATION english
2312 ALTER MAPPING FOR asciiword
2313 WITH my_synonym, english_stem;
2315 SELECT * FROM ts_debug('english', 'Paris');
2316 alias | description | token | dictionaries | dictionary | lexemes
2317 -----------+-----------------+-------+---------------------------+------------+---------
2318 asciiword | Word, all ASCII | Paris | {my_synonym,english_stem} | my_synonym | {paris}
2323 The only parameter required by the <literal>synonym</> template is
2324 <literal>SYNONYMS</>, which is the base name of its configuration file
2325 — <literal>my_synonyms</> in the above example.
2326 The file's full name will be
2327 <filename>$SHAREDIR/tsearch_data/my_synonyms.syn</>
2328 (where <literal>$SHAREDIR</> means the
2329 <productname>PostgreSQL</> installation's shared-data directory).
2330 The file format is just one line
2331 per word to be substituted, with the word followed by its synonym,
2332 separated by white space. Blank lines and trailing spaces are ignored.
2336 The <literal>synonym</> template also has an optional parameter
2337 <literal>CaseSensitive</>, which defaults to <literal>false</>. When
2338 <literal>CaseSensitive</> is <literal>false</>, words in the synonym file
2339 are folded to lower case, as are input tokens. When it is
2340 <literal>true</>, words and tokens are not folded to lower case,
2341 but are compared as-is.
2345 An asterisk (<literal>*</literal>) can be placed at the end of a synonym
2346 in the configuration file. This indicates that the synonym is a prefix.
2347 The asterisk is ignored when the entry is used in
2348 <function>to_tsvector()</function>, but when it is used in
2349 <function>to_tsquery()</function>, the result will be a query item with
2350 the prefix match marker (see
2351 <xref linkend="textsearch-parsing-queries">).
2352 For example, suppose we have these entries in
2353 <filename>$SHAREDIR/tsearch_data/synonym_sample.syn</>:
2361 Then we will get these results:
2363 mydb=# CREATE TEXT SEARCH DICTIONARY syn (template=synonym, synonyms='synonym_sample');
2364 mydb=# SELECT ts_lexize('syn','indices');
2370 mydb=# CREATE TEXT SEARCH CONFIGURATION tst (copy=simple);
2371 mydb=# ALTER TEXT SEARCH CONFIGURATION tst ALTER MAPPING FOR asciiword WITH syn;
2372 mydb=# SELECT to_tsvector('tst','indices');
2378 mydb=# SELECT to_tsquery('tst','indices');
2384 mydb=# SELECT 'indexes are very useful'::tsvector;
2386 ---------------------------------
2387 'are' 'indexes' 'useful' 'very'
2390 mydb=# SELECT 'indexes are very useful'::tsvector @@ to_tsquery('tst','indices');
2399 <sect2 id="textsearch-thesaurus">
2400 <title>Thesaurus Dictionary</title>
2403 A thesaurus dictionary (sometimes abbreviated as <acronym>TZ</acronym>) is
2404 a collection of words that includes information about the relationships
2405 of words and phrases, i.e., broader terms (<acronym>BT</acronym>), narrower
2406 terms (<acronym>NT</acronym>), preferred terms, non-preferred terms, related
2411 Basically a thesaurus dictionary replaces all non-preferred terms by one
2412 preferred term and, optionally, preserves the original terms for indexing
2413 as well. <productname>PostgreSQL</>'s current implementation of the
2414 thesaurus dictionary is an extension of the synonym dictionary with added
2415 <firstterm>phrase</firstterm> support. A thesaurus dictionary requires
2416 a configuration file of the following format:
2420 sample word(s) : indexed word(s)
2421 more sample word(s) : more indexed word(s)
2425 where the colon (<symbol>:</symbol>) symbol acts as a delimiter between a
2426 a phrase and its replacement.
2430 A thesaurus dictionary uses a <firstterm>subdictionary</firstterm> (which
2431 is specified in the dictionary's configuration) to normalize the input
2432 text before checking for phrase matches. It is only possible to select one
2433 subdictionary. An error is reported if the subdictionary fails to
2434 recognize a word. In that case, you should remove the use of the word or
2435 teach the subdictionary about it. You can place an asterisk
2436 (<symbol>*</symbol>) at the beginning of an indexed word to skip applying
2437 the subdictionary to it, but all sample words <emphasis>must</> be known
2438 to the subdictionary.
2442 The thesaurus dictionary chooses the longest match if there are multiple
2443 phrases matching the input, and ties are broken by using the last
2448 Specific stop words recognized by the subdictionary cannot be
2449 specified; instead use <literal>?</> to mark the location where any
2450 stop word can appear. For example, assuming that <literal>a</> and
2451 <literal>the</> are stop words according to the subdictionary:
2457 matches <literal>a one the two</> and <literal>the one a two</>;
2458 both would be replaced by <literal>swsw</>.
2462 Since a thesaurus dictionary has the capability to recognize phrases it
2463 must remember its state and interact with the parser. A thesaurus dictionary
2464 uses these assignments to check if it should handle the next word or stop
2465 accumulation. The thesaurus dictionary must be configured
2466 carefully. For example, if the thesaurus dictionary is assigned to handle
2467 only the <literal>asciiword</literal> token, then a thesaurus dictionary
2468 definition like <literal>one 7</> will not work since token type
2469 <literal>uint</literal> is not assigned to the thesaurus dictionary.
2474 Thesauruses are used during indexing so any change in the thesaurus
2475 dictionary's parameters <emphasis>requires</emphasis> reindexing.
2476 For most other dictionary types, small changes such as adding or
2477 removing stopwords does not force reindexing.
2481 <sect3 id="textsearch-thesaurus-config">
2482 <title>Thesaurus Configuration</title>
2485 To define a new thesaurus dictionary, use the <literal>thesaurus</>
2486 template. For example:
2489 CREATE TEXT SEARCH DICTIONARY thesaurus_simple (
2490 TEMPLATE = thesaurus,
2491 DictFile = mythesaurus,
2492 Dictionary = pg_catalog.english_stem
2497 <itemizedlist spacing="compact" mark="bullet">
2500 <literal>thesaurus_simple</literal> is the new dictionary's name
2505 <literal>mythesaurus</literal> is the base name of the thesaurus
2507 (Its full name will be <filename>$SHAREDIR/tsearch_data/mythesaurus.ths</>,
2508 where <literal>$SHAREDIR</> means the installation shared-data
2514 <literal>pg_catalog.english_stem</literal> is the subdictionary (here,
2515 a Snowball English stemmer) to use for thesaurus normalization.
2516 Notice that the subdictionary will have its own
2517 configuration (for example, stop words), which is not shown here.
2522 Now it is possible to bind the thesaurus dictionary <literal>thesaurus_simple</literal>
2523 to the desired token types in a configuration, for example:
2526 ALTER TEXT SEARCH CONFIGURATION russian
2527 ALTER MAPPING FOR asciiword, asciihword, hword_asciipart
2528 WITH thesaurus_simple;
2534 <sect3 id="textsearch-thesaurus-examples">
2535 <title>Thesaurus Example</title>
2538 Consider a simple astronomical thesaurus <literal>thesaurus_astro</literal>,
2539 which contains some astronomical word combinations:
2542 supernovae stars : sn
2546 Below we create a dictionary and bind some token types to
2547 an astronomical thesaurus and English stemmer:
2550 CREATE TEXT SEARCH DICTIONARY thesaurus_astro (
2551 TEMPLATE = thesaurus,
2552 DictFile = thesaurus_astro,
2553 Dictionary = english_stem
2556 ALTER TEXT SEARCH CONFIGURATION russian
2557 ALTER MAPPING FOR asciiword, asciihword, hword_asciipart
2558 WITH thesaurus_astro, english_stem;
2561 Now we can see how it works.
2562 <function>ts_lexize</function> is not very useful for testing a thesaurus,
2563 because it treats its input as a single token. Instead we can use
2564 <function>plainto_tsquery</function> and <function>to_tsvector</function>
2565 which will break their input strings into multiple tokens:
2568 SELECT plainto_tsquery('supernova star');
2573 SELECT to_tsvector('supernova star');
2579 In principle, one can use <function>to_tsquery</function> if you quote
2583 SELECT to_tsquery('''supernova star''');
2589 Notice that <literal>supernova star</literal> matches <literal>supernovae
2590 stars</literal> in <literal>thesaurus_astro</literal> because we specified
2591 the <literal>english_stem</literal> stemmer in the thesaurus definition.
2592 The stemmer removed the <literal>e</> and <literal>s</>.
2596 To index the original phrase as well as the substitute, just include it
2597 in the right-hand part of the definition:
2600 supernovae stars : sn supernovae stars
2602 SELECT plainto_tsquery('supernova star');
2604 -----------------------------
2605 'sn' & 'supernova' & 'star'
2613 <sect2 id="textsearch-ispell-dictionary">
2614 <title><application>Ispell</> Dictionary</title>
2617 The <application>Ispell</> dictionary template supports
2618 <firstterm>morphological dictionaries</>, which can normalize many
2619 different linguistic forms of a word into the same lexeme. For example,
2620 an English <application>Ispell</> dictionary can match all declensions and
2621 conjugations of the search term <literal>bank</literal>, e.g.,
2622 <literal>banking</>, <literal>banked</>, <literal>banks</>,
2623 <literal>banks'</>, and <literal>bank's</>.
2627 The standard <productname>PostgreSQL</productname> distribution does
2628 not include any <application>Ispell</> configuration files.
2629 Dictionaries for a large number of languages are available from <ulink
2630 url="http://ficus-www.cs.ucla.edu/geoff/ispell.html">Ispell</ulink>.
2631 Also, some more modern dictionary file formats are supported — <ulink
2632 url="http://en.wikipedia.org/wiki/MySpell">MySpell</ulink> (OO < 2.0.1)
2633 and <ulink url="http://sourceforge.net/projects/hunspell/">Hunspell</ulink>
2634 (OO >= 2.0.2). A large list of dictionaries is available on the <ulink
2635 url="http://wiki.services.openoffice.org/wiki/Dictionaries">OpenOffice
2640 To create an <application>Ispell</> dictionary, use the built-in
2641 <literal>ispell</literal> template and specify several parameters:
2645 CREATE TEXT SEARCH DICTIONARY english_ispell (
2654 Here, <literal>DictFile</>, <literal>AffFile</>, and <literal>StopWords</>
2655 specify the base names of the dictionary, affixes, and stop-words files.
2656 The stop-words file has the same format explained above for the
2657 <literal>simple</> dictionary type. The format of the other files is
2658 not specified here but is available from the above-mentioned web sites.
2662 Ispell dictionaries usually recognize a limited set of words, so they
2663 should be followed by another broader dictionary; for
2664 example, a Snowball dictionary, which recognizes everything.
2668 Ispell dictionaries support splitting compound words;
2670 Notice that the affix file should specify a special flag using the
2671 <literal>compoundwords controlled</literal> statement that marks dictionary
2672 words that can participate in compound formation:
2675 compoundwords controlled z
2678 Here are some examples for the Norwegian language:
2681 SELECT ts_lexize('norwegian_ispell', 'overbuljongterningpakkmesterassistent');
2682 {over,buljong,terning,pakk,mester,assistent}
2683 SELECT ts_lexize('norwegian_ispell', 'sjokoladefabrikk');
2684 {sjokoladefabrikk,sjokolade,fabrikk}
2690 <application>MySpell</> does not support compound words.
2691 <application>Hunspell</> has sophisticated support for compound words. At
2692 present, <productname>PostgreSQL</productname> implements only the basic
2693 compound word operations of Hunspell.
2699 <sect2 id="textsearch-snowball-dictionary">
2700 <title><application>Snowball</> Dictionary</title>
2703 The <application>Snowball</> dictionary template is based on a project
2704 by Martin Porter, inventor of the popular Porter's stemming algorithm
2705 for the English language. Snowball now provides stemming algorithms for
2706 many languages (see the <ulink url="http://snowball.tartarus.org">Snowball
2707 site</ulink> for more information). Each algorithm understands how to
2708 reduce common variant forms of words to a base, or stem, spelling within
2709 its language. A Snowball dictionary requires a <literal>language</>
2710 parameter to identify which stemmer to use, and optionally can specify a
2711 <literal>stopword</> file name that gives a list of words to eliminate.
2712 (<productname>PostgreSQL</productname>'s standard stopword lists are also
2713 provided by the Snowball project.)
2714 For example, there is a built-in definition equivalent to
2717 CREATE TEXT SEARCH DICTIONARY english_stem (
2718 TEMPLATE = snowball,
2724 The stopword file format is the same as already explained.
2728 A <application>Snowball</> dictionary recognizes everything, whether
2729 or not it is able to simplify the word, so it should be placed
2730 at the end of the dictionary list. It is useless to have it
2731 before any other dictionary because a token will never pass through it to
2732 the next dictionary.
2739 <sect1 id="textsearch-configuration">
2740 <title>Configuration Example</title>
2743 A text search configuration specifies all options necessary to transform a
2744 document into a <type>tsvector</type>: the parser to use to break text
2745 into tokens, and the dictionaries to use to transform each token into a
2746 lexeme. Every call of
2747 <function>to_tsvector</function> or <function>to_tsquery</function>
2748 needs a text search configuration to perform its processing.
2749 The configuration parameter
2750 <xref linkend="guc-default-text-search-config">
2751 specifies the name of the default configuration, which is the
2752 one used by text search functions if an explicit configuration
2753 parameter is omitted.
2754 It can be set in <filename>postgresql.conf</filename>, or set for an
2755 individual session using the <command>SET</> command.
2759 Several predefined text search configurations are available, and
2760 you can create custom configurations easily. To facilitate management
2761 of text search objects, a set of <acronym>SQL</acronym> commands
2762 is available, and there are several <application>psql</application> commands that display information
2763 about text search objects (<xref linkend="textsearch-psql">).
2767 As an example we will create a configuration
2768 <literal>pg</literal>, starting by duplicating the built-in
2769 <literal>english</> configuration:
2772 CREATE TEXT SEARCH CONFIGURATION public.pg ( COPY = pg_catalog.english );
2777 We will use a PostgreSQL-specific synonym list
2778 and store it in <filename>$SHAREDIR/tsearch_data/pg_dict.syn</filename>.
2779 The file contents look like:
2787 We define the synonym dictionary like this:
2790 CREATE TEXT SEARCH DICTIONARY pg_dict (
2796 Next we register the <productname>Ispell</> dictionary
2797 <literal>english_ispell</literal>, which has its own configuration files:
2800 CREATE TEXT SEARCH DICTIONARY english_ispell (
2808 Now we can set up the mappings for words in configuration
2812 ALTER TEXT SEARCH CONFIGURATION pg
2813 ALTER MAPPING FOR asciiword, asciihword, hword_asciipart,
2814 word, hword, hword_part
2815 WITH pg_dict, english_ispell, english_stem;
2818 We choose not to index or search some token types that the built-in
2819 configuration does handle:
2822 ALTER TEXT SEARCH CONFIGURATION pg
2823 DROP MAPPING FOR email, url, url_path, sfloat, float;
2828 Now we can test our configuration:
2831 SELECT * FROM ts_debug('public.pg', '
2832 PostgreSQL, the highly scalable, SQL compliant, open source object-relational
2833 database management system, is now undergoing beta testing of the next
2834 version of our software.
2840 The next step is to set the session to use the new configuration, which was
2841 created in the <literal>public</> schema:
2845 List of text search configurations
2846 Schema | Name | Description
2847 ---------+------+-------------
2850 SET default_text_search_config = 'public.pg';
2853 SHOW default_text_search_config;
2854 default_text_search_config
2855 ----------------------------
2862 <sect1 id="textsearch-debugging">
2863 <title>Testing and Debugging Text Search</title>
2866 The behavior of a custom text search configuration can easily become
2867 confusing. The functions described
2868 in this section are useful for testing text search objects. You can
2869 test a complete configuration, or test parsers and dictionaries separately.
2872 <sect2 id="textsearch-configuration-testing">
2873 <title>Configuration Testing</title>
2876 The function <function>ts_debug</function> allows easy testing of a
2877 text search configuration.
2881 <primary>ts_debug</primary>
2885 ts_debug(<optional> <replaceable class="PARAMETER">config</replaceable> <type>regconfig</>, </optional> <replaceable class="PARAMETER">document</replaceable> <type>text</>,
2886 OUT <replaceable class="PARAMETER">alias</> <type>text</>,
2887 OUT <replaceable class="PARAMETER">description</> <type>text</>,
2888 OUT <replaceable class="PARAMETER">token</> <type>text</>,
2889 OUT <replaceable class="PARAMETER">dictionaries</> <type>regdictionary[]</>,
2890 OUT <replaceable class="PARAMETER">dictionary</> <type>regdictionary</>,
2891 OUT <replaceable class="PARAMETER">lexemes</> <type>text[]</>)
2892 returns setof record
2896 <function>ts_debug</> displays information about every token of
2897 <replaceable class="PARAMETER">document</replaceable> as produced by the
2898 parser and processed by the configured dictionaries. It uses the
2899 configuration specified by <replaceable
2900 class="PARAMETER">config</replaceable>,
2901 or <varname>default_text_search_config</varname> if that argument is
2906 <function>ts_debug</> returns one row for each token identified in the text
2907 by the parser. The columns returned are
2909 <itemizedlist spacing="compact" mark="bullet">
2912 <replaceable>alias</> <type>text</> — short name of the token type
2917 <replaceable>description</> <type>text</> — description of the
2923 <replaceable>token</> <type>text</> — text of the token
2928 <replaceable>dictionaries</> <type>regdictionary[]</> — the
2929 dictionaries selected by the configuration for this token type
2934 <replaceable>dictionary</> <type>regdictionary</> — the dictionary
2935 that recognized the token, or <literal>NULL</> if none did
2940 <replaceable>lexemes</> <type>text[]</> — the lexeme(s) produced
2941 by the dictionary that recognized the token, or <literal>NULL</> if
2942 none did; an empty array (<literal>{}</>) means it was recognized as a
2950 Here is a simple example:
2953 SELECT * FROM ts_debug('english','a fat cat sat on a mat - it ate a fat rats');
2954 alias | description | token | dictionaries | dictionary | lexemes
2955 -----------+-----------------+-------+----------------+--------------+---------
2956 asciiword | Word, all ASCII | a | {english_stem} | english_stem | {}
2957 blank | Space symbols | | {} | |
2958 asciiword | Word, all ASCII | fat | {english_stem} | english_stem | {fat}
2959 blank | Space symbols | | {} | |
2960 asciiword | Word, all ASCII | cat | {english_stem} | english_stem | {cat}
2961 blank | Space symbols | | {} | |
2962 asciiword | Word, all ASCII | sat | {english_stem} | english_stem | {sat}
2963 blank | Space symbols | | {} | |
2964 asciiword | Word, all ASCII | on | {english_stem} | english_stem | {}
2965 blank | Space symbols | | {} | |
2966 asciiword | Word, all ASCII | a | {english_stem} | english_stem | {}
2967 blank | Space symbols | | {} | |
2968 asciiword | Word, all ASCII | mat | {english_stem} | english_stem | {mat}
2969 blank | Space symbols | | {} | |
2970 blank | Space symbols | - | {} | |
2971 asciiword | Word, all ASCII | it | {english_stem} | english_stem | {}
2972 blank | Space symbols | | {} | |
2973 asciiword | Word, all ASCII | ate | {english_stem} | english_stem | {ate}
2974 blank | Space symbols | | {} | |
2975 asciiword | Word, all ASCII | a | {english_stem} | english_stem | {}
2976 blank | Space symbols | | {} | |
2977 asciiword | Word, all ASCII | fat | {english_stem} | english_stem | {fat}
2978 blank | Space symbols | | {} | |
2979 asciiword | Word, all ASCII | rats | {english_stem} | english_stem | {rat}
2984 For a more extensive demonstration, we
2985 first create a <literal>public.english</literal> configuration and
2986 Ispell dictionary for the English language:
2990 CREATE TEXT SEARCH CONFIGURATION public.english ( COPY = pg_catalog.english );
2992 CREATE TEXT SEARCH DICTIONARY english_ispell (
2999 ALTER TEXT SEARCH CONFIGURATION public.english
3000 ALTER MAPPING FOR asciiword WITH english_ispell, english_stem;
3004 SELECT * FROM ts_debug('public.english','The Brightest supernovaes');
3005 alias | description | token | dictionaries | dictionary | lexemes
3006 -----------+-----------------+-------------+-------------------------------+----------------+-------------
3007 asciiword | Word, all ASCII | The | {english_ispell,english_stem} | english_ispell | {}
3008 blank | Space symbols | | {} | |
3009 asciiword | Word, all ASCII | Brightest | {english_ispell,english_stem} | english_ispell | {bright}
3010 blank | Space symbols | | {} | |
3011 asciiword | Word, all ASCII | supernovaes | {english_ispell,english_stem} | english_stem | {supernova}
3015 In this example, the word <literal>Brightest</> was recognized by the
3016 parser as an <literal>ASCII word</literal> (alias <literal>asciiword</literal>).
3017 For this token type the dictionary list is
3018 <literal>english_ispell</> and
3019 <literal>english_stem</literal>. The word was recognized by
3020 <literal>english_ispell</literal>, which reduced it to the noun
3021 <literal>bright</literal>. The word <literal>supernovaes</literal> is
3022 unknown to the <literal>english_ispell</literal> dictionary so it
3023 was passed to the next dictionary, and, fortunately, was recognized (in
3024 fact, <literal>english_stem</literal> is a Snowball dictionary which
3025 recognizes everything; that is why it was placed at the end of the
3030 The word <literal>The</literal> was recognized by the
3031 <literal>english_ispell</literal> dictionary as a stop word (<xref
3032 linkend="textsearch-stopwords">) and will not be indexed.
3033 The spaces are discarded too, since the configuration provides no
3034 dictionaries at all for them.
3038 You can reduce the width of the output by explicitly specifying which columns
3042 SELECT alias, token, dictionary, lexemes
3043 FROM ts_debug('public.english','The Brightest supernovaes');
3044 alias | token | dictionary | lexemes
3045 -----------+-------------+----------------+-------------
3046 asciiword | The | english_ispell | {}
3048 asciiword | Brightest | english_ispell | {bright}
3050 asciiword | supernovaes | english_stem | {supernova}
3056 <sect2 id="textsearch-parser-testing">
3057 <title>Parser Testing</title>
3060 The following functions allow direct testing of a text search parser.
3064 <primary>ts_parse</primary>
3068 ts_parse(<replaceable class="PARAMETER">parser_name</replaceable> <type>text</>, <replaceable class="PARAMETER">document</replaceable> <type>text</>,
3069 OUT <replaceable class="PARAMETER">tokid</> <type>integer</>, OUT <replaceable class="PARAMETER">token</> <type>text</>) returns <type>setof record</>
3070 ts_parse(<replaceable class="PARAMETER">parser_oid</replaceable> <type>oid</>, <replaceable class="PARAMETER">document</replaceable> <type>text</>,
3071 OUT <replaceable class="PARAMETER">tokid</> <type>integer</>, OUT <replaceable class="PARAMETER">token</> <type>text</>) returns <type>setof record</>
3075 <function>ts_parse</> parses the given <replaceable>document</replaceable>
3076 and returns a series of records, one for each token produced by
3077 parsing. Each record includes a <varname>tokid</varname> showing the
3078 assigned token type and a <varname>token</varname> which is the text of the
3082 SELECT * FROM ts_parse('default', '123 - a number');
3095 <primary>ts_token_type</primary>
3099 ts_token_type(<replaceable class="PARAMETER">parser_name</> <type>text</>, OUT <replaceable class="PARAMETER">tokid</> <type>integer</>,
3100 OUT <replaceable class="PARAMETER">alias</> <type>text</>, OUT <replaceable class="PARAMETER">description</> <type>text</>) returns <type>setof record</>
3101 ts_token_type(<replaceable class="PARAMETER">parser_oid</> <type>oid</>, OUT <replaceable class="PARAMETER">tokid</> <type>integer</>,
3102 OUT <replaceable class="PARAMETER">alias</> <type>text</>, OUT <replaceable class="PARAMETER">description</> <type>text</>) returns <type>setof record</>
3106 <function>ts_token_type</> returns a table which describes each type of
3107 token the specified parser can recognize. For each token type, the table
3108 gives the integer <varname>tokid</varname> that the parser uses to label a
3109 token of that type, the <varname>alias</varname> that names the token type
3110 in configuration commands, and a short <varname>description</varname>. For
3114 SELECT * FROM ts_token_type('default');
3115 tokid | alias | description
3116 -------+-----------------+------------------------------------------
3117 1 | asciiword | Word, all ASCII
3118 2 | word | Word, all letters
3119 3 | numword | Word, letters and digits
3120 4 | email | Email address
3123 7 | sfloat | Scientific notation
3124 8 | version | Version number
3125 9 | hword_numpart | Hyphenated word part, letters and digits
3126 10 | hword_part | Hyphenated word part, all letters
3127 11 | hword_asciipart | Hyphenated word part, all ASCII
3128 12 | blank | Space symbols
3130 14 | protocol | Protocol head
3131 15 | numhword | Hyphenated word, letters and digits
3132 16 | asciihword | Hyphenated word, all ASCII
3133 17 | hword | Hyphenated word, all letters
3134 18 | url_path | URL path
3135 19 | file | File or path name
3136 20 | float | Decimal notation
3137 21 | int | Signed integer
3138 22 | uint | Unsigned integer
3139 23 | entity | XML entity
3145 <sect2 id="textsearch-dictionary-testing">
3146 <title>Dictionary Testing</title>
3149 The <function>ts_lexize</> function facilitates dictionary testing.
3153 <primary>ts_lexize</primary>
3157 ts_lexize(<replaceable class="PARAMETER">dict</replaceable> <type>regdictionary</>, <replaceable class="PARAMETER">token</replaceable> <type>text</>) returns <type>text[]</>
3161 <function>ts_lexize</> returns an array of lexemes if the input
3162 <replaceable>token</replaceable> is known to the dictionary,
3163 or an empty array if the token
3164 is known to the dictionary but it is a stop word, or
3165 <literal>NULL</literal> if it is an unknown word.
3172 SELECT ts_lexize('english_stem', 'stars');
3177 SELECT ts_lexize('english_stem', 'a');
3186 The <function>ts_lexize</function> function expects a single
3187 <emphasis>token</emphasis>, not text. Here is a case
3188 where this can be confusing:
3191 SELECT ts_lexize('thesaurus_astro','supernovae stars') is null;
3197 The thesaurus dictionary <literal>thesaurus_astro</literal> does know the
3198 phrase <literal>supernovae stars</literal>, but <function>ts_lexize</>
3199 fails since it does not parse the input text but treats it as a single
3200 token. Use <function>plainto_tsquery</> or <function>to_tsvector</> to
3201 test thesaurus dictionaries, for example:
3204 SELECT plainto_tsquery('supernovae stars');
3216 <sect1 id="textsearch-indexes">
3217 <title>GiST and GIN Index Types</title>
3219 <indexterm zone="textsearch-indexes">
3220 <primary>text search</primary>
3221 <secondary>indexes</secondary>
3225 There are two kinds of indexes that can be used to speed up full text
3227 Note that indexes are not mandatory for full text searching, but in
3228 cases where a column is searched on a regular basis, an index is
3235 <indexterm zone="textsearch-indexes">
3236 <primary>index</primary>
3237 <secondary>GiST</secondary>
3238 <tertiary>text search</tertiary>
3243 CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING gist(<replaceable>column</replaceable>);
3249 Creates a GiST (Generalized Search Tree)-based index.
3250 The <replaceable>column</replaceable> can be of <type>tsvector</> or
3251 <type>tsquery</> type.
3258 <indexterm zone="textsearch-indexes">
3259 <primary>index</primary>
3260 <secondary>GIN</secondary>
3261 <tertiary>text search</tertiary>
3266 CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING gin(<replaceable>column</replaceable>);
3272 Creates a GIN (Generalized Inverted Index)-based index.
3273 The <replaceable>column</replaceable> must be of <type>tsvector</> type.
3282 There are substantial performance differences between the two index types,
3283 so it is important to understand their characteristics.
3287 A GiST index is <firstterm>lossy</firstterm>, meaning that the index
3288 may produce false matches, and it is necessary
3289 to check the actual table row to eliminate such false matches.
3290 (<productname>PostgreSQL</productname> does this automatically when needed.)
3291 GiST indexes are lossy because each document is represented in the
3292 index by a fixed-length signature. The signature is generated by hashing
3293 each word into a single bit in an n-bit string, with all these bits OR-ed
3294 together to produce an n-bit document signature. When two words hash to
3295 the same bit position there will be a false match. If all words in
3296 the query have matches (real or false) then the table row must be
3297 retrieved to see if the match is correct.
3301 Lossiness causes performance degradation due to unnecessary fetches of table
3302 records that turn out to be false matches. Since random access to table
3303 records is slow, this limits the usefulness of GiST indexes. The
3304 likelihood of false matches depends on several factors, in particular the
3305 number of unique words, so using dictionaries to reduce this number is
3310 GIN indexes are not lossy for standard queries, but their performance
3311 depends logarithmically on the number of unique words.
3312 (However, GIN indexes store only the words (lexemes) of <type>tsvector</>
3313 values, and not their weight labels. Thus a table row recheck is needed
3314 when using a query that involves weights.)
3318 In choosing which index type to use, GiST or GIN, consider these
3319 performance differences:
3321 <itemizedlist spacing="compact" mark="bullet">
3324 GIN index lookups are about three times faster than GiST
3329 GIN indexes take about three times longer to build than GiST
3334 GIN indexes are moderately slower to update than GiST indexes, but
3335 about 10 times slower if fast-update support was disabled
3336 (see <xref linkend="gin-fast-update"> for details)
3341 GIN indexes are two-to-three times larger than GiST indexes
3348 As a rule of thumb, <acronym>GIN</acronym> indexes are best for static data
3349 because lookups are faster. For dynamic data, GiST indexes are
3350 faster to update. Specifically, <acronym>GiST</acronym> indexes are very
3351 good for dynamic data and fast if the number of unique words (lexemes) is
3352 under 100,000, while <acronym>GIN</acronym> indexes will handle 100,000+
3353 lexemes better but are slower to update.
3357 Note that <acronym>GIN</acronym> index build time can often be improved
3358 by increasing <xref linkend="guc-maintenance-work-mem">, while
3359 <acronym>GiST</acronym> index build time is not sensitive to that
3364 Partitioning of big collections and the proper use of GiST and GIN indexes
3365 allows the implementation of very fast searches with online update.
3366 Partitioning can be done at the database level using table inheritance,
3367 or by distributing documents over
3368 servers and collecting search results using the <xref linkend="dblink">
3369 module. The latter is possible because ranking functions use
3370 only local information.
3375 <sect1 id="textsearch-psql">
3376 <title><application>psql</> Support</title>
3379 Information about text search configuration objects can be obtained
3380 in <application>psql</application> using a set of commands:
3382 \dF{d,p,t}<optional>+</optional> <optional>PATTERN</optional>
3384 An optional <literal>+</literal> produces more details.
3388 The optional parameter <literal>PATTERN</literal> can be the name of
3389 a text search object, optionally schema-qualified. If
3390 <literal>PATTERN</literal> is omitted then information about all
3391 visible objects will be displayed. <literal>PATTERN</literal> can be a
3392 regular expression and can provide <emphasis>separate</emphasis> patterns
3393 for the schema and object names. The following examples illustrate this:
3396 => \dF *fulltext*
3397 List of text search configurations
3398 Schema | Name | Description
3399 --------+--------------+-------------
3400 public | fulltext_cfg |
3404 => \dF *.fulltext*
3405 List of text search configurations
3406 Schema | Name | Description
3407 ----------+----------------------------
3408 fulltext | fulltext_cfg |
3409 public | fulltext_cfg |
3412 The available commands are:
3417 <term><synopsis>\dF<optional>+</optional> <optional>PATTERN</optional></synopsis></term>
3420 List text search configurations (add <literal>+</> for more detail).
3423 List of text search configurations
3424 Schema | Name | Description
3425 ------------+---------+------------------------------------
3426 pg_catalog | russian | configuration for russian language
3429 Text search configuration "pg_catalog.russian"
3430 Parser: "pg_catalog.default"
3431 Token | Dictionaries
3432 -----------------+--------------
3433 asciihword | english_stem
3434 asciiword | english_stem
3439 hword | russian_stem
3440 hword_asciipart | english_stem
3441 hword_numpart | simple
3442 hword_part | russian_stem
3458 <term><synopsis>\dFd<optional>+</optional> <optional>PATTERN</optional></synopsis></term>
3461 List text search dictionaries (add <literal>+</> for more detail).
3464 List of text search dictionaries
3465 Schema | Name | Description
3466 ------------+-----------------+-----------------------------------------------------------
3467 pg_catalog | danish_stem | snowball stemmer for danish language
3468 pg_catalog | dutch_stem | snowball stemmer for dutch language
3469 pg_catalog | english_stem | snowball stemmer for english language
3470 pg_catalog | finnish_stem | snowball stemmer for finnish language
3471 pg_catalog | french_stem | snowball stemmer for french language
3472 pg_catalog | german_stem | snowball stemmer for german language
3473 pg_catalog | hungarian_stem | snowball stemmer for hungarian language
3474 pg_catalog | italian_stem | snowball stemmer for italian language
3475 pg_catalog | norwegian_stem | snowball stemmer for norwegian language
3476 pg_catalog | portuguese_stem | snowball stemmer for portuguese language
3477 pg_catalog | romanian_stem | snowball stemmer for romanian language
3478 pg_catalog | russian_stem | snowball stemmer for russian language
3479 pg_catalog | simple | simple dictionary: just lower case and check for stopword
3480 pg_catalog | spanish_stem | snowball stemmer for spanish language
3481 pg_catalog | swedish_stem | snowball stemmer for swedish language
3482 pg_catalog | turkish_stem | snowball stemmer for turkish language
3489 <term><synopsis>\dFp<optional>+</optional> <optional>PATTERN</optional></synopsis></term>
3492 List text search parsers (add <literal>+</> for more detail).
3495 List of text search parsers
3496 Schema | Name | Description
3497 ------------+---------+---------------------
3498 pg_catalog | default | default word parser
3500 Text search parser "pg_catalog.default"
3501 Method | Function | Description
3502 -----------------+----------------+-------------
3503 Start parse | prsd_start |
3504 Get next token | prsd_nexttoken |
3505 End parse | prsd_end |
3506 Get headline | prsd_headline |
3507 Get token types | prsd_lextype |
3509 Token types for parser "pg_catalog.default"
3510 Token name | Description
3511 -----------------+------------------------------------------
3512 asciihword | Hyphenated word, all ASCII
3513 asciiword | Word, all ASCII
3514 blank | Space symbols
3515 email | Email address
3517 file | File or path name
3518 float | Decimal notation
3520 hword | Hyphenated word, all letters
3521 hword_asciipart | Hyphenated word part, all ASCII
3522 hword_numpart | Hyphenated word part, letters and digits
3523 hword_part | Hyphenated word part, all letters
3524 int | Signed integer
3525 numhword | Hyphenated word, letters and digits
3526 numword | Word, letters and digits
3527 protocol | Protocol head
3528 sfloat | Scientific notation
3530 uint | Unsigned integer
3533 version | Version number
3534 word | Word, all letters
3542 <term><synopsis>\dFt<optional>+</optional> <optional>PATTERN</optional></synopsis></term>
3545 List text search templates (add <literal>+</> for more detail).
3548 List of text search templates
3549 Schema | Name | Description
3550 ------------+-----------+-----------------------------------------------------------
3551 pg_catalog | ispell | ispell dictionary
3552 pg_catalog | simple | simple dictionary: just lower case and check for stopword
3553 pg_catalog | snowball | snowball stemmer
3554 pg_catalog | synonym | synonym dictionary: replace word by its synonym
3555 pg_catalog | thesaurus | thesaurus dictionary: phrase by phrase substitution
3564 <sect1 id="textsearch-limitations">
3565 <title>Limitations</title>
3568 The current limitations of <productname>PostgreSQL</productname>'s
3569 text search features are:
3570 <itemizedlist spacing="compact" mark="bullet">
3572 <para>The length of each lexeme must be less than 2K bytes</para>
3575 <para>The length of a <type>tsvector</type> (lexemes + positions) must be
3576 less than 1 megabyte</para>
3579 <!-- TODO: number of lexemes in what? This is unclear -->
3580 <para>The number of lexemes must be less than
3581 2<superscript>64</superscript></para>
3584 <para>Position values in <type>tsvector</> must be greater than 0 and
3585 no more than 16,383</para>
3588 <para>No more than 256 positions per lexeme</para>
3591 <para>The number of nodes (lexemes + operators) in a <type>tsquery</type>
3592 must be less than 32,768</para>
3598 For comparison, the <productname>PostgreSQL</productname> 8.1 documentation
3599 contained 10,441 unique words, a total of 335,420 words, and the most
3600 frequent word <quote>postgresql</> was mentioned 6,127 times in 655
3604 <!-- TODO we need to put a date on these numbers? -->
3606 Another example — the <productname>PostgreSQL</productname> mailing
3607 list archives contained 910,989 unique words with 57,491,343 lexemes in
3613 <sect1 id="textsearch-migration">
3614 <title>Migration from Pre-8.3 Text Search</title>
3617 Applications that use the <xref linkend="tsearch2">
3618 module for text searching will need some adjustments to work
3626 Some functions have been renamed or had small adjustments in their
3627 argument lists, and all of them are now in the <literal>pg_catalog</>
3628 schema, whereas in a previous installation they would have been in
3629 <literal>public</> or another non-system schema. There is a new
3630 version of <application>tsearch2</>
3631 that provides a compatibility layer to solve most problems in this
3638 The old <application>tsearch2</> functions and other objects
3639 <emphasis>must</> be suppressed when loading <application>pg_dump</>
3640 output from a pre-8.3 database. While many of them won't load anyway,
3641 a few will and then cause problems. One simple way to deal with this
3642 is to load the new <application>tsearch2</> module before restoring
3643 the dump; then it will block the old objects from being loaded.
3649 Text search configuration setup is completely different now.
3650 Instead of manually inserting rows into configuration tables,
3651 search is configured through the specialized SQL commands shown
3652 earlier in this chapter. There is no automated
3653 support for converting an existing custom configuration for 8.3;
3654 you're on your own here.
3660 Most types of dictionaries rely on some outside-the-database
3661 configuration files. These are largely compatible with pre-8.3
3662 usage, but note the following differences:
3664 <itemizedlist spacing="compact" mark="bullet">
3667 Configuration files now must be placed in a single specified
3668 directory (<filename>$SHAREDIR/tsearch_data</>), and must have
3669 a specific extension depending on the type of file, as noted
3670 previously in the descriptions of the various dictionary types.
3671 This restriction was added to forestall security problems.
3677 Configuration files must be encoded in UTF-8 encoding,
3678 regardless of what database encoding is used.
3684 In thesaurus configuration files, stop words must be marked with
projects / postgresql / blob