Debian Codegraph @

doc/src/sgml/textsearch.sgml

raw
1 <!-- doc/src/sgml/textsearch.sgml -->
2
3 <chapter id="textsearch">
4 <title>Full Text Search</title>
5
6 <indexterm zone="textsearch">
7 <primary>full text search</primary>
8 </indexterm>
9
10 <indexterm zone="textsearch">
11 <primary>text search</primary>
12 </indexterm>
13
14 <sect1 id="textsearch-intro">
15 <title>Introduction</title>
16
17 <para>
18 Full Text Searching (or just <firstterm>text search</firstterm>) provides
19 the capability to identify natural-language <firstterm>documents</firstterm> 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.
29 </para>
30
31 <para>
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:
37 </para>
38
39 <itemizedlist spacing="compact" mark="bullet">
40 <listitem>
41 <para>
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).
50 </para>
51 </listitem>
52
53 <listitem>
54 <para>
55 They provide no ordering (ranking) of search results, which makes them
56 ineffective when thousands of matching documents are found.
57 </para>
58 </listitem>
59
60 <listitem>
61 <para>
62 They tend to be slow because there is no index support, so they must
63 process all documents for every search.
64 </para>
65 </listitem>
66 </itemizedlist>
67
68 <para>
69 Full text indexing allows documents to be <emphasis>preprocessed</emphasis>
70 and an index saved for later rapid searching. Preprocessing includes:
71 </para>
72
73 <itemizedlist mark="none">
74 <listitem>
75 <para>
76 <emphasis>Parsing documents into <firstterm>tokens</firstterm></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
81 set of classes.
82 <productname>PostgreSQL</productname> uses a <firstterm>parser</firstterm> to
83 perform this step. A standard parser is provided, and custom parsers
84 can be created for specific needs.
85 </para>
86 </listitem>
87
88 <listitem>
89 <para>
90 <emphasis>Converting tokens into <firstterm>lexemes</firstterm></emphasis>.
91 A lexeme is a string, just like a token, but it has been
92 <firstterm>normalized</firstterm> 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</literal> or <literal>es</literal> 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</firstterm>, 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</firstterm> to
103 perform this step. Various standard dictionaries are provided, and
104 custom ones can be created for specific needs.
105 </para>
106 </listitem>
107
108 <listitem>
109 <para>
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</quote> region of query words is
116 assigned a higher rank than one with scattered query words.
117 </para>
118 </listitem>
119 </itemizedlist>
120
121 <para>
122 Dictionaries allow fine-grained control over how tokens are normalized.
123 With appropriate dictionaries, you can:
124 </para>
125
126 <itemizedlist spacing="compact" mark="bullet">
127 <listitem>
128 <para>
129 Define stop words that should not be indexed.
130 </para>
131 </listitem>
132
133 <listitem>
134 <para>
135 Map synonyms to a single word using <application>Ispell</application>.
136 </para>
137 </listitem>
138
139 <listitem>
140 <para>
141 Map phrases to a single word using a thesaurus.
142 </para>
143 </listitem>
144
145 <listitem>
146 <para>
147 Map different variations of a word to a canonical form using
148 an <application>Ispell</application> dictionary.
149 </para>
150 </listitem>
151
152 <listitem>
153 <para>
154 Map different variations of a word to a canonical form using
155 <application>Snowball</application> stemmer rules.
156 </para>
157 </listitem>
158 </itemizedlist>
159
160 <para>
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"/>).
169 </para>
170
171
172 <sect2 id="textsearch-document">
173 <title>What Is a Document?</title>
174
175 <indexterm zone="textsearch-document">
176 <primary>document</primary>
177 <secondary>text search</secondary>
178 </indexterm>
179
180 <para>
181 A <firstterm>document</firstterm> 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.
186 </para>
187
188 <para>
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:
195
196 <programlisting>
197 SELECT title || ' ' || author || ' ' || abstract || ' ' || body AS document
198 FROM messages
199 WHERE mid = 12;
200
201 SELECT m.title || ' ' || m.author || ' ' || m.abstract || ' ' || d.body AS document
202 FROM messages m, docs d
203 WHERE m.mid = d.did AND m.mid = 12;
204 </programlisting>
205 </para>
206
207 <note>
208 <para>
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.
212 </para>
213 </note>
214
215 <para>
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.
225 </para>
226
227 <para>
228 For text search purposes, each document must be reduced to the
229 preprocessed <type>tsvector</type> format. Searching and ranking
230 are performed entirely on the <type>tsvector</type> representation
231 of a document &mdash; 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</type> as being the
234 document, but of course it is only a compact representation of
235 the full document.
236 </para>
237 </sect2>
238
239 <sect2 id="textsearch-matching">
240 <title>Basic Text Matching</title>
241
242 <para>
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:
248
249 <programlisting>
250 SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat &amp; rat'::tsquery;
251 ?column?
252 ----------
253 t
254
255 SELECT 'fat &amp; cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector;
256 ?column?
257 ----------
258 f
259 </programlisting>
260 </para>
261
262 <para>
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, NOT, and FOLLOWED BY operators.
267 (For syntax details see <xref linkend="datatype-tsquery"/>.) There are
268 functions <function>to_tsquery</function>, <function>plainto_tsquery</function>,
269 and <function>phraseto_tsquery</function>
270 that are helpful in converting user-written text into a proper
271 <type>tsquery</type>, primarily by normalizing words appearing in
272 the text. Similarly, <function>to_tsvector</function> is used to parse and
273 normalize a document string. So in practice a text search match would
274 look more like this:
275
276 <programlisting>
277 SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat &amp; rat');
278 ?column?
279 ----------
280 t
281 </programlisting>
282
283 Observe that this match would not succeed if written as
284
285 <programlisting>
286 SELECT 'fat cats ate fat rats'::tsvector @@ to_tsquery('fat &amp; rat');
287 ?column?
288 ----------
289 f
290 </programlisting>
291
292 since here no normalization of the word <literal>rats</literal> will occur.
293 The elements of a <type>tsvector</type> are lexemes, which are assumed
294 already normalized, so <literal>rats</literal> does not match <literal>rat</literal>.
295 </para>
296
297 <para>
298 The <literal>@@</literal> operator also
299 supports <type>text</type> input, allowing explicit conversion of a text
300 string to <type>tsvector</type> or <type>tsquery</type> to be skipped
301 in simple cases. The variants available are:
302
303 <programlisting>
304 tsvector @@ tsquery
305 tsquery @@ tsvector
306 text @@ tsquery
307 text @@ text
308 </programlisting>
309 </para>
310
311 <para>
312 The first two of these we saw already.
313 The form <type>text</type> <literal>@@</literal> <type>tsquery</type>
314 is equivalent to <literal>to_tsvector(x) @@ y</literal>.
315 The form <type>text</type> <literal>@@</literal> <type>text</type>
316 is equivalent to <literal>to_tsvector(x) @@ plainto_tsquery(y)</literal>.
317 </para>
318
319 <para>
320 Within a <type>tsquery</type>, the <literal>&amp;</literal> (AND) operator
321 specifies that both its arguments must appear in the document to have a
322 match. Similarly, the <literal>|</literal> (OR) operator specifies that
323 at least one of its arguments must appear, while the <literal>!</literal> (NOT)
324 operator specifies that its argument must <emphasis>not</emphasis> appear in
325 order to have a match.
326 For example, the query <literal>fat &amp; ! rat</literal> matches documents that
327 contain <literal>fat</literal> but not <literal>rat</literal>.
328 </para>
329
330 <para>
331 Searching for phrases is possible with the help of
332 the <literal>&lt;-&gt;</literal> (FOLLOWED BY) <type>tsquery</type> operator, which
333 matches only if its arguments have matches that are adjacent and in the
334 given order. For example:
335
336 <programlisting>
337 SELECT to_tsvector('fatal error') @@ to_tsquery('fatal &lt;-&gt; error');
338 ?column?
339 ----------
340 t
341
342 SELECT to_tsvector('error is not fatal') @@ to_tsquery('fatal &lt;-&gt; error');
343 ?column?
344 ----------
345 f
346 </programlisting>
347
348 There is a more general version of the FOLLOWED BY operator having the
349 form <literal>&lt;<replaceable>N</replaceable>&gt;</literal>,
350 where <replaceable>N</replaceable> is an integer standing for the difference between
351 the positions of the matching lexemes. <literal>&lt;1&gt;</literal> is
352 the same as <literal>&lt;-&gt;</literal>, while <literal>&lt;2&gt;</literal>
353 allows exactly one other lexeme to appear between the matches, and so
354 on. The <literal>phraseto_tsquery</literal> function makes use of this
355 operator to construct a <literal>tsquery</literal> that can match a multi-word
356 phrase when some of the words are stop words. For example:
357
358 <programlisting>
359 SELECT phraseto_tsquery('cats ate rats');
360 phraseto_tsquery
361 -------------------------------
362 'cat' &lt;-&gt; 'ate' &lt;-&gt; 'rat'
363
364 SELECT phraseto_tsquery('the cats ate the rats');
365 phraseto_tsquery
366 -------------------------------
367 'cat' &lt;-&gt; 'ate' &lt;2&gt; 'rat'
368 </programlisting>
369 </para>
370
371 <para>
372 A special case that's sometimes useful is that <literal>&lt;0&gt;</literal>
373 can be used to require that two patterns match the same word.
374 </para>
375
376 <para>
377 Parentheses can be used to control nesting of the <type>tsquery</type>
378 operators. Without parentheses, <literal>|</literal> binds least tightly,
379 then <literal>&amp;</literal>, then <literal>&lt;-&gt;</literal>,
380 and <literal>!</literal> most tightly.
381 </para>
382
383 <para>
384 It's worth noticing that the AND/OR/NOT operators mean something subtly
385 different when they are within the arguments of a FOLLOWED BY operator
386 than when they are not, because within FOLLOWED BY the exact position of
387 the match is significant. For example, normally <literal>!x</literal> matches
388 only documents that do not contain <literal>x</literal> anywhere.
389 But <literal>!x &lt;-&gt; y</literal> matches <literal>y</literal> if it is not
390 immediately after an <literal>x</literal>; an occurrence of <literal>x</literal>
391 elsewhere in the document does not prevent a match. Another example is
392 that <literal>x &amp; y</literal> normally only requires that <literal>x</literal>
393 and <literal>y</literal> both appear somewhere in the document, but
394 <literal>(x &amp; y) &lt;-&gt; z</literal> requires <literal>x</literal>
395 and <literal>y</literal> to match at the same place, immediately before
396 a <literal>z</literal>. Thus this query behaves differently from
397 <literal>x &lt;-&gt; z &amp; y &lt;-&gt; z</literal>, which will match a
398 document containing two separate sequences <literal>x z</literal> and
399 <literal>y z</literal>. (This specific query is useless as written,
400 since <literal>x</literal> and <literal>y</literal> could not match at the same place;
401 but with more complex situations such as prefix-match patterns, a query
402 of this form could be useful.)
403 </para>
404 </sect2>
405
406 <sect2 id="textsearch-intro-configurations">
407 <title>Configurations</title>
408
409 <para>
410 The above are all simple text search examples. As mentioned before, full
411 text search functionality includes the ability to do many more things:
412 skip indexing certain words (stop words), process synonyms, and use
413 sophisticated parsing, e.g., parse based on more than just white space.
414 This functionality is controlled by <firstterm>text search
415 configurations</firstterm>. <productname>PostgreSQL</productname> comes with predefined
416 configurations for many languages, and you can easily create your own
417 configurations. (<application>psql</application>'s <command>\dF</command> command
418 shows all available configurations.)
419 </para>
420
421 <para>
422 During installation an appropriate configuration is selected and
423 <xref linkend="guc-default-text-search-config"/> is set accordingly
424 in <filename>postgresql.conf</filename>. If you are using the same text search
425 configuration for the entire cluster you can use the value in
426 <filename>postgresql.conf</filename>. To use different configurations
427 throughout the cluster but the same configuration within any one database,
428 use <command>ALTER DATABASE ... SET</command>. Otherwise, you can set
429 <varname>default_text_search_config</varname> in each session.
430 </para>
431
432 <para>
433 Each text search function that depends on a configuration has an optional
434 <type>regconfig</type> argument, so that the configuration to use can be
435 specified explicitly. <varname>default_text_search_config</varname>
436 is used only when this argument is omitted.
437 </para>
438
439 <para>
440 To make it easier to build custom text search configurations, a
441 configuration is built up from simpler database objects.
442 <productname>PostgreSQL</productname>'s text search facility provides
443 four types of configuration-related database objects:
444 </para>
445
446 <itemizedlist spacing="compact" mark="bullet">
447 <listitem>
448 <para>
449 <firstterm>Text search parsers</firstterm> break documents into tokens
450 and classify each token (for example, as words or numbers).
451 </para>
452 </listitem>
453
454 <listitem>
455 <para>
456 <firstterm>Text search dictionaries</firstterm> convert tokens to normalized
457 form and reject stop words.
458 </para>
459 </listitem>
460
461 <listitem>
462 <para>
463 <firstterm>Text search templates</firstterm> provide the functions underlying
464 dictionaries. (A dictionary simply specifies a template and a set
465 of parameters for the template.)
466 </para>
467 </listitem>
468
469 <listitem>
470 <para>
471 <firstterm>Text search configurations</firstterm> select a parser and a set
472 of dictionaries to use to normalize the tokens produced by the parser.
473 </para>
474 </listitem>
475 </itemizedlist>
476
477 <para>
478 Text search parsers and templates are built from low-level C functions;
479 therefore it requires C programming ability to develop new ones, and
480 superuser privileges to install one into a database. (There are examples
481 of add-on parsers and templates in the <filename>contrib/</filename> area of the
482 <productname>PostgreSQL</productname> distribution.) Since dictionaries and
483 configurations just parameterize and connect together some underlying
484 parsers and templates, no special privilege is needed to create a new
485 dictionary or configuration. Examples of creating custom dictionaries and
486 configurations appear later in this chapter.
487 </para>
488
489 </sect2>
490
491 </sect1>
492
493 <sect1 id="textsearch-tables">
494 <title>Tables and Indexes</title>
495
496 <para>
497 The examples in the previous section illustrated full text matching using
498 simple constant strings. This section shows how to search table data,
499 optionally using indexes.
500 </para>
501
502 <sect2 id="textsearch-tables-search">
503 <title>Searching a Table</title>
504
505 <para>
506 It is possible to do a full text search without an index. A simple query
507 to print the <structname>title</structname> of each row that contains the word
508 <literal>friend</literal> in its <structfield>body</structfield> field is:
509
510 <programlisting>
511 SELECT title
512 FROM pgweb
513 WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');
514 </programlisting>
515
516 This will also find related words such as <literal>friends</literal>
517 and <literal>friendly</literal>, since all these are reduced to the same
518 normalized lexeme.
519 </para>
520
521 <para>
522 The query above specifies that the <literal>english</literal> configuration
523 is to be used to parse and normalize the strings. Alternatively we
524 could omit the configuration parameters:
525
526 <programlisting>
527 SELECT title
528 FROM pgweb
529 WHERE to_tsvector(body) @@ to_tsquery('friend');
530 </programlisting>
531
532 This query will use the configuration set by <xref
533 linkend="guc-default-text-search-config"/>.
534 </para>
535
536 <para>
537 A more complex example is to
538 select the ten most recent documents that contain <literal>create</literal> and
539 <literal>table</literal> in the <structname>title</structname> or <structname>body</structname>:
540
541 <programlisting>
542 SELECT title
543 FROM pgweb
544 WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('create &amp; table')
545 ORDER BY last_mod_date DESC
546 LIMIT 10;
547 </programlisting>
548
549 For clarity we omitted the <function>coalesce</function> function calls
550 which would be needed to find rows that contain <literal>NULL</literal>
551 in one of the two fields.
552 </para>
553
554 <para>
555 Although these queries will work without an index, most applications
556 will find this approach too slow, except perhaps for occasional ad-hoc
557 searches. Practical use of text searching usually requires creating
558 an index.
559 </para>
560
561 </sect2>
562
563 <sect2 id="textsearch-tables-index">
564 <title>Creating Indexes</title>
565
566 <para>
567 We can create a <acronym>GIN</acronym> index (<xref
568 linkend="textsearch-indexes"/>) to speed up text searches:
569
570 <programlisting>
571 CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', body));
572 </programlisting>
573
574 Notice that the 2-argument version of <function>to_tsvector</function> is
575 used. Only text search functions that specify a configuration name can
576 be used in expression indexes (<xref linkend="indexes-expressional"/>).
577 This is because the index contents must be unaffected by <xref
578 linkend="guc-default-text-search-config"/>. If they were affected, the
579 index contents might be inconsistent because different entries could
580 contain <type>tsvector</type>s that were created with different text search
581 configurations, and there would be no way to guess which was which. It
582 would be impossible to dump and restore such an index correctly.
583 </para>
584
585 <para>
586 Because the two-argument version of <function>to_tsvector</function> was
587 used in the index above, only a query reference that uses the 2-argument
588 version of <function>to_tsvector</function> with the same configuration
589 name will use that index. That is, <literal>WHERE
590 to_tsvector('english', body) @@ 'a &amp; b'</literal> can use the index,
591 but <literal>WHERE to_tsvector(body) @@ 'a &amp; b'</literal> cannot.
592 This ensures that an index will be used only with the same configuration
593 used to create the index entries.
594 </para>
595
596 <para>
597 It is possible to set up more complex expression indexes wherein the
598 configuration name is specified by another column, e.g.:
599
600 <programlisting>
601 CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector(config_name, body));
602 </programlisting>
603
604 where <literal>config_name</literal> is a column in the <literal>pgweb</literal>
605 table. This allows mixed configurations in the same index while
606 recording which configuration was used for each index entry. This
607 would be useful, for example, if the document collection contained
608 documents in different languages. Again,
609 queries that are meant to use the index must be phrased to match, e.g.,
610 <literal>WHERE to_tsvector(config_name, body) @@ 'a &amp; b'</literal>.
611 </para>
612
613 <para>
614 Indexes can even concatenate columns:
615
616 <programlisting>
617 CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', title || ' ' || body));
618 </programlisting>
619 </para>
620
621 <para>
622 Another approach is to create a separate <type>tsvector</type> column
623 to hold the output of <function>to_tsvector</function>. To keep this
624 column automatically up to date with its source data, use a stored
625 generated column. This example is a
626 concatenation of <literal>title</literal> and <literal>body</literal>,
627 using <function>coalesce</function> to ensure that one field will still be
628 indexed when the other is <literal>NULL</literal>:
629
630 <programlisting>
631 ALTER TABLE pgweb
632 ADD COLUMN textsearchable_index_col tsvector
633 GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;
634 </programlisting>
635
636 Then we create a <acronym>GIN</acronym> index to speed up the search:
637
638 <programlisting>
639 CREATE INDEX textsearch_idx ON pgweb USING GIN (textsearchable_index_col);
640 </programlisting>
641
642 Now we are ready to perform a fast full text search:
643
644 <programlisting>
645 SELECT title
646 FROM pgweb
647 WHERE textsearchable_index_col @@ to_tsquery('create &amp; table')
648 ORDER BY last_mod_date DESC
649 LIMIT 10;
650 </programlisting>
651 </para>
652
653 <para>
654 One advantage of the separate-column approach over an expression index
655 is that it is not necessary to explicitly specify the text search
656 configuration in queries in order to make use of the index. As shown
657 in the example above, the query can depend on
658 <varname>default_text_search_config</varname>. Another advantage is that
659 searches will be faster, since it will not be necessary to redo the
660 <function>to_tsvector</function> calls to verify index matches. (This is more
661 important when using a GiST index than a GIN index; see <xref
662 linkend="textsearch-indexes"/>.) The expression-index approach is
663 simpler to set up, however, and it requires less disk space since the
664 <type>tsvector</type> representation is not stored explicitly.
665 </para>
666
667 </sect2>
668
669 </sect1>
670
671 <sect1 id="textsearch-controls">
672 <title>Controlling Text Search</title>
673
674 <para>
675 To implement full text searching there must be a function to create a
676 <type>tsvector</type> from a document and a <type>tsquery</type> from a
677 user query. Also, we need to return results in a useful order, so we need
678 a function that compares documents with respect to their relevance to
679 the query. It's also important to be able to display the results nicely.
680 <productname>PostgreSQL</productname> provides support for all of these
681 functions.
682 </para>
683
684 <sect2 id="textsearch-parsing-documents">
685 <title>Parsing Documents</title>
686
687 <para>
688 <productname>PostgreSQL</productname> provides the
689 function <function>to_tsvector</function> for converting a document to
690 the <type>tsvector</type> data type.
691 </para>
692
693 <indexterm>
694 <primary>to_tsvector</primary>
695 </indexterm>
696
697 <synopsis>
698 to_tsvector(<optional> <replaceable class="parameter">config</replaceable> <type>regconfig</type>, </optional> <replaceable class="parameter">document</replaceable> <type>text</type>) returns <type>tsvector</type>
699 </synopsis>
700
701 <para>
702 <function>to_tsvector</function> parses a textual document into tokens,
703 reduces the tokens to lexemes, and returns a <type>tsvector</type> which
704 lists the lexemes together with their positions in the document.
705 The document is processed according to the specified or default
706 text search configuration.
707 Here is a simple example:
708
709 <screen>
710 SELECT to_tsvector('english', 'a fat cat sat on a mat - it ate a fat rats');
711 to_tsvector
712 -----------------------------------------------------
713 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4
714 </screen>
715 </para>
716
717 <para>
718 In the example above we see that the resulting <type>tsvector</type> does not
719 contain the words <literal>a</literal>, <literal>on</literal>, or
720 <literal>it</literal>, the word <literal>rats</literal> became
721 <literal>rat</literal>, and the punctuation sign <literal>-</literal> was
722 ignored.
723 </para>
724
725 <para>
726 The <function>to_tsvector</function> function internally calls a parser
727 which breaks the document text into tokens and assigns a type to
728 each token. For each token, a list of
729 dictionaries (<xref linkend="textsearch-dictionaries"/>) is consulted,
730 where the list can vary depending on the token type. The first dictionary
731 that <firstterm>recognizes</firstterm> the token emits one or more normalized
732 <firstterm>lexemes</firstterm> to represent the token. For example,
733 <literal>rats</literal> became <literal>rat</literal> because one of the
734 dictionaries recognized that the word <literal>rats</literal> is a plural
735 form of <literal>rat</literal>. Some words are recognized as
736 <firstterm>stop words</firstterm> (<xref linkend="textsearch-stopwords"/>), which
737 causes them to be ignored since they occur too frequently to be useful in
738 searching. In our example these are
739 <literal>a</literal>, <literal>on</literal>, and <literal>it</literal>.
740 If no dictionary in the list recognizes the token then it is also ignored.
741 In this example that happened to the punctuation sign <literal>-</literal>
742 because there are in fact no dictionaries assigned for its token type
743 (<literal>Space symbols</literal>), meaning space tokens will never be
744 indexed. The choices of parser, dictionaries and which types of tokens to
745 index are determined by the selected text search configuration (<xref
746 linkend="textsearch-configuration"/>). It is possible to have
747 many different configurations in the same database, and predefined
748 configurations are available for various languages. In our example
749 we used the default configuration <literal>english</literal> for the
750 English language.
751 </para>
752
753 <para>
754 The function <function>setweight</function> can be used to label the
755 entries of a <type>tsvector</type> with a given <firstterm>weight</firstterm>,
756 where a weight is one of the letters <literal>A</literal>, <literal>B</literal>,
757 <literal>C</literal>, or <literal>D</literal>.
758 This is typically used to mark entries coming from
759 different parts of a document, such as title versus body. Later, this
760 information can be used for ranking of search results.
761 </para>
762
763 <para>
764 Because <function>to_tsvector</function>(<literal>NULL</literal>) will
765 return <literal>NULL</literal>, it is recommended to use
766 <function>coalesce</function> whenever a field might be null.
767 Here is the recommended method for creating
768 a <type>tsvector</type> from a structured document:
769
770 <programlisting>
771 UPDATE tt SET ti =
772 setweight(to_tsvector(coalesce(title,'')), 'A') ||
773 setweight(to_tsvector(coalesce(keyword,'')), 'B') ||
774 setweight(to_tsvector(coalesce(abstract,'')), 'C') ||
775 setweight(to_tsvector(coalesce(body,'')), 'D');
776 </programlisting>
777
778 Here we have used <function>setweight</function> to label the source
779 of each lexeme in the finished <type>tsvector</type>, and then merged
780 the labeled <type>tsvector</type> values using the <type>tsvector</type>
781 concatenation operator <literal>||</literal>. (<xref
782 linkend="textsearch-manipulate-tsvector"/> gives details about these
783 operations.)
784 </para>
785
786 </sect2>
787
788 <sect2 id="textsearch-parsing-queries">
789 <title>Parsing Queries</title>
790
791 <para>
792 <productname>PostgreSQL</productname> provides the
793 functions <function>to_tsquery</function>,
794 <function>plainto_tsquery</function>,
795 <function>phraseto_tsquery</function> and
796 <function>websearch_to_tsquery</function>
797 for converting a query to the <type>tsquery</type> data type.
798 <function>to_tsquery</function> offers access to more features
799 than either <function>plainto_tsquery</function> or
800 <function>phraseto_tsquery</function>, but it is less forgiving about its
801 input. <function>websearch_to_tsquery</function> is a simplified version
802 of <function>to_tsquery</function> with an alternative syntax, similar
803 to the one used by web search engines.
804 </para>
805
806 <indexterm>
807 <primary>to_tsquery</primary>
808 </indexterm>
809
810 <synopsis>
811 to_tsquery(<optional> <replaceable class="parameter">config</replaceable> <type>regconfig</type>, </optional> <replaceable class="parameter">querytext</replaceable> <type>text</type>) returns <type>tsquery</type>
812 </synopsis>
813
814 <para>
815 <function>to_tsquery</function> creates a <type>tsquery</type> value from
816 <replaceable>querytext</replaceable>, which must consist of single tokens
817 separated by the <type>tsquery</type> operators <literal>&amp;</literal> (AND),
818 <literal>|</literal> (OR), <literal>!</literal> (NOT), and
819 <literal>&lt;-&gt;</literal> (FOLLOWED BY), possibly grouped
820 using parentheses. In other words, the input to
821 <function>to_tsquery</function> must already follow the general rules for
822 <type>tsquery</type> input, as described in <xref
823 linkend="datatype-tsquery"/>. The difference is that while basic
824 <type>tsquery</type> input takes the tokens at face value,
825 <function>to_tsquery</function> normalizes each token into a lexeme using
826 the specified or default configuration, and discards any tokens that are
827 stop words according to the configuration. For example:
828
829 <screen>
830 SELECT to_tsquery('english', 'The &amp; Fat &amp; Rats');
831 to_tsquery
832 ---------------
833 'fat' &amp; 'rat'
834 </screen>
835
836 As in basic <type>tsquery</type> input, weight(s) can be attached to each
837 lexeme to restrict it to match only <type>tsvector</type> lexemes of those
838 weight(s). For example:
839
840 <screen>
841 SELECT to_tsquery('english', 'Fat | Rats:AB');
842 to_tsquery
843 ------------------
844 'fat' | 'rat':AB
845 </screen>
846
847 Also, <literal>*</literal> can be attached to a lexeme to specify prefix matching:
848
849 <screen>
850 SELECT to_tsquery('supern:*A &amp; star:A*B');
851 to_tsquery
852 --------------------------
853 'supern':*A &amp; 'star':*AB
854 </screen>
855
856 Such a lexeme will match any word in a <type>tsvector</type> that begins
857 with the given string.
858 </para>
859
860 <para>
861 <function>to_tsquery</function> can also accept single-quoted
862 phrases. This is primarily useful when the configuration includes a
863 thesaurus dictionary that may trigger on such phrases.
864 In the example below, a thesaurus contains the rule <literal>supernovae
865 stars : sn</literal>:
866
867 <screen>
868 SELECT to_tsquery('''supernovae stars'' &amp; !crab');
869 to_tsquery
870 ---------------
871 'sn' &amp; !'crab'
872 </screen>
873
874 Without quotes, <function>to_tsquery</function> will generate a syntax
875 error for tokens that are not separated by an AND, OR, or FOLLOWED BY
876 operator.
877 </para>
878
879 <indexterm>
880 <primary>plainto_tsquery</primary>
881 </indexterm>
882
883 <synopsis>
884 plainto_tsquery(<optional> <replaceable class="parameter">config</replaceable> <type>regconfig</type>, </optional> <replaceable class="parameter">querytext</replaceable> <type>text</type>) returns <type>tsquery</type>
885 </synopsis>
886
887 <para>
888 <function>plainto_tsquery</function> transforms the unformatted text
889 <replaceable>querytext</replaceable> to a <type>tsquery</type> value.
890 The text is parsed and normalized much as for <function>to_tsvector</function>,
891 then the <literal>&amp;</literal> (AND) <type>tsquery</type> operator is
892 inserted between surviving words.
893 </para>
894
895 <para>
896 Example:
897
898 <screen>
899 SELECT plainto_tsquery('english', 'The Fat Rats');
900 plainto_tsquery
901 -----------------
902 'fat' &amp; 'rat'
903 </screen>
904
905 Note that <function>plainto_tsquery</function> will not
906 recognize <type>tsquery</type> operators, weight labels,
907 or prefix-match labels in its input:
908
909 <screen>
910 SELECT plainto_tsquery('english', 'The Fat &amp; Rats:C');
911 plainto_tsquery
912 ---------------------
913 'fat' &amp; 'rat' &amp; 'c'
914 </screen>
915
916 Here, all the input punctuation was discarded.
917 </para>
918
919 <indexterm>
920 <primary>phraseto_tsquery</primary>
921 </indexterm>
922
923 <synopsis>
924 phraseto_tsquery(<optional> <replaceable class="parameter">config</replaceable> <type>regconfig</type>, </optional> <replaceable class="parameter">querytext</replaceable> <type>text</type>) returns <type>tsquery</type>
925 </synopsis>
926
927 <para>
928 <function>phraseto_tsquery</function> behaves much like
929 <function>plainto_tsquery</function>, except that it inserts
930 the <literal>&lt;-&gt;</literal> (FOLLOWED BY) operator between
931 surviving words instead of the <literal>&amp;</literal> (AND) operator.
932 Also, stop words are not simply discarded, but are accounted for by
933 inserting <literal>&lt;<replaceable>N</replaceable>&gt;</literal> operators rather
934 than <literal>&lt;-&gt;</literal> operators. This function is useful
935 when searching for exact lexeme sequences, since the FOLLOWED BY
936 operators check lexeme order not just the presence of all the lexemes.
937 </para>
938
939 <para>
940 Example:
941
942 <screen>
943 SELECT phraseto_tsquery('english', 'The Fat Rats');
944 phraseto_tsquery
945 ------------------
946 'fat' &lt;-&gt; 'rat'
947 </screen>
948
949 Like <function>plainto_tsquery</function>, the
950 <function>phraseto_tsquery</function> function will not
951 recognize <type>tsquery</type> operators, weight labels,
952 or prefix-match labels in its input:
953
954 <screen>
955 SELECT phraseto_tsquery('english', 'The Fat &amp; Rats:C');
956 phraseto_tsquery
957 -----------------------------
958 'fat' &lt;-&gt; 'rat' &lt;-&gt; 'c'
959 </screen>
960 </para>
961
962 <synopsis>
963 websearch_to_tsquery(<optional> <replaceable class="parameter">config</replaceable> <type>regconfig</type>, </optional> <replaceable class="parameter">querytext</replaceable> <type>text</type>) returns <type>tsquery</type>
964 </synopsis>
965
966 <para>
967 <function>websearch_to_tsquery</function> creates a <type>tsquery</type>
968 value from <replaceable>querytext</replaceable> using an alternative
969 syntax in which simple unformatted text is a valid query.
970 Unlike <function>plainto_tsquery</function>
971 and <function>phraseto_tsquery</function>, it also recognizes certain
972 operators. Moreover, this function will never raise syntax errors,
973 which makes it possible to use raw user-supplied input for search.
974 The following syntax is supported:
975
976 <itemizedlist spacing="compact" mark="bullet">
977 <listitem>
978 <para>
979 <literal>unquoted text</literal>: text not inside quote marks will be
980 converted to terms separated by <literal>&amp;</literal> operators, as
981 if processed by <function>plainto_tsquery</function>.
982 </para>
983 </listitem>
984 <listitem>
985 <para>
986 <literal>"quoted text"</literal>: text inside quote marks will be
987 converted to terms separated by <literal>&lt;-&gt;</literal>
988 operators, as if processed by <function>phraseto_tsquery</function>.
989 </para>
990 </listitem>
991 <listitem>
992 <para>
993 <literal>OR</literal>: the word <quote>or</quote> will be converted to
994 the <literal>|</literal> operator.
995 </para>
996 </listitem>
997 <listitem>
998 <para>
999 <literal>-</literal>: a dash will be converted to
1000 the <literal>!</literal> operator.
1001 </para>
1002 </listitem>
1003 </itemizedlist>
1004
1005 Other punctuation is ignored. So
1006 like <function>plainto_tsquery</function>
1007 and <function>phraseto_tsquery</function>,
1008 the <function>websearch_to_tsquery</function> function will not
1009 recognize <type>tsquery</type> operators, weight labels, or prefix-match
1010 labels in its input.
1011 </para>
1012
1013 <para>
1014 Examples:
1015 <screen>
1016 SELECT websearch_to_tsquery('english', 'The fat rats');
1017 websearch_to_tsquery
1018 ----------------------
1019 'fat' &amp; 'rat'
1020 (1 row)
1021
1022 SELECT websearch_to_tsquery('english', '"supernovae stars" -crab');
1023 websearch_to_tsquery
1024 ----------------------------------
1025 'supernova' &lt;-&gt; 'star' &amp; !'crab'
1026 (1 row)
1027
1028 SELECT websearch_to_tsquery('english', '"sad cat" or "fat rat"');
1029 websearch_to_tsquery
1030 -----------------------------------
1031 'sad' &lt;-&gt; 'cat' | 'fat' &lt;-&gt; 'rat'
1032 (1 row)
1033
1034 SELECT websearch_to_tsquery('english', 'signal -"segmentation fault"');
1035 websearch_to_tsquery
1036 ---------------------------------------
1037 'signal' &amp; !( 'segment' &lt;-&gt; 'fault' )
1038 (1 row)
1039
1040 SELECT websearch_to_tsquery('english', '""" )( dummy \\ query &lt;-&gt;');
1041 websearch_to_tsquery
1042 ----------------------
1043 'dummi' &amp; 'queri'
1044 (1 row)
1045 </screen>
1046 </para>
1047 </sect2>
1048
1049 <sect2 id="textsearch-ranking">
1050 <title>Ranking Search Results</title>
1051
1052 <para>
1053 Ranking attempts to measure how relevant documents are to a particular
1054 query, so that when there are many matches the most relevant ones can be
1055 shown first. <productname>PostgreSQL</productname> provides two
1056 predefined ranking functions, which take into account lexical, proximity,
1057 and structural information; that is, they consider how often the query
1058 terms appear in the document, how close together the terms are in the
1059 document, and how important is the part of the document where they occur.
1060 However, the concept of relevancy is vague and very application-specific.
1061 Different applications might require additional information for ranking,
1062 e.g., document modification time. The built-in ranking functions are only
1063 examples. You can write your own ranking functions and/or combine their
1064 results with additional factors to fit your specific needs.
1065 </para>
1066
1067 <para>
1068 The two ranking functions currently available are:
1069
1070 <variablelist>
1071
1072 <varlistentry>
1073
1074 <term>
1075 <indexterm>
1076 <primary>ts_rank</primary>
1077 </indexterm>
1078
1079 <literal>ts_rank(<optional> <replaceable class="parameter">weights</replaceable> <type>float4[]</type>, </optional> <replaceable class="parameter">vector</replaceable> <type>tsvector</type>, <replaceable class="parameter">query</replaceable> <type>tsquery</type> <optional>, <replaceable class="parameter">normalization</replaceable> <type>integer</type> </optional>) returns <type>float4</type></literal>
1080 </term>
1081
1082 <listitem>
1083 <para>
1084 Ranks vectors based on the frequency of their matching lexemes.
1085 </para>
1086 </listitem>
1087 </varlistentry>
1088
1089 <varlistentry>
1090
1091 <term>
1092 <indexterm>
1093 <primary>ts_rank_cd</primary>
1094 </indexterm>
1095
1096 <literal>ts_rank_cd(<optional> <replaceable class="parameter">weights</replaceable> <type>float4[]</type>, </optional> <replaceable class="parameter">vector</replaceable> <type>tsvector</type>, <replaceable class="parameter">query</replaceable> <type>tsquery</type> <optional>, <replaceable class="parameter">normalization</replaceable> <type>integer</type> </optional>) returns <type>float4</type></literal>
1097 </term>
1098
1099 <listitem>
1100 <para>
1101 This function computes the <firstterm>cover density</firstterm>
1102 ranking for the given document vector and query, as described in
1103 Clarke, Cormack, and Tudhope's "Relevance Ranking for One to Three
1104 Term Queries" in the journal "Information Processing and Management",
1105 1999. Cover density is similar to <function>ts_rank</function> ranking
1106 except that the proximity of matching lexemes to each other is
1107 taken into consideration.
1108 </para>
1109
1110 <para>
1111 This function requires lexeme positional information to perform
1112 its calculation. Therefore, it ignores any <quote>stripped</quote>
1113 lexemes in the <type>tsvector</type>. If there are no unstripped
1114 lexemes in the input, the result will be zero. (See <xref
1115 linkend="textsearch-manipulate-tsvector"/> for more information
1116 about the <function>strip</function> function and positional information
1117 in <type>tsvector</type>s.)
1118 </para>
1119 </listitem>
1120 </varlistentry>
1121
1122 </variablelist>
1123
1124 </para>
1125
1126 <para>
1127 For both these functions,
1128 the optional <replaceable class="parameter">weights</replaceable>
1129 argument offers the ability to weigh word instances more or less
1130 heavily depending on how they are labeled. The weight arrays specify
1131 how heavily to weigh each category of word, in the order:
1132
1133 <synopsis>
1134 {D-weight, C-weight, B-weight, A-weight}
1135 </synopsis>
1136
1137 If no <replaceable class="parameter">weights</replaceable> are provided,
1138 then these defaults are used:
1139
1140 <programlisting>
1141 {0.1, 0.2, 0.4, 1.0}
1142 </programlisting>
1143
1144 Typically weights are used to mark words from special areas of the
1145 document, like the title or an initial abstract, so they can be
1146 treated with more or less importance than words in the document body.
1147 </para>
1148
1149 <para>
1150 Since a longer document has a greater chance of containing a query term
1151 it is reasonable to take into account document size, e.g., a hundred-word
1152 document with five instances of a search word is probably more relevant
1153 than a thousand-word document with five instances. Both ranking functions
1154 take an integer <replaceable>normalization</replaceable> option that
1155 specifies whether and how a document's length should impact its rank.
1156 The integer option controls several behaviors, so it is a bit mask:
1157 you can specify one or more behaviors using
1158 <literal>|</literal> (for example, <literal>2|4</literal>).
1159
1160 <itemizedlist spacing="compact" mark="bullet">
1161 <listitem>
1162 <para>
1163 0 (the default) ignores the document length
1164 </para>
1165 </listitem>
1166 <listitem>
1167 <para>
1168 1 divides the rank by 1 + the logarithm of the document length
1169 </para>
1170 </listitem>
1171 <listitem>
1172 <para>
1173 2 divides the rank by the document length
1174 </para>
1175 </listitem>
1176 <listitem>
1177 <para>
1178 4 divides the rank by the mean harmonic distance between extents
1179 (this is implemented only by <function>ts_rank_cd</function>)
1180 </para>
1181 </listitem>
1182 <listitem>
1183 <para>
1184 8 divides the rank by the number of unique words in document
1185 </para>
1186 </listitem>
1187 <listitem>
1188 <para>
1189 16 divides the rank by 1 + the logarithm of the number
1190 of unique words in document
1191 </para>
1192 </listitem>
1193 <listitem>
1194 <para>
1195 32 divides the rank by itself + 1
1196 </para>
1197 </listitem>
1198 </itemizedlist>
1199
1200 If more than one flag bit is specified, the transformations are
1201 applied in the order listed.
1202 </para>
1203
1204 <para>
1205 It is important to note that the ranking functions do not use any global
1206 information, so it is impossible to produce a fair normalization to 1% or
1207 100% as sometimes desired. Normalization option 32
1208 (<literal>rank/(rank+1)</literal>) can be applied to scale all ranks
1209 into the range zero to one, but of course this is just a cosmetic change;
1210 it will not affect the ordering of the search results.
1211 </para>
1212
1213 <para>
1214 Here is an example that selects only the ten highest-ranked matches:
1215
1216 <screen>
1217 SELECT title, ts_rank_cd(textsearch, query) AS rank
1218 FROM apod, to_tsquery('neutrino|(dark &amp; matter)') query
1219 WHERE query @@ textsearch
1220 ORDER BY rank DESC
1221 LIMIT 10;
1222 title | rank
1223 -----------------------------------------------+----------
1224 Neutrinos in the Sun | 3.1
1225 The Sudbury Neutrino Detector | 2.4
1226 A MACHO View of Galactic Dark Matter | 2.01317
1227 Hot Gas and Dark Matter | 1.91171
1228 The Virgo Cluster: Hot Plasma and Dark Matter | 1.90953
1229 Rafting for Solar Neutrinos | 1.9
1230 NGC 4650A: Strange Galaxy and Dark Matter | 1.85774
1231 Hot Gas and Dark Matter | 1.6123
1232 Ice Fishing for Cosmic Neutrinos | 1.6
1233 Weak Lensing Distorts the Universe | 0.818218
1234 </screen>
1235
1236 This is the same example using normalized ranking:
1237
1238 <screen>
1239 SELECT title, ts_rank_cd(textsearch, query, 32 /* rank/(rank+1) */ ) AS rank
1240 FROM apod, to_tsquery('neutrino|(dark &amp; matter)') query
1241 WHERE query @@ textsearch
1242 ORDER BY rank DESC
1243 LIMIT 10;
1244 title | rank
1245 -----------------------------------------------+-------------------
1246 Neutrinos in the Sun | 0.756097569485493
1247 The Sudbury Neutrino Detector | 0.705882361190954
1248 A MACHO View of Galactic Dark Matter | 0.668123210574724
1249 Hot Gas and Dark Matter | 0.65655958650282
1250 The Virgo Cluster: Hot Plasma and Dark Matter | 0.656301290640973
1251 Rafting for Solar Neutrinos | 0.655172410958162
1252 NGC 4650A: Strange Galaxy and Dark Matter | 0.650072921219637
1253 Hot Gas and Dark Matter | 0.617195790024749
1254 Ice Fishing for Cosmic Neutrinos | 0.615384618911517
1255 Weak Lensing Distorts the Universe | 0.450010798361481
1256 </screen>
1257 </para>
1258
1259 <para>
1260 Ranking can be expensive since it requires consulting the
1261 <type>tsvector</type> of each matching document, which can be I/O bound and
1262 therefore slow. Unfortunately, it is almost impossible to avoid since
1263 practical queries often result in large numbers of matches.
1264 </para>
1265
1266 </sect2>
1267
1268 <sect2 id="textsearch-headline">
1269 <title>Highlighting Results</title>
1270
1271 <para>
1272 To present search results it is ideal to show a part of each document and
1273 how it is related to the query. Usually, search engines show fragments of
1274 the document with marked search terms. <productname>PostgreSQL</productname>
1275 provides a function <function>ts_headline</function> that
1276 implements this functionality.
1277 </para>
1278
1279 <indexterm>
1280 <primary>ts_headline</primary>
1281 </indexterm>
1282
1283 <synopsis>
1284 ts_headline(<optional> <replaceable class="parameter">config</replaceable> <type>regconfig</type>, </optional> <replaceable class="parameter">document</replaceable> <type>text</type>, <replaceable class="parameter">query</replaceable> <type>tsquery</type> <optional>, <replaceable class="parameter">options</replaceable> <type>text</type> </optional>) returns <type>text</type>
1285 </synopsis>
1286
1287 <para>
1288 <function>ts_headline</function> accepts a document along
1289 with a query, and returns an excerpt from
1290 the document in which terms from the query are highlighted.
1291 Specifically, the function will use the query to select relevant
1292 text fragments, and then highlight all words that appear in the query,
1293 even if those word positions do not match the query's restrictions. The
1294 configuration to be used to parse the document can be specified by
1295 <replaceable>config</replaceable>; if <replaceable>config</replaceable>
1296 is omitted, the
1297 <varname>default_text_search_config</varname> configuration is used.
1298 </para>
1299
1300 <para>
1301 If an <replaceable>options</replaceable> string is specified it must
1302 consist of a comma-separated list of one or more
1303 <replaceable>option</replaceable><literal>=</literal><replaceable>value</replaceable> pairs.
1304 The available options are:
1305
1306 <itemizedlist spacing="compact" mark="bullet">
1307 <listitem>
1308 <para>
1309 <literal>MaxWords</literal>, <literal>MinWords</literal> (integers):
1310 these numbers determine the longest and shortest headlines to output.
1311 The default values are 35 and 15.
1312 </para>
1313 </listitem>
1314 <listitem>
1315 <para>
1316 <literal>ShortWord</literal> (integer): words of this length or less
1317 will be dropped at the start and end of a headline, unless they are
1318 query terms. The default value of three eliminates common English
1319 articles.
1320 </para>
1321 </listitem>
1322 <listitem>
1323 <para>
1324 <literal>HighlightAll</literal> (boolean): if
1325 <literal>true</literal> the whole document will be used as the
1326 headline, ignoring the preceding three parameters. The default
1327 is <literal>false</literal>.
1328 </para>
1329 </listitem>
1330 <listitem>
1331 <para>
1332 <literal>MaxFragments</literal> (integer): maximum number of text
1333 fragments to display. The default value of zero selects a
1334 non-fragment-based headline generation method. A value greater
1335 than zero selects fragment-based headline generation (see below).
1336 </para>
1337 </listitem>
1338 <listitem>
1339 <para>
1340 <literal>StartSel</literal>, <literal>StopSel</literal> (strings):
1341 the strings with which to delimit query words appearing in the
1342 document, to distinguish them from other excerpted words. The
1343 default values are <quote><literal>&lt;b&gt;</literal></quote> and
1344 <quote><literal>&lt;/b&gt;</literal></quote>, which can be suitable
1345 for HTML output (but see the warning below).
1346 </para>
1347 </listitem>
1348 <listitem>
1349 <para>
1350 <literal>FragmentDelimiter</literal> (string): When more than one
1351 fragment is displayed, the fragments will be separated by this string.
1352 The default is <quote><literal> ... </literal></quote>.
1353 </para>
1354 </listitem>
1355 </itemizedlist>
1356
1357 <warning>
1358 <title>Warning: Cross-site Scripting (XSS) Safety</title>
1359 <para>
1360 The output from <function>ts_headline</function> is not guaranteed to
1361 be safe for direct inclusion in web pages. When
1362 <literal>HighlightAll</literal> is <literal>false</literal> (the
1363 default), some simple XML tags are removed from the document, but this
1364 is not guaranteed to remove all HTML markup. Therefore, this does not
1365 provide an effective defense against attacks such as cross-site
1366 scripting (XSS) attacks, when working with untrusted input. To guard
1367 against such attacks, all HTML markup should be removed from the input
1368 document, or an HTML sanitizer should be used on the output.
1369 </para>
1370 </warning>
1371
1372 These option names are recognized case-insensitively.
1373 You must double-quote string values if they contain spaces or commas.
1374 </para>
1375
1376 <para>
1377 In non-fragment-based headline
1378 generation, <function>ts_headline</function> locates matches for the
1379 given <replaceable class="parameter">query</replaceable> and chooses a
1380 single one to display, preferring matches that have more query words
1381 within the allowed headline length.
1382 In fragment-based headline generation, <function>ts_headline</function>
1383 locates the query matches and splits each match
1384 into <quote>fragments</quote> of no more than <literal>MaxWords</literal>
1385 words each, preferring fragments with more query words, and when
1386 possible <quote>stretching</quote> fragments to include surrounding
1387 words. The fragment-based mode is thus more useful when the query
1388 matches span large sections of the document, or when it's desirable to
1389 display multiple matches.
1390 In either mode, if no query matches can be identified, then a single
1391 fragment of the first <literal>MinWords</literal> words in the document
1392 will be displayed.
1393 </para>
1394
1395 <para>
1396 For example:
1397
1398 <screen>
1399 SELECT ts_headline('english',
1400 'The most common type of search
1401 is to find all documents containing given query terms
1402 and return them in order of their similarity to the
1403 query.',
1404 to_tsquery('english', 'query &amp; similarity'));
1405 ts_headline
1406 ------------------------------------------------------------
1407 containing given &lt;b&gt;query&lt;/b&gt; terms +
1408 and return them in order of their &lt;b&gt;similarity&lt;/b&gt; to the+
1409 &lt;b&gt;query&lt;/b&gt;.
1410
1411 SELECT ts_headline('english',
1412 'Search terms may occur
1413 many times in a document,
1414 requiring ranking of the search matches to decide which
1415 occurrences to display in the result.',
1416 to_tsquery('english', 'search &amp; term'),
1417 'MaxFragments=10, MaxWords=7, MinWords=3, StartSel=&lt;&lt;, StopSel=&gt;&gt;');
1418 ts_headline
1419 ------------------------------------------------------------
1420 &lt;&lt;Search&gt;&gt; &lt;&lt;terms&gt;&gt; may occur +
1421 many times ... ranking of the &lt;&lt;search&gt;&gt; matches to decide
1422 </screen>
1423 </para>
1424
1425 <para>
1426 <function>ts_headline</function> uses the original document, not a
1427 <type>tsvector</type> summary, so it can be slow and should be used with
1428 care.
1429 </para>
1430
1431 </sect2>
1432
1433 </sect1>
1434
1435 <sect1 id="textsearch-features">
1436 <title>Additional Features</title>
1437
1438 <para>
1439 This section describes additional functions and operators that are
1440 useful in connection with text search.
1441 </para>
1442
1443 <sect2 id="textsearch-manipulate-tsvector">
1444 <title>Manipulating Documents</title>
1445
1446 <para>
1447 <xref linkend="textsearch-parsing-documents"/> showed how raw textual
1448 documents can be converted into <type>tsvector</type> values.
1449 <productname>PostgreSQL</productname> also provides functions and
1450 operators that can be used to manipulate documents that are already
1451 in <type>tsvector</type> form.
1452 </para>
1453
1454 <variablelist>
1455
1456 <varlistentry>
1457
1458 <term>
1459 <indexterm>
1460 <primary>tsvector concatenation</primary>
1461 </indexterm>
1462
1463 <literal><type>tsvector</type> || <type>tsvector</type></literal>
1464 </term>
1465
1466 <listitem>
1467 <para>
1468 The <type>tsvector</type> concatenation operator
1469 returns a vector which combines the lexemes and positional information
1470 of the two vectors given as arguments. Positions and weight labels
1471 are retained during the concatenation.
1472 Positions appearing in the right-hand vector are offset by the largest
1473 position mentioned in the left-hand vector, so that the result is
1474 nearly equivalent to the result of performing <function>to_tsvector</function>
1475 on the concatenation of the two original document strings. (The
1476 equivalence is not exact, because any stop-words removed from the
1477 end of the left-hand argument will not affect the result, whereas
1478 they would have affected the positions of the lexemes in the
1479 right-hand argument if textual concatenation were used.)
1480 </para>
1481
1482 <para>
1483 One advantage of using concatenation in the vector form, rather than
1484 concatenating text before applying <function>to_tsvector</function>, is that
1485 you can use different configurations to parse different sections
1486 of the document. Also, because the <function>setweight</function> function
1487 marks all lexemes of the given vector the same way, it is necessary
1488 to parse the text and do <function>setweight</function> before concatenating
1489 if you want to label different parts of the document with different
1490 weights.
1491 </para>
1492 </listitem>
1493 </varlistentry>
1494
1495 <varlistentry>
1496
1497 <term>
1498 <indexterm>
1499 <primary>setweight</primary>
1500 </indexterm>
1501
1502 <literal>setweight(<replaceable class="parameter">vector</replaceable> <type>tsvector</type>, <replaceable class="parameter">weight</replaceable> <type>"char"</type>) returns <type>tsvector</type></literal>
1503 </term>
1504
1505 <listitem>
1506 <para>
1507 <function>setweight</function> returns a copy of the input vector in which every
1508 position has been labeled with the given <replaceable>weight</replaceable>, either
1509 <literal>A</literal>, <literal>B</literal>, <literal>C</literal>, or
1510 <literal>D</literal>. (<literal>D</literal> is the default for new
1511 vectors and as such is not displayed on output.) These labels are
1512 retained when vectors are concatenated, allowing words from different
1513 parts of a document to be weighted differently by ranking functions.
1514 </para>
1515
1516 <para>
1517 Note that weight labels apply to <emphasis>positions</emphasis>, not
1518 <emphasis>lexemes</emphasis>. If the input vector has been stripped of
1519 positions then <function>setweight</function> does nothing.
1520 </para>
1521 </listitem>
1522 </varlistentry>
1523
1524 <varlistentry>
1525 <term>
1526 <indexterm>
1527 <primary>length(tsvector)</primary>
1528 </indexterm>
1529
1530 <literal>length(<replaceable class="parameter">vector</replaceable> <type>tsvector</type>) returns <type>integer</type></literal>
1531 </term>
1532
1533 <listitem>
1534 <para>
1535 Returns the number of lexemes stored in the vector.
1536 </para>
1537 </listitem>
1538 </varlistentry>
1539
1540 <varlistentry>
1541
1542 <term>
1543 <indexterm>
1544 <primary>strip</primary>
1545 </indexterm>
1546
1547 <literal>strip(<replaceable class="parameter">vector</replaceable> <type>tsvector</type>) returns <type>tsvector</type></literal>
1548 </term>
1549
1550 <listitem>
1551 <para>
1552 Returns a vector that lists the same lexemes as the given vector, but
1553 lacks any position or weight information. The result is usually much
1554 smaller than an unstripped vector, but it is also less useful.
1555 Relevance ranking does not work as well on stripped vectors as
1556 unstripped ones. Also,
1557 the <literal>&lt;-&gt;</literal> (FOLLOWED BY) <type>tsquery</type> operator
1558 will never match stripped input, since it cannot determine the
1559 distance between lexeme occurrences.
1560 </para>
1561 </listitem>
1562
1563 </varlistentry>
1564
1565 </variablelist>
1566
1567 <para>
1568 A full list of <type>tsvector</type>-related functions is available
1569 in <xref linkend="textsearch-functions-table"/>.
1570 </para>
1571
1572 </sect2>
1573
1574 <sect2 id="textsearch-manipulate-tsquery">
1575 <title>Manipulating Queries</title>
1576
1577 <para>
1578 <xref linkend="textsearch-parsing-queries"/> showed how raw textual
1579 queries can be converted into <type>tsquery</type> values.
1580 <productname>PostgreSQL</productname> also provides functions and
1581 operators that can be used to manipulate queries that are already
1582 in <type>tsquery</type> form.
1583 </para>
1584
1585 <variablelist>
1586
1587 <varlistentry>
1588
1589 <term>
1590 <literal><type>tsquery</type> &amp;&amp; <type>tsquery</type></literal>
1591 </term>
1592
1593 <listitem>
1594 <para>
1595 Returns the AND-combination of the two given queries.
1596 </para>
1597 </listitem>
1598
1599 </varlistentry>
1600
1601 <varlistentry>
1602
1603 <term>
1604 <literal><type>tsquery</type> || <type>tsquery</type></literal>
1605 </term>
1606
1607 <listitem>
1608 <para>
1609 Returns the OR-combination of the two given queries.
1610 </para>
1611 </listitem>
1612
1613 </varlistentry>
1614
1615 <varlistentry>
1616
1617 <term>
1618 <literal>!! <type>tsquery</type></literal>
1619 </term>
1620
1621 <listitem>
1622 <para>
1623 Returns the negation (NOT) of the given query.
1624 </para>
1625 </listitem>
1626
1627 </varlistentry>
1628
1629 <varlistentry>
1630
1631 <term>
1632 <literal><type>tsquery</type> &lt;-&gt; <type>tsquery</type></literal>
1633 </term>
1634
1635 <listitem>
1636 <para>
1637 Returns a query that searches for a match to the first given query
1638 immediately followed by a match to the second given query, using
1639 the <literal>&lt;-&gt;</literal> (FOLLOWED BY)
1640 <type>tsquery</type> operator. For example:
1641
1642 <screen>
1643 SELECT to_tsquery('fat') &lt;-&gt; to_tsquery('cat | rat');
1644 ?column?
1645 ----------------------------
1646 'fat' &lt;-&gt; ( 'cat' | 'rat' )
1647 </screen>
1648 </para>
1649 </listitem>
1650
1651 </varlistentry>
1652
1653 <varlistentry>
1654
1655 <term>
1656 <indexterm>
1657 <primary>tsquery_phrase</primary>
1658 </indexterm>
1659
1660 <literal>tsquery_phrase(<replaceable class="parameter">query1</replaceable> <type>tsquery</type>, <replaceable class="parameter">query2</replaceable> <type>tsquery</type> [, <replaceable class="parameter">distance</replaceable> <type>integer</type> ]) returns <type>tsquery</type></literal>
1661 </term>
1662
1663 <listitem>
1664 <para>
1665 Returns a query that searches for a match to the first given query
1666 followed by a match to the second given query at a distance of exactly
1667 <replaceable>distance</replaceable> lexemes, using
1668 the <literal>&lt;<replaceable>N</replaceable>&gt;</literal>
1669 <type>tsquery</type> operator. For example:
1670
1671 <screen>
1672 SELECT tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 10);
1673 tsquery_phrase
1674 ------------------
1675 'fat' &lt;10&gt; 'cat'
1676 </screen>
1677 </para>
1678 </listitem>
1679
1680 </varlistentry>
1681
1682 <varlistentry>
1683
1684 <term>
1685 <indexterm>
1686 <primary>numnode</primary>
1687 </indexterm>
1688
1689 <literal>numnode(<replaceable class="parameter">query</replaceable> <type>tsquery</type>) returns <type>integer</type></literal>
1690 </term>
1691
1692 <listitem>
1693 <para>
1694 Returns the number of nodes (lexemes plus operators) in a
1695 <type>tsquery</type>. This function is useful
1696 to determine if the <replaceable>query</replaceable> is meaningful
1697 (returns &gt; 0), or contains only stop words (returns 0).
1698 Examples:
1699
1700 <screen>
1701 SELECT numnode(plainto_tsquery('the any'));
1702 NOTICE: query contains only stopword(s) or doesn't contain lexeme(s), ignored
1703 numnode
1704 ---------
1705 0
1706
1707 SELECT numnode('foo &amp; bar'::tsquery);
1708 numnode
1709 ---------
1710 3
1711 </screen>
1712 </para>
1713 </listitem>
1714 </varlistentry>
1715
1716 <varlistentry>
1717
1718 <term>
1719 <indexterm>
1720 <primary>querytree</primary>
1721 </indexterm>
1722
1723 <literal>querytree(<replaceable class="parameter">query</replaceable> <type>tsquery</type>) returns <type>text</type></literal>
1724 </term>
1725
1726 <listitem>
1727 <para>
1728 Returns the portion of a <type>tsquery</type> that can be used for
1729 searching an index. This function is useful for detecting
1730 unindexable queries, for example those containing only stop words
1731 or only negated terms. For example:
1732
1733 <screen>
1734 SELECT querytree(to_tsquery('defined'));
1735 querytree
1736 -----------
1737 'defin'
1738
1739 SELECT querytree(to_tsquery('!defined'));
1740 querytree
1741 -----------
1742 T
1743 </screen>
1744 </para>
1745 </listitem>
1746 </varlistentry>
1747
1748 </variablelist>
1749
1750 <sect3 id="textsearch-query-rewriting">
1751 <title>Query Rewriting</title>
1752
1753 <indexterm zone="textsearch-query-rewriting">
1754 <primary>ts_rewrite</primary>
1755 </indexterm>
1756
1757 <para>
1758 The <function>ts_rewrite</function> family of functions search a
1759 given <type>tsquery</type> for occurrences of a target
1760 subquery, and replace each occurrence with a
1761 substitute subquery. In essence this operation is a
1762 <type>tsquery</type>-specific version of substring replacement.
1763 A target and substitute combination can be
1764 thought of as a <firstterm>query rewrite rule</firstterm>. A collection
1765 of such rewrite rules can be a powerful search aid.
1766 For example, you can expand the search using synonyms
1767 (e.g., <literal>new york</literal>, <literal>big apple</literal>, <literal>nyc</literal>,
1768 <literal>gotham</literal>) or narrow the search to direct the user to some hot
1769 topic. There is some overlap in functionality between this feature
1770 and thesaurus dictionaries (<xref linkend="textsearch-thesaurus"/>).
1771 However, you can modify a set of rewrite rules on-the-fly without
1772 reindexing, whereas updating a thesaurus requires reindexing to be
1773 effective.
1774 </para>
1775
1776 <variablelist>
1777
1778 <varlistentry>
1779
1780 <term>
1781 <literal>ts_rewrite (<replaceable class="parameter">query</replaceable> <type>tsquery</type>, <replaceable class="parameter">target</replaceable> <type>tsquery</type>, <replaceable class="parameter">substitute</replaceable> <type>tsquery</type>) returns <type>tsquery</type></literal>
1782 </term>
1783
1784 <listitem>
1785 <para>
1786 This form of <function>ts_rewrite</function> simply applies a single
1787 rewrite rule: <replaceable class="parameter">target</replaceable>
1788 is replaced by <replaceable class="parameter">substitute</replaceable>
1789 wherever it appears in <replaceable
1790 class="parameter">query</replaceable>. For example:
1791
1792 <screen>
1793 SELECT ts_rewrite('a &amp; b'::tsquery, 'a'::tsquery, 'c'::tsquery);
1794 ts_rewrite
1795 ------------
1796 'b' &amp; 'c'
1797 </screen>
1798 </para>
1799 </listitem>
1800 </varlistentry>
1801
1802 <varlistentry>
1803
1804 <term>
1805 <literal>ts_rewrite (<replaceable class="parameter">query</replaceable> <type>tsquery</type>, <replaceable class="parameter">select</replaceable> <type>text</type>) returns <type>tsquery</type></literal>
1806 </term>
1807
1808 <listitem>
1809 <para>
1810 This form of <function>ts_rewrite</function> accepts a starting
1811 <replaceable>query</replaceable> and an SQL <replaceable>select</replaceable> command, which
1812 is given as a text string. The <replaceable>select</replaceable> must yield two
1813 columns of <type>tsquery</type> type. For each row of the
1814 <replaceable>select</replaceable> result, occurrences of the first column value
1815 (the target) are replaced by the second column value (the substitute)
1816 within the current <replaceable>query</replaceable> value. For example:
1817
1818 <screen>
1819 CREATE TABLE aliases (t tsquery PRIMARY KEY, s tsquery);
1820 INSERT INTO aliases VALUES('a', 'c');
1821
1822 SELECT ts_rewrite('a &amp; b'::tsquery, 'SELECT t,s FROM aliases');
1823 ts_rewrite
1824 ------------
1825 'b' &amp; 'c'
1826 </screen>
1827 </para>
1828
1829 <para>
1830 Note that when multiple rewrite rules are applied in this way,
1831 the order of application can be important; so in practice you will
1832 want the source query to <literal>ORDER BY</literal> some ordering key.
1833 </para>
1834 </listitem>
1835 </varlistentry>
1836
1837 </variablelist>
1838
1839 <para>
1840 Let's consider a real-life astronomical example. We'll expand query
1841 <literal>supernovae</literal> using table-driven rewriting rules:
1842
1843 <screen>
1844 CREATE TABLE aliases (t tsquery primary key, s tsquery);
1845 INSERT INTO aliases VALUES(to_tsquery('supernovae'), to_tsquery('supernovae|sn'));
1846
1847 SELECT ts_rewrite(to_tsquery('supernovae &amp; crab'), 'SELECT * FROM aliases');
1848 ts_rewrite
1849 ---------------------------------
1850 'crab' &amp; ( 'supernova' | 'sn' )
1851 </screen>
1852
1853 We can change the rewriting rules just by updating the table:
1854
1855 <screen>
1856 UPDATE aliases
1857 SET s = to_tsquery('supernovae|sn &amp; !nebulae')
1858 WHERE t = to_tsquery('supernovae');
1859
1860 SELECT ts_rewrite(to_tsquery('supernovae &amp; crab'), 'SELECT * FROM aliases');
1861 ts_rewrite
1862 ---------------------------------------------
1863 'crab' &amp; ( 'supernova' | 'sn' &amp; !'nebula' )
1864 </screen>
1865 </para>
1866
1867 <para>
1868 Rewriting can be slow when there are many rewriting rules, since it
1869 checks every rule for a possible match. To filter out obvious non-candidate
1870 rules we can use the containment operators for the <type>tsquery</type>
1871 type. In the example below, we select only those rules which might match
1872 the original query:
1873
1874 <screen>
1875 SELECT ts_rewrite('a &amp; b'::tsquery,
1876 'SELECT t,s FROM aliases WHERE ''a &amp; b''::tsquery @&gt; t');
1877 ts_rewrite
1878 ------------
1879 'b' &amp; 'c'
1880 </screen>
1881 </para>
1882
1883 </sect3>
1884
1885 </sect2>
1886
1887 <sect2 id="textsearch-update-triggers">
1888 <title>Triggers for Automatic Updates</title>
1889
1890 <indexterm>
1891 <primary>trigger</primary>
1892 <secondary>for updating a derived tsvector column</secondary>
1893 </indexterm>
1894
1895 <note>
1896 <para>
1897 The method described in this section has been obsoleted by the use of
1898 stored generated columns, as described in <xref
1899 linkend="textsearch-tables-index"/>.
1900 </para>
1901 </note>
1902
1903 <para>
1904 When using a separate column to store the <type>tsvector</type> representation
1905 of your documents, it is necessary to create a trigger to update the
1906 <type>tsvector</type> column when the document content columns change.
1907 Two built-in trigger functions are available for this, or you can write
1908 your own.
1909 </para>
1910
1911 <synopsis>
1912 tsvector_update_trigger(<replaceable class="parameter">tsvector_column_name</replaceable>,&zwsp; <replaceable class="parameter">config_name</replaceable>, <replaceable class="parameter">text_column_name</replaceable> <optional>, ... </optional>)
1913 tsvector_update_trigger_column(<replaceable class="parameter">tsvector_column_name</replaceable>,&zwsp; <replaceable class="parameter">config_column_name</replaceable>, <replaceable class="parameter">text_column_name</replaceable> <optional>, ... </optional>)
1914 </synopsis>
1915
1916 <para>
1917 These trigger functions automatically compute a <type>tsvector</type>
1918 column from one or more textual columns, under the control of
1919 parameters specified in the <command>CREATE TRIGGER</command> command.
1920 An example of their use is:
1921
1922 <screen>
1923 CREATE TABLE messages (
1924 title text,
1925 body text,
1926 tsv tsvector
1927 );
1928
1929 CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
1930 ON messages FOR EACH ROW EXECUTE FUNCTION
1931 tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);
1932
1933 INSERT INTO messages VALUES('title here', 'the body text is here');
1934
1935 SELECT * FROM messages;
1936 title | body | tsv
1937 ------------+-----------------------+----------------------------
1938 title here | the body text is here | 'bodi':4 'text':5 'titl':1
1939
1940 SELECT title, body FROM messages WHERE tsv @@ to_tsquery('title &amp; body');
1941 title | body
1942 ------------+-----------------------
1943 title here | the body text is here
1944 </screen>
1945
1946 Having created this trigger, any change in <structfield>title</structfield> or
1947 <structfield>body</structfield> will automatically be reflected into
1948 <structfield>tsv</structfield>, without the application having to worry about it.
1949 </para>
1950
1951 <para>
1952 The first trigger argument must be the name of the <type>tsvector</type>
1953 column to be updated. The second argument specifies the text search
1954 configuration to be used to perform the conversion. For
1955 <function>tsvector_update_trigger</function>, the configuration name is simply
1956 given as the second trigger argument. It must be schema-qualified as
1957 shown above, so that the trigger behavior will not change with changes
1958 in <varname>search_path</varname>. For
1959 <function>tsvector_update_trigger_column</function>, the second trigger argument
1960 is the name of another table column, which must be of type
1961 <type>regconfig</type>. This allows a per-row selection of configuration
1962 to be made. The remaining argument(s) are the names of textual columns
1963 (of type <type>text</type>, <type>varchar</type>, or <type>char</type>). These
1964 will be included in the document in the order given. NULL values will
1965 be skipped (but the other columns will still be indexed).
1966 </para>
1967
1968 <para>
1969 A limitation of these built-in triggers is that they treat all the
1970 input columns alike. To process columns differently &mdash; for
1971 example, to weight title differently from body &mdash; it is necessary
1972 to write a custom trigger. Here is an example using
1973 <application>PL/pgSQL</application> as the trigger language:
1974
1975 <programlisting>
1976 CREATE FUNCTION messages_trigger() RETURNS trigger AS $$
1977 begin
1978 new.tsv :=
1979 setweight(to_tsvector('pg_catalog.english', coalesce(new.title,'')), 'A') ||
1980 setweight(to_tsvector('pg_catalog.english', coalesce(new.body,'')), 'D');
1981 return new;
1982 end
1983 $$ LANGUAGE plpgsql;
1984
1985 CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
1986 ON messages FOR EACH ROW EXECUTE FUNCTION messages_trigger();
1987 </programlisting>
1988 </para>
1989
1990 <para>
1991 Keep in mind that it is important to specify the configuration name
1992 explicitly when creating <type>tsvector</type> values inside triggers,
1993 so that the column's contents will not be affected by changes to
1994 <varname>default_text_search_config</varname>. Failure to do this is likely to
1995 lead to problems such as search results changing after a dump and restore.
1996 </para>
1997
1998 </sect2>
1999
2000 <sect2 id="textsearch-statistics">
2001 <title>Gathering Document Statistics</title>
2002
2003 <indexterm>
2004 <primary>ts_stat</primary>
2005 </indexterm>
2006
2007 <para>
2008 The function <function>ts_stat</function> is useful for checking your
2009 configuration and for finding stop-word candidates.
2010 </para>
2011
2012 <synopsis>
2013 ts_stat(<replaceable class="parameter">sqlquery</replaceable> <type>text</type>, <optional> <replaceable class="parameter">weights</replaceable> <type>text</type>, </optional>
2014 OUT <replaceable class="parameter">word</replaceable> <type>text</type>, OUT <replaceable class="parameter">ndoc</replaceable> <type>integer</type>,
2015 OUT <replaceable class="parameter">nentry</replaceable> <type>integer</type>) returns <type>setof record</type>
2016 </synopsis>
2017
2018 <para>
2019 <replaceable>sqlquery</replaceable> is a text value containing an SQL
2020 query which must return a single <type>tsvector</type> column.
2021 <function>ts_stat</function> executes the query and returns statistics about
2022 each distinct lexeme (word) contained in the <type>tsvector</type>
2023 data. The columns returned are
2024
2025 <itemizedlist spacing="compact" mark="bullet">
2026 <listitem>
2027 <para>
2028 <replaceable>word</replaceable> <type>text</type> &mdash; the value of a lexeme
2029 </para>
2030 </listitem>
2031 <listitem>
2032 <para>
2033 <replaceable>ndoc</replaceable> <type>integer</type> &mdash; number of documents
2034 (<type>tsvector</type>s) the word occurred in
2035 </para>
2036 </listitem>
2037 <listitem>
2038 <para>
2039 <replaceable>nentry</replaceable> <type>integer</type> &mdash; total number of
2040 occurrences of the word
2041 </para>
2042 </listitem>
2043 </itemizedlist>
2044
2045 If <replaceable>weights</replaceable> is supplied, only occurrences
2046 having one of those weights are counted.
2047 </para>
2048
2049 <para>
2050 For example, to find the ten most frequent words in a document collection:
2051
2052 <programlisting>
2053 SELECT * FROM ts_stat('SELECT vector FROM apod')
2054 ORDER BY nentry DESC, ndoc DESC, word
2055 LIMIT 10;
2056 </programlisting>
2057
2058 The same, but counting only word occurrences with weight <literal>A</literal>
2059 or <literal>B</literal>:
2060
2061 <programlisting>
2062 SELECT * FROM ts_stat('SELECT vector FROM apod', 'ab')
2063 ORDER BY nentry DESC, ndoc DESC, word
2064 LIMIT 10;
2065 </programlisting>
2066 </para>
2067
2068 </sect2>
2069
2070 </sect1>
2071
2072 <sect1 id="textsearch-parsers">
2073 <title>Parsers</title>
2074
2075 <para>
2076 Text search parsers are responsible for splitting raw document text
2077 into <firstterm>tokens</firstterm> and identifying each token's type, where
2078 the set of possible types is defined by the parser itself.
2079 Note that a parser does not modify the text at all &mdash; it simply
2080 identifies plausible word boundaries. Because of this limited scope,
2081 there is less need for application-specific custom parsers than there is
2082 for custom dictionaries. At present <productname>PostgreSQL</productname>
2083 provides just one built-in parser, which has been found to be useful for a
2084 wide range of applications.
2085 </para>
2086
2087 <para>
2088 The built-in parser is named <literal>pg_catalog.default</literal>.
2089 It recognizes 23 token types, shown in <xref linkend="textsearch-default-parser"/>.
2090 </para>
2091
2092 <table id="textsearch-default-parser">
2093 <title>Default Parser's Token Types</title>
2094 <tgroup cols="3">
2095 <colspec colname="col1" colwidth="2*"/>
2096 <colspec colname="col2" colwidth="2*"/>
2097 <colspec colname="col3" colwidth="3*"/>
2098 <thead>
2099 <row>
2100 <entry>Alias</entry>
2101 <entry>Description</entry>
2102 <entry>Example</entry>
2103 </row>
2104 </thead>
2105 <tbody>
2106 <row>
2107 <entry><literal>asciiword</literal></entry>
2108 <entry>Word, all ASCII letters</entry>
2109 <entry><literal>elephant</literal></entry>
2110 </row>
2111 <row>
2112 <entry><literal>word</literal></entry>
2113 <entry>Word, all letters</entry>
2114 <entry><literal>ma&ntilde;ana</literal></entry>
2115 </row>
2116 <row>
2117 <entry><literal>numword</literal></entry>
2118 <entry>Word, letters and digits</entry>
2119 <entry><literal>beta1</literal></entry>
2120 </row>
2121 <row>
2122 <entry><literal>asciihword</literal></entry>
2123 <entry>Hyphenated word, all ASCII</entry>
2124 <entry><literal>up-to-date</literal></entry>
2125 </row>
2126 <row>
2127 <entry><literal>hword</literal></entry>
2128 <entry>Hyphenated word, all letters</entry>
2129 <entry><literal>l&oacute;gico-matem&aacute;tica</literal></entry>
2130 </row>
2131 <row>
2132 <entry><literal>numhword</literal></entry>
2133 <entry>Hyphenated word, letters and digits</entry>
2134 <entry><literal>postgresql-beta1</literal></entry>
2135 </row>
2136 <row>
2137 <entry><literal>hword_asciipart</literal></entry>
2138 <entry>Hyphenated word part, all ASCII</entry>
2139 <entry><literal>postgresql</literal> in the context <literal>postgresql-beta1</literal></entry>
2140 </row>
2141 <row>
2142 <entry><literal>hword_part</literal></entry>
2143 <entry>Hyphenated word part, all letters</entry>
2144 <entry><literal>l&oacute;gico</literal> or <literal>matem&aacute;tica</literal>
2145 in the context <literal>l&oacute;gico-matem&aacute;tica</literal></entry>
2146 </row>
2147 <row>
2148 <entry><literal>hword_numpart</literal></entry>
2149 <entry>Hyphenated word part, letters and digits</entry>
2150 <entry><literal>beta1</literal> in the context
2151 <literal>postgresql-beta1</literal></entry>
2152 </row>
2153 <row>
2154 <entry><literal>email</literal></entry>
2155 <entry>Email address</entry>
2156 <entry><literal>[email protected]</literal></entry>
2157 </row>
2158 <row>
2159 <entry><literal>protocol</literal></entry>
2160 <entry>Protocol head</entry>
2161 <entry><literal>http://</literal></entry>
2162 </row>
2163 <row>
2164 <entry><literal>url</literal></entry>
2165 <entry>URL</entry>
2166 <entry><literal>example.com/stuff/index.html</literal></entry>
2167 </row>
2168 <row>
2169 <entry><literal>host</literal></entry>
2170 <entry>Host</entry>
2171 <entry><literal>example.com</literal></entry>
2172 </row>
2173 <row>
2174 <entry><literal>url_path</literal></entry>
2175 <entry>URL path</entry>
2176 <entry><literal>/stuff/index.html</literal>, in the context of a URL</entry>
2177 </row>
2178 <row>
2179 <entry><literal>file</literal></entry>
2180 <entry>File or path name</entry>
2181 <entry><literal>/usr/local/foo.txt</literal>, if not within a URL</entry>
2182 </row>
2183 <row>
2184 <entry><literal>sfloat</literal></entry>
2185 <entry>Scientific notation</entry>
2186 <entry><literal>-1.234e56</literal></entry>
2187 </row>
2188 <row>
2189 <entry><literal>float</literal></entry>
2190 <entry>Decimal notation</entry>
2191 <entry><literal>-1.234</literal></entry>
2192 </row>
2193 <row>
2194 <entry><literal>int</literal></entry>
2195 <entry>Signed integer</entry>
2196 <entry><literal>-1234</literal></entry>
2197 </row>
2198 <row>
2199 <entry><literal>uint</literal></entry>
2200 <entry>Unsigned integer</entry>
2201 <entry><literal>1234</literal></entry>
2202 </row>
2203 <row>
2204 <entry><literal>version</literal></entry>
2205 <entry>Version number</entry>
2206 <entry><literal>8.3.0</literal></entry>
2207 </row>
2208 <row>
2209 <entry><literal>tag</literal></entry>
2210 <entry>XML tag</entry>
2211 <entry><literal>&lt;a href="dictionaries.html"&gt;</literal></entry>
2212 </row>
2213 <row>
2214 <entry><literal>entity</literal></entry>
2215 <entry>XML entity</entry>
2216 <entry><literal>&amp;amp;</literal></entry>
2217 </row>
2218 <row>
2219 <entry><literal>blank</literal></entry>
2220 <entry>Space symbols</entry>
2221 <entry>(any whitespace or punctuation not otherwise recognized)</entry>
2222 </row>
2223 </tbody>
2224 </tgroup>
2225 </table>
2226
2227 <note>
2228 <para>
2229 The parser's notion of a <quote>letter</quote> is determined by the database's
2230 locale setting, specifically <varname>lc_ctype</varname>. Words containing
2231 only the basic ASCII letters are reported as a separate token type,
2232 since it is sometimes useful to distinguish them. In most European
2233 languages, token types <literal>word</literal> and <literal>asciiword</literal>
2234 should be treated alike.
2235 </para>
2236
2237 <para>
2238 <literal>email</literal> does not support all valid email characters as
2239 defined by <ulink url="https://datatracker.ietf.org/doc/html/rfc5322">RFC 5322</ulink>.
2240 Specifically, the only non-alphanumeric characters supported for
2241 email user names are period, dash, and underscore.
2242 </para>
2243
2244 <para>
2245 <literal>tag</literal> does not support all valid tag names as defined by
2246 <ulink url="https://www.w3.org/TR/xml/">W3C Recommendation, XML</ulink>.
2247 Specifically, the only tag names supported are those starting with an
2248 ASCII letter, underscore, or colon, and containing only letters, digits,
2249 hyphens, underscores, periods, and colons. <literal>tag</literal> also
2250 includes XML comments starting with <literal>&lt;!--</literal> and ending
2251 with <literal>--&gt;</literal>, and XML declarations (but note that this
2252 includes anything starting with <literal>&lt;?x</literal> and ending with
2253 <literal>&gt;</literal>).
2254 </para>
2255 </note>
2256
2257 <para>
2258 It is possible for the parser to produce overlapping tokens from the same
2259 piece of text. As an example, a hyphenated word will be reported both
2260 as the entire word and as each component:
2261
2262 <screen>
2263 SELECT alias, description, token FROM ts_debug('foo-bar-beta1');
2264 alias | description | token
2265 -----------------+------------------------------------------+---------------
2266 numhword | Hyphenated word, letters and digits | foo-bar-beta1
2267 hword_asciipart | Hyphenated word part, all ASCII | foo
2268 blank | Space symbols | -
2269 hword_asciipart | Hyphenated word part, all ASCII | bar
2270 blank | Space symbols | -
2271 hword_numpart | Hyphenated word part, letters and digits | beta1
2272 </screen>
2273
2274 This behavior is desirable since it allows searches to work for both
2275 the whole compound word and for components. Here is another
2276 instructive example:
2277
2278 <screen>
2279 SELECT alias, description, token FROM ts_debug('http://example.com/stuff/index.html');
2280 alias | description | token
2281 ----------+---------------+------------------------------
2282 protocol | Protocol head | http://
2283 url | URL | example.com/stuff/index.html
2284 host | Host | example.com
2285 url_path | URL path | /stuff/index.html
2286 </screen>
2287 </para>
2288
2289 </sect1>
2290
2291 <sect1 id="textsearch-dictionaries">
2292 <title>Dictionaries</title>
2293
2294 <para>
2295 Dictionaries are used to eliminate words that should not be considered in a
2296 search (<firstterm>stop words</firstterm>), and to <firstterm>normalize</firstterm> words so
2297 that different derived forms of the same word will match. A successfully
2298 normalized word is called a <firstterm>lexeme</firstterm>. Aside from
2299 improving search quality, normalization and removal of stop words reduce the
2300 size of the <type>tsvector</type> representation of a document, thereby
2301 improving performance. Normalization does not always have linguistic meaning
2302 and usually depends on application semantics.
2303 </para>
2304
2305 <para>
2306 Some examples of normalization:
2307
2308 <itemizedlist spacing="compact" mark="bullet">
2309
2310 <listitem>
2311 <para>
2312 Linguistic &mdash; Ispell dictionaries try to reduce input words to a
2313 normalized form; stemmer dictionaries remove word endings
2314 </para>
2315 </listitem>
2316 <listitem>
2317 <para>
2318 <acronym>URL</acronym> locations can be canonicalized to make
2319 equivalent URLs match:
2320
2321 <itemizedlist spacing="compact" mark="bullet">
2322 <listitem>
2323 <para>
2324 http://www.pgsql.ru/db/mw/index.html
2325 </para>
2326 </listitem>
2327 <listitem>
2328 <para>
2329 http://www.pgsql.ru/db/mw/
2330 </para>
2331 </listitem>
2332 <listitem>
2333 <para>
2334 http://www.pgsql.ru/db/../db/mw/index.html
2335 </para>
2336 </listitem>
2337 </itemizedlist>
2338 </para>
2339 </listitem>
2340 <listitem>
2341 <para>
2342 Color names can be replaced by their hexadecimal values, e.g.,
2343 <literal>red, green, blue, magenta -> FF0000, 00FF00, 0000FF, FF00FF</literal>
2344 </para>
2345 </listitem>
2346 <listitem>
2347 <para>
2348 If indexing numbers, we can
2349 remove some fractional digits to reduce the range of possible
2350 numbers, so for example <emphasis>3.14</emphasis>159265359,
2351 <emphasis>3.14</emphasis>15926, <emphasis>3.14</emphasis> will be the same
2352 after normalization if only two digits are kept after the decimal point.
2353 </para>
2354 </listitem>
2355 </itemizedlist>
2356
2357 </para>
2358
2359 <para>
2360 A dictionary is a program that accepts a token as
2361 input and returns:
2362 <itemizedlist spacing="compact" mark="bullet">
2363 <listitem>
2364 <para>
2365 an array of lexemes if the input token is known to the dictionary
2366 (notice that one token can produce more than one lexeme)
2367 </para>
2368 </listitem>
2369 <listitem>
2370 <para>
2371 a single lexeme with the <literal>TSL_FILTER</literal> flag set, to replace
2372 the original token with a new token to be passed to subsequent
2373 dictionaries (a dictionary that does this is called a
2374 <firstterm>filtering dictionary</firstterm>)
2375 </para>
2376 </listitem>
2377 <listitem>
2378 <para>
2379 an empty array if the dictionary knows the token, but it is a stop word
2380 </para>
2381 </listitem>
2382 <listitem>
2383 <para>
2384 <literal>NULL</literal> if the dictionary does not recognize the input token
2385 </para>
2386 </listitem>
2387 </itemizedlist>
2388 </para>
2389
2390 <para>
2391 <productname>PostgreSQL</productname> provides predefined dictionaries for
2392 many languages. There are also several predefined templates that can be
2393 used to create new dictionaries with custom parameters. Each predefined
2394 dictionary template is described below. If no existing
2395 template is suitable, it is possible to create new ones; see the
2396 <filename>contrib/</filename> area of the <productname>PostgreSQL</productname> distribution
2397 for examples.
2398 </para>
2399
2400 <para>
2401 A text search configuration binds a parser together with a set of
2402 dictionaries to process the parser's output tokens. For each token
2403 type that the parser can return, a separate list of dictionaries is
2404 specified by the configuration. When a token of that type is found
2405 by the parser, each dictionary in the list is consulted in turn,
2406 until some dictionary recognizes it as a known word. If it is identified
2407 as a stop word, or if no dictionary recognizes the token, it will be
2408 discarded and not indexed or searched for.
2409 Normally, the first dictionary that returns a non-<literal>NULL</literal>
2410 output determines the result, and any remaining dictionaries are not
2411 consulted; but a filtering dictionary can replace the given word
2412 with a modified word, which is then passed to subsequent dictionaries.
2413 </para>
2414
2415 <para>
2416 The general rule for configuring a list of dictionaries
2417 is to place first the most narrow, most specific dictionary, then the more
2418 general dictionaries, finishing with a very general dictionary, like
2419 a <application>Snowball</application> stemmer or <literal>simple</literal>, which
2420 recognizes everything. For example, for an astronomy-specific search
2421 (<literal>astro_en</literal> configuration) one could bind token type
2422 <type>asciiword</type> (ASCII word) to a synonym dictionary of astronomical
2423 terms, a general English dictionary and a <application>Snowball</application> English
2424 stemmer:
2425
2426 <programlisting>
2427 ALTER TEXT SEARCH CONFIGURATION astro_en
2428 ADD MAPPING FOR asciiword WITH astrosyn, english_ispell, english_stem;
2429 </programlisting>
2430 </para>
2431
2432 <para>
2433 A filtering dictionary can be placed anywhere in the list, except at the
2434 end where it'd be useless. Filtering dictionaries are useful to partially
2435 normalize words to simplify the task of later dictionaries. For example,
2436 a filtering dictionary could be used to remove accents from accented
2437 letters, as is done by the <xref linkend="unaccent"/> module.
2438 </para>
2439
2440 <sect2 id="textsearch-stopwords">
2441 <title>Stop Words</title>
2442
2443 <para>
2444 Stop words are words that are very common, appear in almost every
2445 document, and have no discrimination value. Therefore, they can be ignored
2446 in the context of full text searching. For example, every English text
2447 contains words like <literal>a</literal> and <literal>the</literal>, so it is
2448 useless to store them in an index. However, stop words do affect the
2449 positions in <type>tsvector</type>, which in turn affect ranking:
2450
2451 <screen>
2452 SELECT to_tsvector('english', 'in the list of stop words');
2453 to_tsvector
2454 ----------------------------
2455 'list':3 'stop':5 'word':6
2456 </screen>
2457
2458 The missing positions 1,2,4 are because of stop words. Ranks
2459 calculated for documents with and without stop words are quite different:
2460
2461 <screen>
2462 SELECT ts_rank_cd (to_tsvector('english', 'in the list of stop words'), to_tsquery('list &amp; stop'));
2463 ts_rank_cd
2464 ------------
2465 0.05
2466
2467 SELECT ts_rank_cd (to_tsvector('english', 'list stop words'), to_tsquery('list &amp; stop'));
2468 ts_rank_cd
2469 ------------
2470 0.1
2471 </screen>
2472
2473 </para>
2474
2475 <para>
2476 It is up to the specific dictionary how it treats stop words. For example,
2477 <literal>ispell</literal> dictionaries first normalize words and then
2478 look at the list of stop words, while <literal>Snowball</literal> stemmers
2479 first check the list of stop words. The reason for the different
2480 behavior is an attempt to decrease noise.
2481 </para>
2482
2483 </sect2>
2484
2485 <sect2 id="textsearch-simple-dictionary">
2486 <title>Simple Dictionary</title>
2487
2488 <para>
2489 The <literal>simple</literal> dictionary template operates by converting the
2490 input token to lower case and checking it against a file of stop words.
2491 If it is found in the file then an empty array is returned, causing
2492 the token to be discarded. If not, the lower-cased form of the word
2493 is returned as the normalized lexeme. Alternatively, the dictionary
2494 can be configured to report non-stop-words as unrecognized, allowing
2495 them to be passed on to the next dictionary in the list.
2496 </para>
2497
2498 <para>
2499 Here is an example of a dictionary definition using the <literal>simple</literal>
2500 template:
2501
2502 <programlisting>
2503 CREATE TEXT SEARCH DICTIONARY public.simple_dict (
2504 TEMPLATE = pg_catalog.simple,
2505 STOPWORDS = english
2506 );
2507 </programlisting>
2508
2509 Here, <literal>english</literal> is the base name of a file of stop words.
2510 The file's full name will be
2511 <filename>$SHAREDIR/tsearch_data/english.stop</filename>,
2512 where <literal>$SHAREDIR</literal> means the
2513 <productname>PostgreSQL</productname> installation's shared-data directory,
2514 often <filename>/usr/local/share/postgresql</filename> (use <command>pg_config
2515 --sharedir</command> to determine it if you're not sure).
2516 The file format is simply a list
2517 of words, one per line. Blank lines and trailing spaces are ignored,
2518 and upper case is folded to lower case, but no other processing is done
2519 on the file contents.
2520 </para>
2521
2522 <para>
2523 Now we can test our dictionary:
2524
2525 <screen>
2526 SELECT ts_lexize('public.simple_dict', 'YeS');
2527 ts_lexize
2528 -----------
2529 {yes}
2530
2531 SELECT ts_lexize('public.simple_dict', 'The');
2532 ts_lexize
2533 -----------
2534 {}
2535 </screen>
2536 </para>
2537
2538 <para>
2539 We can also choose to return <literal>NULL</literal>, instead of the lower-cased
2540 word, if it is not found in the stop words file. This behavior is
2541 selected by setting the dictionary's <literal>Accept</literal> parameter to
2542 <literal>false</literal>. Continuing the example:
2543
2544 <screen>
2545 ALTER TEXT SEARCH DICTIONARY public.simple_dict ( Accept = false );
2546
2547 SELECT ts_lexize('public.simple_dict', 'YeS');
2548 ts_lexize
2549 -----------
2550
2551
2552 SELECT ts_lexize('public.simple_dict', 'The');
2553 ts_lexize
2554 -----------
2555 {}
2556 </screen>
2557 </para>
2558
2559 <para>
2560 With the default setting of <literal>Accept</literal> = <literal>true</literal>,
2561 it is only useful to place a <literal>simple</literal> dictionary at the end
2562 of a list of dictionaries, since it will never pass on any token to
2563 a following dictionary. Conversely, <literal>Accept</literal> = <literal>false</literal>
2564 is only useful when there is at least one following dictionary.
2565 </para>
2566
2567 <caution>
2568 <para>
2569 Most types of dictionaries rely on configuration files, such as files of
2570 stop words. These files <emphasis>must</emphasis> be stored in UTF-8 encoding.
2571 They will be translated to the actual database encoding, if that is
2572 different, when they are read into the server.
2573 </para>
2574 </caution>
2575
2576 <caution>
2577 <para>
2578 Normally, a database session will read a dictionary configuration file
2579 only once, when it is first used within the session. If you modify a
2580 configuration file and want to force existing sessions to pick up the
2581 new contents, issue an <command>ALTER TEXT SEARCH DICTIONARY</command> command
2582 on the dictionary. This can be a <quote>dummy</quote> update that doesn't
2583 actually change any parameter values.
2584 </para>
2585 </caution>
2586
2587 </sect2>
2588
2589 <sect2 id="textsearch-synonym-dictionary">
2590 <title>Synonym Dictionary</title>
2591
2592 <para>
2593 This dictionary template is used to create dictionaries that replace a
2594 word with a synonym. Phrases are not supported (use the thesaurus
2595 template (<xref linkend="textsearch-thesaurus"/>) for that). A synonym
2596 dictionary can be used to overcome linguistic problems, for example, to
2597 prevent an English stemmer dictionary from reducing the word <quote>Paris</quote> to
2598 <quote>pari</quote>. It is enough to have a <literal>Paris paris</literal> line in the
2599 synonym dictionary and put it before the <literal>english_stem</literal>
2600 dictionary. For example:
2601
2602 <screen>
2603 SELECT * FROM ts_debug('english', 'Paris');
2604 alias | description | token | dictionaries | dictionary | lexemes
2605 -----------+-----------------+-------+----------------+--------------+---------
2606 asciiword | Word, all ASCII | Paris | {english_stem} | english_stem | {pari}
2607
2608 CREATE TEXT SEARCH DICTIONARY my_synonym (
2609 TEMPLATE = synonym,
2610 SYNONYMS = my_synonyms
2611 );
2612
2613 ALTER TEXT SEARCH CONFIGURATION english
2614 ALTER MAPPING FOR asciiword
2615 WITH my_synonym, english_stem;
2616
2617 SELECT * FROM ts_debug('english', 'Paris');
2618 alias | description | token | dictionaries | dictionary | lexemes
2619 -----------+-----------------+-------+---------------------------+------------+---------
2620 asciiword | Word, all ASCII | Paris | {my_synonym,english_stem} | my_synonym | {paris}
2621 </screen>
2622 </para>
2623
2624 <para>
2625 The only parameter required by the <literal>synonym</literal> template is
2626 <literal>SYNONYMS</literal>, which is the base name of its configuration file
2627 &mdash; <literal>my_synonyms</literal> in the above example.
2628 The file's full name will be
2629 <filename>$SHAREDIR/tsearch_data/my_synonyms.syn</filename>
2630 (where <literal>$SHAREDIR</literal> means the
2631 <productname>PostgreSQL</productname> installation's shared-data directory).
2632 The file format is just one line
2633 per word to be substituted, with the word followed by its synonym,
2634 separated by white space. Blank lines and trailing spaces are ignored.
2635 </para>
2636
2637 <para>
2638 The <literal>synonym</literal> template also has an optional parameter
2639 <literal>CaseSensitive</literal>, which defaults to <literal>false</literal>. When
2640 <literal>CaseSensitive</literal> is <literal>false</literal>, words in the synonym file
2641 are folded to lower case, as are input tokens. When it is
2642 <literal>true</literal>, words and tokens are not folded to lower case,
2643 but are compared as-is.
2644 </para>
2645
2646 <para>
2647 An asterisk (<literal>*</literal>) can be placed at the end of a synonym
2648 in the configuration file. This indicates that the synonym is a prefix.
2649 The asterisk is ignored when the entry is used in
2650 <function>to_tsvector()</function>, but when it is used in
2651 <function>to_tsquery()</function>, the result will be a query item with
2652 the prefix match marker (see
2653 <xref linkend="textsearch-parsing-queries"/>).
2654 For example, suppose we have these entries in
2655 <filename>$SHAREDIR/tsearch_data/synonym_sample.syn</filename>:
2656 <programlisting>
2657 postgres pgsql
2658 postgresql pgsql
2659 postgre pgsql
2660 gogle googl
2661 indices index*
2662 </programlisting>
2663 Then we will get these results:
2664 <screen>
2665 mydb=# CREATE TEXT SEARCH DICTIONARY syn (template=synonym, synonyms='synonym_sample');
2666 mydb=# SELECT ts_lexize('syn', 'indices');
2667 ts_lexize
2668 -----------
2669 {index}
2670 (1 row)
2671
2672 mydb=# CREATE TEXT SEARCH CONFIGURATION tst (copy=simple);
2673 mydb=# ALTER TEXT SEARCH CONFIGURATION tst ALTER MAPPING FOR asciiword WITH syn;
2674 mydb=# SELECT to_tsvector('tst', 'indices');
2675 to_tsvector
2676 -------------
2677 'index':1
2678 (1 row)
2679
2680 mydb=# SELECT to_tsquery('tst', 'indices');
2681 to_tsquery
2682 ------------
2683 'index':*
2684 (1 row)
2685
2686 mydb=# SELECT 'indexes are very useful'::tsvector;
2687 tsvector
2688 ---------------------------------
2689 'are' 'indexes' 'useful' 'very'
2690 (1 row)
2691
2692 mydb=# SELECT 'indexes are very useful'::tsvector @@ to_tsquery('tst', 'indices');
2693 ?column?
2694 ----------
2695 t
2696 (1 row)
2697 </screen>
2698 </para>
2699 </sect2>
2700
2701 <sect2 id="textsearch-thesaurus">
2702 <title>Thesaurus Dictionary</title>
2703
2704 <para>
2705 A thesaurus dictionary (sometimes abbreviated as <acronym>TZ</acronym>) is
2706 a collection of words that includes information about the relationships
2707 of words and phrases, i.e., broader terms (<acronym>BT</acronym>), narrower
2708 terms (<acronym>NT</acronym>), preferred terms, non-preferred terms, related
2709 terms, etc.
2710 </para>
2711
2712 <para>
2713 Basically a thesaurus dictionary replaces all non-preferred terms by one
2714 preferred term and, optionally, preserves the original terms for indexing
2715 as well. <productname>PostgreSQL</productname>'s current implementation of the
2716 thesaurus dictionary is an extension of the synonym dictionary with added
2717 <firstterm>phrase</firstterm> support. A thesaurus dictionary requires
2718 a configuration file of the following format:
2719
2720 <programlisting>
2721 # this is a comment
2722 sample word(s) : indexed word(s)
2723 more sample word(s) : more indexed word(s)
2724 ...
2725 </programlisting>
2726
2727 where the colon (<symbol>:</symbol>) symbol acts as a delimiter between a
2728 phrase and its replacement.
2729 </para>
2730
2731 <para>
2732 A thesaurus dictionary uses a <firstterm>subdictionary</firstterm> (which
2733 is specified in the dictionary's configuration) to normalize the input
2734 text before checking for phrase matches. It is only possible to select one
2735 subdictionary. An error is reported if the subdictionary fails to
2736 recognize a word. In that case, you should remove the use of the word or
2737 teach the subdictionary about it. You can place an asterisk
2738 (<symbol>*</symbol>) at the beginning of an indexed word to skip applying
2739 the subdictionary to it, but all sample words <emphasis>must</emphasis> be known
2740 to the subdictionary.
2741 </para>
2742
2743 <para>
2744 The thesaurus dictionary chooses the longest match if there are multiple
2745 phrases matching the input, and ties are broken by using the last
2746 definition.
2747 </para>
2748
2749 <para>
2750 Specific stop words recognized by the subdictionary cannot be
2751 specified; instead use <literal>?</literal> to mark the location where any
2752 stop word can appear. For example, assuming that <literal>a</literal> and
2753 <literal>the</literal> are stop words according to the subdictionary:
2754
2755 <programlisting>
2756 ? one ? two : swsw
2757 </programlisting>
2758
2759 matches <literal>a one the two</literal> and <literal>the one a two</literal>;
2760 both would be replaced by <literal>swsw</literal>.
2761 </para>
2762
2763 <para>
2764 Since a thesaurus dictionary has the capability to recognize phrases it
2765 must remember its state and interact with the parser. A thesaurus dictionary
2766 uses these assignments to check if it should handle the next word or stop
2767 accumulation. The thesaurus dictionary must be configured
2768 carefully. For example, if the thesaurus dictionary is assigned to handle
2769 only the <literal>asciiword</literal> token, then a thesaurus dictionary
2770 definition like <literal>one 7</literal> will not work since token type
2771 <literal>uint</literal> is not assigned to the thesaurus dictionary.
2772 </para>
2773
2774 <caution>
2775 <para>
2776 Thesauruses are used during indexing so any change in the thesaurus
2777 dictionary's parameters <emphasis>requires</emphasis> reindexing.
2778 For most other dictionary types, small changes such as adding or
2779 removing stopwords does not force reindexing.
2780 </para>
2781 </caution>
2782
2783 <sect3 id="textsearch-thesaurus-config">
2784 <title>Thesaurus Configuration</title>
2785
2786 <para>
2787 To define a new thesaurus dictionary, use the <literal>thesaurus</literal>
2788 template. For example:
2789
2790 <programlisting>
2791 CREATE TEXT SEARCH DICTIONARY thesaurus_simple (
2792 TEMPLATE = thesaurus,
2793 DictFile = mythesaurus,
2794 Dictionary = pg_catalog.english_stem
2795 );
2796 </programlisting>
2797
2798 Here:
2799 <itemizedlist spacing="compact" mark="bullet">
2800 <listitem>
2801 <para>
2802 <literal>thesaurus_simple</literal> is the new dictionary's name
2803 </para>
2804 </listitem>
2805 <listitem>
2806 <para>
2807 <literal>mythesaurus</literal> is the base name of the thesaurus
2808 configuration file.
2809 (Its full name will be <filename>$SHAREDIR/tsearch_data/mythesaurus.ths</filename>,
2810 where <literal>$SHAREDIR</literal> means the installation shared-data
2811 directory.)
2812 </para>
2813 </listitem>
2814 <listitem>
2815 <para>
2816 <literal>pg_catalog.english_stem</literal> is the subdictionary (here,
2817 a Snowball English stemmer) to use for thesaurus normalization.
2818 Notice that the subdictionary will have its own
2819 configuration (for example, stop words), which is not shown here.
2820 </para>
2821 </listitem>
2822 </itemizedlist>
2823
2824 Now it is possible to bind the thesaurus dictionary <literal>thesaurus_simple</literal>
2825 to the desired token types in a configuration, for example:
2826
2827 <programlisting>
2828 ALTER TEXT SEARCH CONFIGURATION russian
2829 ALTER MAPPING FOR asciiword, asciihword, hword_asciipart
2830 WITH thesaurus_simple;
2831 </programlisting>
2832 </para>
2833
2834 </sect3>
2835
2836 <sect3 id="textsearch-thesaurus-examples">
2837 <title>Thesaurus Example</title>
2838
2839 <para>
2840 Consider a simple astronomical thesaurus <literal>thesaurus_astro</literal>,
2841 which contains some astronomical word combinations:
2842
2843 <programlisting>
2844 supernovae stars : sn
2845 crab nebulae : crab
2846 </programlisting>
2847
2848 Below we create a dictionary and bind some token types to
2849 an astronomical thesaurus and English stemmer:
2850
2851 <programlisting>
2852 CREATE TEXT SEARCH DICTIONARY thesaurus_astro (
2853 TEMPLATE = thesaurus,
2854 DictFile = thesaurus_astro,
2855 Dictionary = english_stem
2856 );
2857
2858 ALTER TEXT SEARCH CONFIGURATION russian
2859 ALTER MAPPING FOR asciiword, asciihword, hword_asciipart
2860 WITH thesaurus_astro, english_stem;
2861 </programlisting>
2862
2863 Now we can see how it works.
2864 <function>ts_lexize</function> is not very useful for testing a thesaurus,
2865 because it treats its input as a single token. Instead we can use
2866 <function>plainto_tsquery</function> and <function>to_tsvector</function>
2867 which will break their input strings into multiple tokens:
2868
2869 <screen>
2870 SELECT plainto_tsquery('supernova star');
2871 plainto_tsquery
2872 -----------------
2873 'sn'
2874
2875 SELECT to_tsvector('supernova star');
2876 to_tsvector
2877 -------------
2878 'sn':1
2879 </screen>
2880
2881 In principle, one can use <function>to_tsquery</function> if you quote
2882 the argument:
2883
2884 <screen>
2885 SELECT to_tsquery('''supernova star''');
2886 to_tsquery
2887 ------------
2888 'sn'
2889 </screen>
2890
2891 Notice that <literal>supernova star</literal> matches <literal>supernovae
2892 stars</literal> in <literal>thesaurus_astro</literal> because we specified
2893 the <literal>english_stem</literal> stemmer in the thesaurus definition.
2894 The stemmer removed the <literal>e</literal> and <literal>s</literal>.
2895 </para>
2896
2897 <para>
2898 To index the original phrase as well as the substitute, just include it
2899 in the right-hand part of the definition:
2900
2901 <screen>
2902 supernovae stars : sn supernovae stars
2903
2904 SELECT plainto_tsquery('supernova star');
2905 plainto_tsquery
2906 -----------------------------
2907 'sn' &amp; 'supernova' &amp; 'star'
2908 </screen>
2909 </para>
2910
2911 </sect3>
2912
2913 </sect2>
2914
2915 <sect2 id="textsearch-ispell-dictionary">
2916 <title><application>Ispell</application> Dictionary</title>
2917
2918 <para>
2919 The <application>Ispell</application> dictionary template supports
2920 <firstterm>morphological dictionaries</firstterm>, which can normalize many
2921 different linguistic forms of a word into the same lexeme. For example,
2922 an English <application>Ispell</application> dictionary can match all declensions and
2923 conjugations of the search term <literal>bank</literal>, e.g.,
2924 <literal>banking</literal>, <literal>banked</literal>, <literal>banks</literal>,
2925 <literal>banks'</literal>, and <literal>bank's</literal>.
2926 </para>
2927
2928 <para>
2929 The standard <productname>PostgreSQL</productname> distribution does
2930 not include any <application>Ispell</application> configuration files.
2931 Dictionaries for a large number of languages are available from <ulink
2932 url="https://www.cs.hmc.edu/~geoff/ispell.html">Ispell</ulink>.
2933 Also, some more modern dictionary file formats are supported &mdash; <ulink
2934 url="https://en.wikipedia.org/wiki/MySpell">MySpell</ulink> (OO &lt; 2.0.1)
2935 and <ulink url="https://hunspell.github.io/">Hunspell</ulink>
2936 (OO &gt;= 2.0.2). A large list of dictionaries is available on the <ulink
2937 url="https://wiki.openoffice.org/wiki/Dictionaries">OpenOffice
2938 Wiki</ulink>.
2939 </para>
2940
2941 <para>
2942 To create an <application>Ispell</application> dictionary perform these steps:
2943 </para>
2944 <itemizedlist spacing="compact" mark="bullet">
2945 <listitem>
2946 <para>
2947 download dictionary configuration files. <productname>OpenOffice</productname>
2948 extension files have the <filename>.oxt</filename> extension. It is necessary
2949 to extract <filename>.aff</filename> and <filename>.dic</filename> files, change
2950 extensions to <filename>.affix</filename> and <filename>.dict</filename>. For some
2951 dictionary files it is also needed to convert characters to the UTF-8
2952 encoding with commands (for example, for a Norwegian language dictionary):
2953 <programlisting>
2954 iconv -f ISO_8859-1 -t UTF-8 -o nn_no.affix nn_NO.aff
2955 iconv -f ISO_8859-1 -t UTF-8 -o nn_no.dict nn_NO.dic
2956 </programlisting>
2957 </para>
2958 </listitem>
2959 <listitem>
2960 <para>
2961 copy files to the <filename>$SHAREDIR/tsearch_data</filename> directory
2962 </para>
2963 </listitem>
2964 <listitem>
2965 <para>
2966 load files into PostgreSQL with the following command:
2967 <programlisting>
2968 CREATE TEXT SEARCH DICTIONARY english_hunspell (
2969 TEMPLATE = ispell,
2970 DictFile = en_us,
2971 AffFile = en_us,
2972 Stopwords = english);
2973 </programlisting>
2974 </para>
2975 </listitem>
2976 </itemizedlist>
2977
2978 <para>
2979 Here, <literal>DictFile</literal>, <literal>AffFile</literal>, and <literal>StopWords</literal>
2980 specify the base names of the dictionary, affixes, and stop-words files.
2981 The stop-words file has the same format explained above for the
2982 <literal>simple</literal> dictionary type. The format of the other files is
2983 not specified here but is available from the above-mentioned web sites.
2984 </para>
2985
2986 <para>
2987 Ispell dictionaries usually recognize a limited set of words, so they
2988 should be followed by another broader dictionary; for
2989 example, a Snowball dictionary, which recognizes everything.
2990 </para>
2991
2992 <para>
2993 The <filename>.affix</filename> file of <application>Ispell</application> has the following
2994 structure:
2995 <programlisting>
2996 prefixes
2997 flag *A:
2998 . > RE # As in enter > reenter
2999 suffixes
3000 flag T:
3001 E > ST # As in late > latest
3002 [^AEIOU]Y > -Y,IEST # As in dirty > dirtiest
3003 [AEIOU]Y > EST # As in gray > grayest
3004 [^EY] > EST # As in small > smallest
3005 </programlisting>
3006 </para>
3007 <para>
3008 And the <filename>.dict</filename> file has the following structure:
3009 <programlisting>
3010 lapse/ADGRS
3011 lard/DGRS
3012 large/PRTY
3013 lark/MRS
3014 </programlisting>
3015 </para>
3016
3017 <para>
3018 Format of the <filename>.dict</filename> file is:
3019 <programlisting>
3020 basic_form/affix_class_name
3021 </programlisting>
3022 </para>
3023
3024 <para>
3025 In the <filename>.affix</filename> file every affix flag is described in the
3026 following format:
3027 <programlisting>
3028 condition > [-stripping_letters,] adding_affix
3029 </programlisting>
3030 </para>
3031
3032 <para>
3033 Here, condition has a format similar to the format of regular expressions.
3034 It can use groupings <literal>[...]</literal> and <literal>[^...]</literal>.
3035 For example, <literal>[AEIOU]Y</literal> means that the last letter of the word
3036 is <literal>"y"</literal> and the penultimate letter is <literal>"a"</literal>,
3037 <literal>"e"</literal>, <literal>"i"</literal>, <literal>"o"</literal> or <literal>"u"</literal>.
3038 <literal>[^EY]</literal> means that the last letter is neither <literal>"e"</literal>
3039 nor <literal>"y"</literal>.
3040 </para>
3041
3042 <para>
3043 Ispell dictionaries support splitting compound words;
3044 a useful feature.
3045 Notice that the affix file should specify a special flag using the
3046 <literal>compoundwords controlled</literal> statement that marks dictionary
3047 words that can participate in compound formation:
3048
3049 <programlisting>
3050 compoundwords controlled z
3051 </programlisting>
3052
3053 Here are some examples for the Norwegian language:
3054
3055 <programlisting>
3056 SELECT ts_lexize('norwegian_ispell', 'overbuljongterningpakkmesterassistent');
3057 {over,buljong,terning,pakk,mester,assistent}
3058 SELECT ts_lexize('norwegian_ispell', 'sjokoladefabrikk');
3059 {sjokoladefabrikk,sjokolade,fabrikk}
3060 </programlisting>
3061 </para>
3062
3063 <para>
3064 <application>MySpell</application> format is a subset of <application>Hunspell</application>.
3065 The <filename>.affix</filename> file of <application>Hunspell</application> has the following
3066 structure:
3067 <programlisting>
3068 PFX A Y 1
3069 PFX A 0 re .
3070 SFX T N 4
3071 SFX T 0 st e
3072 SFX T y iest [^aeiou]y
3073 SFX T 0 est [aeiou]y
3074 SFX T 0 est [^ey]
3075 </programlisting>
3076 </para>
3077
3078 <para>
3079 The first line of an affix class is the header. Fields of an affix rules are
3080 listed after the header:
3081 </para>
3082 <itemizedlist spacing="compact" mark="bullet">
3083 <listitem>
3084 <para>
3085 parameter name (PFX or SFX)
3086 </para>
3087 </listitem>
3088 <listitem>
3089 <para>
3090 flag (name of the affix class)
3091 </para>
3092 </listitem>
3093 <listitem>
3094 <para>
3095 stripping characters from beginning (at prefix) or end (at suffix) of the
3096 word
3097 </para>
3098 </listitem>
3099 <listitem>
3100 <para>
3101 adding affix
3102 </para>
3103 </listitem>
3104 <listitem>
3105 <para>
3106 condition that has a format similar to the format of regular expressions.
3107 </para>
3108 </listitem>
3109 </itemizedlist>
3110
3111 <para>
3112 The <filename>.dict</filename> file looks like the <filename>.dict</filename> file of
3113 <application>Ispell</application>:
3114 <programlisting>
3115 larder/M
3116 lardy/RT
3117 large/RSPMYT
3118 largehearted
3119 </programlisting>
3120 </para>
3121
3122 <note>
3123 <para>
3124 <application>MySpell</application> does not support compound words.
3125 <application>Hunspell</application> has sophisticated support for compound words. At
3126 present, <productname>PostgreSQL</productname> implements only the basic
3127 compound word operations of Hunspell.
3128 </para>
3129 </note>
3130
3131 </sect2>
3132
3133 <sect2 id="textsearch-snowball-dictionary">
3134 <title><application>Snowball</application> Dictionary</title>
3135
3136 <para>
3137 The <application>Snowball</application> dictionary template is based on a project
3138 by Martin Porter, inventor of the popular Porter's stemming algorithm
3139 for the English language. Snowball now provides stemming algorithms for
3140 many languages (see the <ulink url="https://snowballstem.org/">Snowball
3141 site</ulink> for more information). Each algorithm understands how to
3142 reduce common variant forms of words to a base, or stem, spelling within
3143 its language. A Snowball dictionary requires a <literal>language</literal>
3144 parameter to identify which stemmer to use, and optionally can specify a
3145 <literal>stopword</literal> file name that gives a list of words to eliminate.
3146 (<productname>PostgreSQL</productname>'s standard stopword lists are also
3147 provided by the Snowball project.)
3148 For example, there is a built-in definition equivalent to
3149
3150 <programlisting>
3151 CREATE TEXT SEARCH DICTIONARY english_stem (
3152 TEMPLATE = snowball,
3153 Language = english,
3154 StopWords = english
3155 );
3156 </programlisting>
3157
3158 The stopword file format is the same as already explained.
3159 </para>
3160
3161 <para>
3162 A <application>Snowball</application> dictionary recognizes everything, whether
3163 or not it is able to simplify the word, so it should be placed
3164 at the end of the dictionary list. It is useless to have it
3165 before any other dictionary because a token will never pass through it to
3166 the next dictionary.
3167 </para>
3168
3169 </sect2>
3170
3171 </sect1>
3172
3173 <sect1 id="textsearch-configuration">
3174 <title>Configuration Example</title>
3175
3176 <para>
3177 A text search configuration specifies all options necessary to transform a
3178 document into a <type>tsvector</type>: the parser to use to break text
3179 into tokens, and the dictionaries to use to transform each token into a
3180 lexeme. Every call of
3181 <function>to_tsvector</function> or <function>to_tsquery</function>
3182 needs a text search configuration to perform its processing.
3183 The configuration parameter
3184 <xref linkend="guc-default-text-search-config"/>
3185 specifies the name of the default configuration, which is the
3186 one used by text search functions if an explicit configuration
3187 parameter is omitted.
3188 It can be set in <filename>postgresql.conf</filename>, or set for an
3189 individual session using the <command>SET</command> command.
3190 </para>
3191
3192 <para>
3193 Several predefined text search configurations are available, and
3194 you can create custom configurations easily. To facilitate management
3195 of text search objects, a set of <acronym>SQL</acronym> commands
3196 is available, and there are several <application>psql</application> commands that display information
3197 about text search objects (<xref linkend="textsearch-psql"/>).
3198 </para>
3199
3200 <para>
3201 As an example we will create a configuration
3202 <literal>pg</literal>, starting by duplicating the built-in
3203 <literal>english</literal> configuration:
3204
3205 <programlisting>
3206 CREATE TEXT SEARCH CONFIGURATION public.pg ( COPY = pg_catalog.english );
3207 </programlisting>
3208 </para>
3209
3210 <para>
3211 We will use a PostgreSQL-specific synonym list
3212 and store it in <filename>$SHAREDIR/tsearch_data/pg_dict.syn</filename>.
3213 The file contents look like:
3214
3215 <programlisting>
3216 postgres pg
3217 pgsql pg
3218 postgresql pg
3219 </programlisting>
3220
3221 We define the synonym dictionary like this:
3222
3223 <programlisting>
3224 CREATE TEXT SEARCH DICTIONARY pg_dict (
3225 TEMPLATE = synonym,
3226 SYNONYMS = pg_dict
3227 );
3228 </programlisting>
3229
3230 Next we register the <productname>Ispell</productname> dictionary
3231 <literal>english_ispell</literal>, which has its own configuration files:
3232
3233 <programlisting>
3234 CREATE TEXT SEARCH DICTIONARY english_ispell (
3235 TEMPLATE = ispell,
3236 DictFile = english,
3237 AffFile = english,
3238 StopWords = english
3239 );
3240 </programlisting>
3241
3242 Now we can set up the mappings for words in configuration
3243 <literal>pg</literal>:
3244
3245 <programlisting>
3246 ALTER TEXT SEARCH CONFIGURATION pg
3247 ALTER MAPPING FOR asciiword, asciihword, hword_asciipart,
3248 word, hword, hword_part
3249 WITH pg_dict, english_ispell, english_stem;
3250 </programlisting>
3251
3252 We choose not to index or search some token types that the built-in
3253 configuration does handle:
3254
3255 <programlisting>
3256 ALTER TEXT SEARCH CONFIGURATION pg
3257 DROP MAPPING FOR email, url, url_path, sfloat, float;
3258 </programlisting>
3259 </para>
3260
3261 <para>
3262 Now we can test our configuration:
3263
3264 <programlisting>
3265 SELECT * FROM ts_debug('public.pg', '
3266 PostgreSQL, the highly scalable, SQL compliant, open source object-relational
3267 database management system, is now undergoing beta testing of the next
3268 version of our software.
3269 ');
3270 </programlisting>
3271 </para>
3272
3273 <para>
3274 The next step is to set the session to use the new configuration, which was
3275 created in the <literal>public</literal> schema:
3276
3277 <screen>
3278 =&gt; \dF
3279 List of text search configurations
3280 Schema | Name | Description
3281 ---------+------+-------------
3282 public | pg |
3283
3284 SET default_text_search_config = 'public.pg';
3285 SET
3286
3287 SHOW default_text_search_config;
3288 default_text_search_config
3289 ----------------------------
3290 public.pg
3291 </screen>
3292 </para>
3293
3294 </sect1>
3295
3296 <sect1 id="textsearch-debugging">
3297 <title>Testing and Debugging Text Search</title>
3298
3299 <para>
3300 The behavior of a custom text search configuration can easily become
3301 confusing. The functions described
3302 in this section are useful for testing text search objects. You can
3303 test a complete configuration, or test parsers and dictionaries separately.
3304 </para>
3305
3306 <sect2 id="textsearch-configuration-testing">
3307 <title>Configuration Testing</title>
3308
3309 <para>
3310 The function <function>ts_debug</function> allows easy testing of a
3311 text search configuration.
3312 </para>
3313
3314 <indexterm>
3315 <primary>ts_debug</primary>
3316 </indexterm>
3317
3318 <synopsis>
3319 ts_debug(<optional> <replaceable class="parameter">config</replaceable> <type>regconfig</type>, </optional> <replaceable class="parameter">document</replaceable> <type>text</type>,
3320 OUT <replaceable class="parameter">alias</replaceable> <type>text</type>,
3321 OUT <replaceable class="parameter">description</replaceable> <type>text</type>,
3322 OUT <replaceable class="parameter">token</replaceable> <type>text</type>,
3323 OUT <replaceable class="parameter">dictionaries</replaceable> <type>regdictionary[]</type>,
3324 OUT <replaceable class="parameter">dictionary</replaceable> <type>regdictionary</type>,
3325 OUT <replaceable class="parameter">lexemes</replaceable> <type>text[]</type>)
3326 returns setof record
3327 </synopsis>
3328
3329 <para>
3330 <function>ts_debug</function> displays information about every token of
3331 <replaceable class="parameter">document</replaceable> as produced by the
3332 parser and processed by the configured dictionaries. It uses the
3333 configuration specified by <replaceable
3334 class="parameter">config</replaceable>,
3335 or <varname>default_text_search_config</varname> if that argument is
3336 omitted.
3337 </para>
3338
3339 <para>
3340 <function>ts_debug</function> returns one row for each token identified in the text
3341 by the parser. The columns returned are
3342
3343 <itemizedlist spacing="compact" mark="bullet">
3344 <listitem>
3345 <para>
3346 <replaceable>alias</replaceable> <type>text</type> &mdash; short name of the token type
3347 </para>
3348 </listitem>
3349 <listitem>
3350 <para>
3351 <replaceable>description</replaceable> <type>text</type> &mdash; description of the
3352 token type
3353 </para>
3354 </listitem>
3355 <listitem>
3356 <para>
3357 <replaceable>token</replaceable> <type>text</type> &mdash; text of the token
3358 </para>
3359 </listitem>
3360 <listitem>
3361 <para>
3362 <replaceable>dictionaries</replaceable> <type>regdictionary[]</type> &mdash; the
3363 dictionaries selected by the configuration for this token type
3364 </para>
3365 </listitem>
3366 <listitem>
3367 <para>
3368 <replaceable>dictionary</replaceable> <type>regdictionary</type> &mdash; the dictionary
3369 that recognized the token, or <literal>NULL</literal> if none did
3370 </para>
3371 </listitem>
3372 <listitem>
3373 <para>
3374 <replaceable>lexemes</replaceable> <type>text[]</type> &mdash; the lexeme(s) produced
3375 by the dictionary that recognized the token, or <literal>NULL</literal> if
3376 none did; an empty array (<literal>{}</literal>) means it was recognized as a
3377 stop word
3378 </para>
3379 </listitem>
3380 </itemizedlist>
3381 </para>
3382
3383 <para>
3384 Here is a simple example:
3385
3386 <screen>
3387 SELECT * FROM ts_debug('english', 'a fat cat sat on a mat - it ate a fat rats');
3388 alias | description | token | dictionaries | dictionary | lexemes
3389 -----------+-----------------+-------+----------------+--------------+---------
3390 asciiword | Word, all ASCII | a | {english_stem} | english_stem | {}
3391 blank | Space symbols | | {} | |
3392 asciiword | Word, all ASCII | fat | {english_stem} | english_stem | {fat}
3393 blank | Space symbols | | {} | |
3394 asciiword | Word, all ASCII | cat | {english_stem} | english_stem | {cat}
3395 blank | Space symbols | | {} | |
3396 asciiword | Word, all ASCII | sat | {english_stem} | english_stem | {sat}
3397 blank | Space symbols | | {} | |
3398 asciiword | Word, all ASCII | on | {english_stem} | english_stem | {}
3399 blank | Space symbols | | {} | |
3400 asciiword | Word, all ASCII | a | {english_stem} | english_stem | {}
3401 blank | Space symbols | | {} | |
3402 asciiword | Word, all ASCII | mat | {english_stem} | english_stem | {mat}
3403 blank | Space symbols | | {} | |
3404 blank | Space symbols | - | {} | |
3405 asciiword | Word, all ASCII | it | {english_stem} | english_stem | {}
3406 blank | Space symbols | | {} | |
3407 asciiword | Word, all ASCII | ate | {english_stem} | english_stem | {ate}
3408 blank | Space symbols | | {} | |
3409 asciiword | Word, all ASCII | a | {english_stem} | english_stem | {}
3410 blank | Space symbols | | {} | |
3411 asciiword | Word, all ASCII | fat | {english_stem} | english_stem | {fat}
3412 blank | Space symbols | | {} | |
3413 asciiword | Word, all ASCII | rats | {english_stem} | english_stem | {rat}
3414 </screen>
3415 </para>
3416
3417 <para>
3418 For a more extensive demonstration, we
3419 first create a <literal>public.english</literal> configuration and
3420 Ispell dictionary for the English language:
3421 </para>
3422
3423 <programlisting>
3424 CREATE TEXT SEARCH CONFIGURATION public.english ( COPY = pg_catalog.english );
3425
3426 CREATE TEXT SEARCH DICTIONARY english_ispell (
3427 TEMPLATE = ispell,
3428 DictFile = english,
3429 AffFile = english,
3430 StopWords = english
3431 );
3432
3433 ALTER TEXT SEARCH CONFIGURATION public.english
3434 ALTER MAPPING FOR asciiword WITH english_ispell, english_stem;
3435 </programlisting>
3436
3437 <screen>
3438 SELECT * FROM ts_debug('public.english', 'The Brightest supernovaes');
3439 alias | description | token | dictionaries | dictionary | lexemes
3440 -----------+-----------------+-------------+-------------------------------+----------------+-------------
3441 asciiword | Word, all ASCII | The | {english_ispell,english_stem} | english_ispell | {}
3442 blank | Space symbols | | {} | |
3443 asciiword | Word, all ASCII | Brightest | {english_ispell,english_stem} | english_ispell | {bright}
3444 blank | Space symbols | | {} | |
3445 asciiword | Word, all ASCII | supernovaes | {english_ispell,english_stem} | english_stem | {supernova}
3446 </screen>
3447
3448 <para>
3449 In this example, the word <literal>Brightest</literal> was recognized by the
3450 parser as an <literal>ASCII word</literal> (alias <literal>asciiword</literal>).
3451 For this token type the dictionary list is
3452 <literal>english_ispell</literal> and
3453 <literal>english_stem</literal>. The word was recognized by
3454 <literal>english_ispell</literal>, which reduced it to the noun
3455 <literal>bright</literal>. The word <literal>supernovaes</literal> is
3456 unknown to the <literal>english_ispell</literal> dictionary so it
3457 was passed to the next dictionary, and, fortunately, was recognized (in
3458 fact, <literal>english_stem</literal> is a Snowball dictionary which
3459 recognizes everything; that is why it was placed at the end of the
3460 dictionary list).
3461 </para>
3462
3463 <para>
3464 The word <literal>The</literal> was recognized by the
3465 <literal>english_ispell</literal> dictionary as a stop word (<xref
3466 linkend="textsearch-stopwords"/>) and will not be indexed.
3467 The spaces are discarded too, since the configuration provides no
3468 dictionaries at all for them.
3469 </para>
3470
3471 <para>
3472 You can reduce the width of the output by explicitly specifying which columns
3473 you want to see:
3474
3475 <screen>
3476 SELECT alias, token, dictionary, lexemes
3477 FROM ts_debug('public.english', 'The Brightest supernovaes');
3478 alias | token | dictionary | lexemes
3479 -----------+-------------+----------------+-------------
3480 asciiword | The | english_ispell | {}
3481 blank | | |
3482 asciiword | Brightest | english_ispell | {bright}
3483 blank | | |
3484 asciiword | supernovaes | english_stem | {supernova}
3485 </screen>
3486 </para>
3487
3488 </sect2>
3489
3490 <sect2 id="textsearch-parser-testing">
3491 <title>Parser Testing</title>
3492
3493 <para>
3494 The following functions allow direct testing of a text search parser.
3495 </para>
3496
3497 <indexterm>
3498 <primary>ts_parse</primary>
3499 </indexterm>
3500
3501 <synopsis>
3502 ts_parse(<replaceable class="parameter">parser_name</replaceable> <type>text</type>, <replaceable class="parameter">document</replaceable> <type>text</type>,
3503 OUT <replaceable class="parameter">tokid</replaceable> <type>integer</type>, OUT <replaceable class="parameter">token</replaceable> <type>text</type>) returns <type>setof record</type>
3504 ts_parse(<replaceable class="parameter">parser_oid</replaceable> <type>oid</type>, <replaceable class="parameter">document</replaceable> <type>text</type>,
3505 OUT <replaceable class="parameter">tokid</replaceable> <type>integer</type>, OUT <replaceable class="parameter">token</replaceable> <type>text</type>) returns <type>setof record</type>
3506 </synopsis>
3507
3508 <para>
3509 <function>ts_parse</function> parses the given <replaceable>document</replaceable>
3510 and returns a series of records, one for each token produced by
3511 parsing. Each record includes a <varname>tokid</varname> showing the
3512 assigned token type and a <varname>token</varname> which is the text of the
3513 token. For example:
3514
3515 <screen>
3516 SELECT * FROM ts_parse('default', '123 - a number');
3517 tokid | token
3518 -------+--------
3519 22 | 123
3520 12 |
3521 12 | -
3522 1 | a
3523 12 |
3524 1 | number
3525 </screen>
3526 </para>
3527
3528 <indexterm>
3529 <primary>ts_token_type</primary>
3530 </indexterm>
3531
3532 <synopsis>
3533 ts_token_type(<replaceable class="parameter">parser_name</replaceable> <type>text</type>, OUT <replaceable class="parameter">tokid</replaceable> <type>integer</type>,
3534 OUT <replaceable class="parameter">alias</replaceable> <type>text</type>, OUT <replaceable class="parameter">description</replaceable> <type>text</type>) returns <type>setof record</type>
3535 ts_token_type(<replaceable class="parameter">parser_oid</replaceable> <type>oid</type>, OUT <replaceable class="parameter">tokid</replaceable> <type>integer</type>,
3536 OUT <replaceable class="parameter">alias</replaceable> <type>text</type>, OUT <replaceable class="parameter">description</replaceable> <type>text</type>) returns <type>setof record</type>
3537 </synopsis>
3538
3539 <para>
3540 <function>ts_token_type</function> returns a table which describes each type of
3541 token the specified parser can recognize. For each token type, the table
3542 gives the integer <varname>tokid</varname> that the parser uses to label a
3543 token of that type, the <varname>alias</varname> that names the token type
3544 in configuration commands, and a short <varname>description</varname>. For
3545 example:
3546
3547 <screen>
3548 SELECT * FROM ts_token_type('default');
3549 tokid | alias | description
3550 -------+-----------------+------------------------------------------
3551 1 | asciiword | Word, all ASCII
3552 2 | word | Word, all letters
3553 3 | numword | Word, letters and digits
3554 4 | email | Email address
3555 5 | url | URL
3556 6 | host | Host
3557 7 | sfloat | Scientific notation
3558 8 | version | Version number
3559 9 | hword_numpart | Hyphenated word part, letters and digits
3560 10 | hword_part | Hyphenated word part, all letters
3561 11 | hword_asciipart | Hyphenated word part, all ASCII
3562 12 | blank | Space symbols
3563 13 | tag | XML tag
3564 14 | protocol | Protocol head
3565 15 | numhword | Hyphenated word, letters and digits
3566 16 | asciihword | Hyphenated word, all ASCII
3567 17 | hword | Hyphenated word, all letters
3568 18 | url_path | URL path
3569 19 | file | File or path name
3570 20 | float | Decimal notation
3571 21 | int | Signed integer
3572 22 | uint | Unsigned integer
3573 23 | entity | XML entity
3574 </screen>
3575 </para>
3576
3577 </sect2>
3578
3579 <sect2 id="textsearch-dictionary-testing">
3580 <title>Dictionary Testing</title>
3581
3582 <para>
3583 The <function>ts_lexize</function> function facilitates dictionary testing.
3584 </para>
3585
3586 <indexterm>
3587 <primary>ts_lexize</primary>
3588 </indexterm>
3589
3590 <synopsis>
3591 ts_lexize(<replaceable class="parameter">dict</replaceable> <type>regdictionary</type>, <replaceable class="parameter">token</replaceable> <type>text</type>) returns <type>text[]</type>
3592 </synopsis>
3593
3594 <para>
3595 <function>ts_lexize</function> returns an array of lexemes if the input
3596 <replaceable>token</replaceable> is known to the dictionary,
3597 or an empty array if the token
3598 is known to the dictionary but it is a stop word, or
3599 <literal>NULL</literal> if it is an unknown word.
3600 </para>
3601
3602 <para>
3603 Examples:
3604
3605 <screen>
3606 SELECT ts_lexize('english_stem', 'stars');
3607 ts_lexize
3608 -----------
3609 {star}
3610
3611 SELECT ts_lexize('english_stem', 'a');
3612 ts_lexize
3613 -----------
3614 {}
3615 </screen>
3616 </para>
3617
3618 <note>
3619 <para>
3620 The <function>ts_lexize</function> function expects a single
3621 <emphasis>token</emphasis>, not text. Here is a case
3622 where this can be confusing:
3623
3624 <screen>
3625 SELECT ts_lexize('thesaurus_astro', 'supernovae stars') is null;
3626 ?column?
3627 ----------
3628 t
3629 </screen>
3630
3631 The thesaurus dictionary <literal>thesaurus_astro</literal> does know the
3632 phrase <literal>supernovae stars</literal>, but <function>ts_lexize</function>
3633 fails since it does not parse the input text but treats it as a single
3634 token. Use <function>plainto_tsquery</function> or <function>to_tsvector</function> to
3635 test thesaurus dictionaries, for example:
3636
3637 <screen>
3638 SELECT plainto_tsquery('supernovae stars');
3639 plainto_tsquery
3640 -----------------
3641 'sn'
3642 </screen>
3643 </para>
3644 </note>
3645
3646 </sect2>
3647
3648 </sect1>
3649
3650 <sect1 id="textsearch-indexes">
3651 <title>Preferred Index Types for Text Search</title>
3652
3653 <indexterm zone="textsearch-indexes">
3654 <primary>text search</primary>
3655 <secondary>indexes</secondary>
3656 </indexterm>
3657
3658 <para>
3659 There are two kinds of indexes that can be used to speed up full text
3660 searches:
3661 <link linkend="gin"><acronym>GIN</acronym></link> and
3662 <link linkend="gist"><acronym>GiST</acronym></link>.
3663 Note that indexes are not mandatory for full text searching, but in
3664 cases where a column is searched on a regular basis, an index is
3665 usually desirable.
3666 </para>
3667
3668 <para>
3669 To create such an index, do one of:
3670
3671 <variablelist>
3672
3673 <varlistentry>
3674
3675 <term>
3676 <indexterm zone="textsearch-indexes">
3677 <primary>index</primary>
3678 <secondary>GIN</secondary>
3679 <tertiary>text search</tertiary>
3680 </indexterm>
3681
3682 <literal>CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING GIN (<replaceable>column</replaceable>);</literal>
3683 </term>
3684
3685 <listitem>
3686 <para>
3687 Creates a GIN (Generalized Inverted Index)-based index.
3688 The <replaceable>column</replaceable> must be of <type>tsvector</type> type.
3689 </para>
3690 </listitem>
3691 </varlistentry>
3692
3693 <varlistentry>
3694
3695 <term>
3696 <indexterm zone="textsearch-indexes">
3697 <primary>index</primary>
3698 <secondary>GiST</secondary>
3699 <tertiary>text search</tertiary>
3700 </indexterm>
3701
3702 <literal>CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING GIST (<replaceable>column</replaceable> [ { DEFAULT | tsvector_ops } (siglen = <replaceable>number</replaceable>) ] );</literal>
3703 </term>
3704
3705 <listitem>
3706 <para>
3707 Creates a GiST (Generalized Search Tree)-based index.
3708 The <replaceable>column</replaceable> can be of <type>tsvector</type> or
3709 <type>tsquery</type> type.
3710 Optional integer parameter <literal>siglen</literal> determines
3711 signature length in bytes (see below for details).
3712 </para>
3713 </listitem>
3714 </varlistentry>
3715
3716 </variablelist>
3717 </para>
3718
3719 <para>
3720 GIN indexes are the preferred text search index type. As inverted
3721 indexes, they contain an index entry for each word (lexeme), with a
3722 compressed list of matching locations. Multi-word searches can find
3723 the first match, then use the index to remove rows that are lacking
3724 additional words. GIN indexes store only the words (lexemes) of
3725 <type>tsvector</type> values, and not their weight labels. Thus a table
3726 row recheck is needed when using a query that involves weights.
3727 </para>
3728
3729 <para>
3730 A GiST index is <firstterm>lossy</firstterm>, meaning that the index
3731 might produce false matches, and it is necessary
3732 to check the actual table row to eliminate such false matches.
3733 (<productname>PostgreSQL</productname> does this automatically when needed.)
3734 GiST indexes are lossy because each document is represented in the
3735 index by a fixed-length signature. The signature length in bytes is determined
3736 by the value of the optional integer parameter <literal>siglen</literal>.
3737 The default signature length (when <literal>siglen</literal> is not specified) is
3738 124 bytes, the maximum signature length is 2024 bytes. The signature is generated by hashing
3739 each word into a single bit in an n-bit string, with all these bits OR-ed
3740 together to produce an n-bit document signature. When two words hash to
3741 the same bit position there will be a false match. If all words in
3742 the query have matches (real or false) then the table row must be
3743 retrieved to see if the match is correct. Longer signatures lead to a more
3744 precise search (scanning a smaller fraction of the index and fewer heap
3745 pages), at the cost of a larger index.
3746 </para>
3747
3748 <para>
3749 A GiST index can be covering, i.e., use the <literal>INCLUDE</literal>
3750 clause. Included columns can have data types without any GiST operator
3751 class. Included attributes will be stored uncompressed.
3752 </para>
3753
3754 <para>
3755 Lossiness causes performance degradation due to unnecessary fetches of table
3756 records that turn out to be false matches. Since random access to table
3757 records is slow, this limits the usefulness of GiST indexes. The
3758 likelihood of false matches depends on several factors, in particular the
3759 number of unique words, so using dictionaries to reduce this number is
3760 recommended.
3761 </para>
3762
3763 <para>
3764 Note that <acronym>GIN</acronym> index build time can often be improved
3765 by increasing <xref linkend="guc-maintenance-work-mem"/>, while
3766 <acronym>GiST</acronym> index build time is not sensitive to that
3767 parameter.
3768 </para>
3769
3770 <para>
3771 Partitioning of big collections and the proper use of GIN and GiST indexes
3772 allows the implementation of very fast searches with online update.
3773 Partitioning can be done at the database level using table inheritance,
3774 or by distributing documents over
3775 servers and collecting external search results, e.g., via <link
3776 linkend="ddl-foreign-data">Foreign Data</link> access.
3777 The latter is possible because ranking functions use
3778 only local information.
3779 </para>
3780
3781 </sect1>
3782
3783 <sect1 id="textsearch-psql">
3784 <title><application>psql</application> Support</title>
3785
3786 <para>
3787 Information about text search configuration objects can be obtained
3788 in <application>psql</application> using a set of commands:
3789 <synopsis>
3790 \dF{d,p,t}<optional>+</optional> <optional>PATTERN</optional>
3791 </synopsis>
3792 An optional <literal>+</literal> produces more details.
3793 </para>
3794
3795 <para>
3796 The optional parameter <replaceable>PATTERN</replaceable> can be the name of
3797 a text search object, optionally schema-qualified. If
3798 <replaceable>PATTERN</replaceable> is omitted then information about all
3799 visible objects will be displayed. <replaceable>PATTERN</replaceable> can be a
3800 regular expression and can provide <emphasis>separate</emphasis> patterns
3801 for the schema and object names. The following examples illustrate this:
3802
3803 <screen>
3804 =&gt; \dF *fulltext*
3805 List of text search configurations
3806 Schema | Name | Description
3807 --------+--------------+-------------
3808 public | fulltext_cfg |
3809 </screen>
3810
3811 <screen>
3812 =&gt; \dF *.fulltext*
3813 List of text search configurations
3814 Schema | Name | Description
3815 ----------+----------------------------
3816 fulltext | fulltext_cfg |
3817 public | fulltext_cfg |
3818 </screen>
3819
3820 The available commands are:
3821 </para>
3822
3823 <variablelist>
3824 <varlistentry>
3825 <term><literal>\dF<optional>+</optional> <optional>PATTERN</optional></literal></term>
3826 <listitem>
3827 <para>
3828 List text search configurations (add <literal>+</literal> for more detail).
3829 <screen>
3830 =&gt; \dF russian
3831 List of text search configurations
3832 Schema | Name | Description
3833 ------------+---------+------------------------------------
3834 pg_catalog | russian | configuration for russian language
3835
3836 =&gt; \dF+ russian
3837 Text search configuration "pg_catalog.russian"
3838 Parser: "pg_catalog.default"
3839 Token | Dictionaries
3840 -----------------+--------------
3841 asciihword | english_stem
3842 asciiword | english_stem
3843 email | simple
3844 file | simple
3845 float | simple
3846 host | simple
3847 hword | russian_stem
3848 hword_asciipart | english_stem
3849 hword_numpart | simple
3850 hword_part | russian_stem
3851 int | simple
3852 numhword | simple
3853 numword | simple
3854 sfloat | simple
3855 uint | simple
3856 url | simple
3857 url_path | simple
3858 version | simple
3859 word | russian_stem
3860 </screen>
3861 </para>
3862 </listitem>
3863 </varlistentry>
3864
3865 <varlistentry>
3866 <term><literal>\dFd<optional>+</optional> <optional>PATTERN</optional></literal></term>
3867 <listitem>
3868 <para>
3869 List text search dictionaries (add <literal>+</literal> for more detail).
3870 <screen>
3871 =&gt; \dFd
3872 List of text search dictionaries
3873 Schema | Name | Description
3874 ------------+-----------------+-----------------------------------------------------------
3875 pg_catalog | arabic_stem | snowball stemmer for arabic language
3876 pg_catalog | armenian_stem | snowball stemmer for armenian language
3877 pg_catalog | basque_stem | snowball stemmer for basque language
3878 pg_catalog | catalan_stem | snowball stemmer for catalan language
3879 pg_catalog | danish_stem | snowball stemmer for danish language
3880 pg_catalog | dutch_stem | snowball stemmer for dutch language
3881 pg_catalog | english_stem | snowball stemmer for english language
3882 pg_catalog | estonian_stem | snowball stemmer for estonian language
3883 pg_catalog | finnish_stem | snowball stemmer for finnish language
3884 pg_catalog | french_stem | snowball stemmer for french language
3885 pg_catalog | german_stem | snowball stemmer for german language
3886 pg_catalog | greek_stem | snowball stemmer for greek language
3887 pg_catalog | hindi_stem | snowball stemmer for hindi language
3888 pg_catalog | hungarian_stem | snowball stemmer for hungarian language
3889 pg_catalog | indonesian_stem | snowball stemmer for indonesian language
3890 pg_catalog | irish_stem | snowball stemmer for irish language
3891 pg_catalog | italian_stem | snowball stemmer for italian language
3892 pg_catalog | lithuanian_stem | snowball stemmer for lithuanian language
3893 pg_catalog | nepali_stem | snowball stemmer for nepali language
3894 pg_catalog | norwegian_stem | snowball stemmer for norwegian language
3895 pg_catalog | portuguese_stem | snowball stemmer for portuguese language
3896 pg_catalog | romanian_stem | snowball stemmer for romanian language
3897 pg_catalog | russian_stem | snowball stemmer for russian language
3898 pg_catalog | serbian_stem | snowball stemmer for serbian language
3899 pg_catalog | simple | simple dictionary: just lower case and check for stopword
3900 pg_catalog | spanish_stem | snowball stemmer for spanish language
3901 pg_catalog | swedish_stem | snowball stemmer for swedish language
3902 pg_catalog | tamil_stem | snowball stemmer for tamil language
3903 pg_catalog | turkish_stem | snowball stemmer for turkish language
3904 pg_catalog | yiddish_stem | snowball stemmer for yiddish language
3905 </screen>
3906 </para>
3907 </listitem>
3908 </varlistentry>
3909
3910 <varlistentry>
3911 <term><literal>\dFp<optional>+</optional> <optional>PATTERN</optional></literal></term>
3912 <listitem>
3913 <para>
3914 List text search parsers (add <literal>+</literal> for more detail).
3915 <screen>
3916 =&gt; \dFp
3917 List of text search parsers
3918 Schema | Name | Description
3919 ------------+---------+---------------------
3920 pg_catalog | default | default word parser
3921 =&gt; \dFp+
3922 Text search parser "pg_catalog.default"
3923 Method | Function | Description
3924 -----------------+----------------+-------------
3925 Start parse | prsd_start |
3926 Get next token | prsd_nexttoken |
3927 End parse | prsd_end |
3928 Get headline | prsd_headline |
3929 Get token types | prsd_lextype |
3930
3931 Token types for parser "pg_catalog.default"
3932 Token name | Description
3933 -----------------+------------------------------------------
3934 asciihword | Hyphenated word, all ASCII
3935 asciiword | Word, all ASCII
3936 blank | Space symbols
3937 email | Email address
3938 entity | XML entity
3939 file | File or path name
3940 float | Decimal notation
3941 host | Host
3942 hword | Hyphenated word, all letters
3943 hword_asciipart | Hyphenated word part, all ASCII
3944 hword_numpart | Hyphenated word part, letters and digits
3945 hword_part | Hyphenated word part, all letters
3946 int | Signed integer
3947 numhword | Hyphenated word, letters and digits
3948 numword | Word, letters and digits
3949 protocol | Protocol head
3950 sfloat | Scientific notation
3951 tag | XML tag
3952 uint | Unsigned integer
3953 url | URL
3954 url_path | URL path
3955 version | Version number
3956 word | Word, all letters
3957 (23 rows)
3958 </screen>
3959 </para>
3960 </listitem>
3961 </varlistentry>
3962
3963 <varlistentry>
3964 <term><literal>\dFt<optional>+</optional> <optional>PATTERN</optional></literal></term>
3965 <listitem>
3966 <para>
3967 List text search templates (add <literal>+</literal> for more detail).
3968 <screen>
3969 =&gt; \dFt
3970 List of text search templates
3971 Schema | Name | Description
3972 ------------+-----------+-----------------------------------------------------------
3973 pg_catalog | ispell | ispell dictionary
3974 pg_catalog | simple | simple dictionary: just lower case and check for stopword
3975 pg_catalog | snowball | snowball stemmer
3976 pg_catalog | synonym | synonym dictionary: replace word by its synonym
3977 pg_catalog | thesaurus | thesaurus dictionary: phrase by phrase substitution
3978 </screen>
3979 </para>
3980 </listitem>
3981 </varlistentry>
3982 </variablelist>
3983
3984 </sect1>
3985
3986 <sect1 id="textsearch-limitations">
3987 <title>Limitations</title>
3988
3989 <para>
3990 The current limitations of <productname>PostgreSQL</productname>'s
3991 text search features are:
3992 <itemizedlist spacing="compact" mark="bullet">
3993 <listitem>
3994 <para>The length of each lexeme must be less than 2 kilobytes</para>
3995 </listitem>
3996 <listitem>
3997 <para>The length of a <type>tsvector</type> (lexemes + positions) must be
3998 less than 1 megabyte</para>
3999 </listitem>
4000 <listitem>
4001 <!-- TODO: number of lexemes in what? This is unclear -->
4002 <para>The number of lexemes must be less than
4003 2<superscript>64</superscript></para>
4004 </listitem>
4005 <listitem>
4006 <para>Position values in <type>tsvector</type> must be greater than 0 and
4007 no more than 16,383</para>
4008 </listitem>
4009 <listitem>
4010 <para>The match distance in a <literal>&lt;<replaceable>N</replaceable>&gt;</literal>
4011 (FOLLOWED BY) <type>tsquery</type> operator cannot be more than
4012 16,384</para>
4013 </listitem>
4014 <listitem>
4015 <para>No more than 256 positions per lexeme</para>
4016 </listitem>
4017 <listitem>
4018 <para>The number of nodes (lexemes + operators) in a <type>tsquery</type>
4019 must be less than 32,768</para>
4020 </listitem>
4021 </itemizedlist>
4022 </para>
4023
4024 <para>
4025 For comparison, the <productname>PostgreSQL</productname> 8.1 documentation
4026 contained 10,441 unique words, a total of 335,420 words, and the most
4027 frequent word <quote>postgresql</quote> was mentioned 6,127 times in 655
4028 documents.
4029 </para>
4030
4031 <!-- TODO we need to put a date on these numbers? -->
4032 <para>
4033 Another example &mdash; the <productname>PostgreSQL</productname> mailing
4034 list archives contained 910,989 unique words with 57,491,343 lexemes in
4035 461,020 messages.
4036 </para>
4037
4038 </sect1>
4039
4040 </chapter>