| 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 — 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 & rat'::tsquery; |
| 251 |
?column? |
| 252 |
---------- |
| 253 |
t |
| 254 |
|
| 255 |
SELECT 'fat & 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 & 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 & 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>&</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 & ! 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><-></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 <-> error'); |
| 338 |
?column? |
| 339 |
---------- |
| 340 |
t |
| 341 |
|
| 342 |
SELECT to_tsvector('error is not fatal') @@ to_tsquery('fatal <-> 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><<replaceable>N</replaceable>></literal>, |
| 350 |
where <replaceable>N</replaceable> is an integer standing for the difference between |
| 351 |
the positions of the matching lexemes. <literal><1></literal> is |
| 352 |
the same as <literal><-></literal>, while <literal><2></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' <-> 'ate' <-> 'rat' |
| 363 |
|
| 364 |
SELECT phraseto_tsquery('the cats ate the rats'); |
| 365 |
phraseto_tsquery |
| 366 |
------------------------------- |
| 367 |
'cat' <-> 'ate' <2> 'rat' |
| 368 |
</programlisting> |
| 369 |
</para> |
| 370 |
|
| 371 |
<para> |
| 372 |
A special case that's sometimes useful is that <literal><0></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>&</literal>, then <literal><-></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 <-> 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 & y</literal> normally only requires that <literal>x</literal> |
| 393 |
and <literal>y</literal> both appear somewhere in the document, but |
| 394 |
<literal>(x & y) <-> 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 <-> z & y <-> 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 & 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 & b'</literal> can use the index, |
| 591 |
but <literal>WHERE to_tsvector(body) @@ 'a & 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 & 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 & 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>&</literal> (AND), |
| 818 |
<literal>|</literal> (OR), <literal>!</literal> (NOT), and |
| 819 |
<literal><-></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 & Fat & Rats'); |
| 831 |
to_tsquery |
| 832 |
--------------- |
| 833 |
'fat' & '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 & star:A*B'); |
| 851 |
to_tsquery |
| 852 |
-------------------------- |
| 853 |
'supern':*A & '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'' & !crab'); |
| 869 |
to_tsquery |
| 870 |
--------------- |
| 871 |
'sn' & !'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>&</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' & '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 & Rats:C'); |
| 911 |
plainto_tsquery |
| 912 |
--------------------- |
| 913 |
'fat' & 'rat' & '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><-></literal> (FOLLOWED BY) operator between |
| 931 |
surviving words instead of the <literal>&</literal> (AND) operator. |
| 932 |
Also, stop words are not simply discarded, but are accounted for by |
| 933 |
inserting <literal><<replaceable>N</replaceable>></literal> operators rather |
| 934 |
than <literal><-></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' <-> '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 & Rats:C'); |
| 956 |
phraseto_tsquery |
| 957 |
----------------------------- |
| 958 |
'fat' <-> 'rat' <-> '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>&</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><-></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' & 'rat' |
| 1020 |
(1 row) |
| 1021 |
|
| 1022 |
SELECT websearch_to_tsquery('english', '"supernovae stars" -crab'); |
| 1023 |
websearch_to_tsquery |
| 1024 |
---------------------------------- |
| 1025 |
'supernova' <-> 'star' & !'crab' |
| 1026 |
(1 row) |
| 1027 |
|
| 1028 |
SELECT websearch_to_tsquery('english', '"sad cat" or "fat rat"'); |
| 1029 |
websearch_to_tsquery |
| 1030 |
----------------------------------- |
| 1031 |
'sad' <-> 'cat' | 'fat' <-> 'rat' |
| 1032 |
(1 row) |
| 1033 |
|
| 1034 |
SELECT websearch_to_tsquery('english', 'signal -"segmentation fault"'); |
| 1035 |
websearch_to_tsquery |
| 1036 |
--------------------------------------- |
| 1037 |
'signal' & !( 'segment' <-> 'fault' ) |
| 1038 |
(1 row) |
| 1039 |
|
| 1040 |
SELECT websearch_to_tsquery('english', '""" )( dummy \\ query <->'); |
| 1041 |
websearch_to_tsquery |
| 1042 |
---------------------- |
| 1043 |
'dummi' & '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 & 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 & 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><b></literal></quote> and |
| 1344 |
<quote><literal></b></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 & similarity')); |
| 1405 |
ts_headline |
| 1406 |
------------------------------------------------------------ |
| 1407 |
containing given <b>query</b> terms + |
| 1408 |
and return them in order of their <b>similarity</b> to the+ |
| 1409 |
<b>query</b>. |
| 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 & term'), |
| 1417 |
'MaxFragments=10, MaxWords=7, MinWords=3, StartSel=<<, StopSel=>>'); |
| 1418 |
ts_headline |
| 1419 |
------------------------------------------------------------ |
| 1420 |
<<Search>> <<terms>> may occur + |
| 1421 |
many times ... ranking of the <<search>> 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><-></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> && <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> <-> <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><-></literal> (FOLLOWED BY) |
| 1640 |
<type>tsquery</type> operator. For example: |
| 1641 |
|
| 1642 |
<screen> |
| 1643 |
SELECT to_tsquery('fat') <-> to_tsquery('cat | rat'); |
| 1644 |
?column? |
| 1645 |
---------------------------- |
| 1646 |
'fat' <-> ( '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><<replaceable>N</replaceable>></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' <10> '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 > 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 & 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 & b'::tsquery, 'a'::tsquery, 'c'::tsquery); |
| 1794 |
ts_rewrite |
| 1795 |
------------ |
| 1796 |
'b' & '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 & b'::tsquery, 'SELECT t,s FROM aliases'); |
| 1823 |
ts_rewrite |
| 1824 |
------------ |
| 1825 |
'b' & '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 & crab'), 'SELECT * FROM aliases'); |
| 1848 |
ts_rewrite |
| 1849 |
--------------------------------- |
| 1850 |
'crab' & ( '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 & !nebulae') |
| 1858 |
WHERE t = to_tsquery('supernovae'); |
| 1859 |
|
| 1860 |
SELECT ts_rewrite(to_tsquery('supernovae & crab'), 'SELECT * FROM aliases'); |
| 1861 |
ts_rewrite |
| 1862 |
--------------------------------------------- |
| 1863 |
'crab' & ( 'supernova' | 'sn' & !'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 & b'::tsquery, |
| 1876 |
'SELECT t,s FROM aliases WHERE ''a & b''::tsquery @> t'); |
| 1877 |
ts_rewrite |
| 1878 |
------------ |
| 1879 |
'b' & '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 & 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 — for |
| 1971 |
example, to weight title differently from body — 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> — the value of a lexeme |
| 2029 |
</para> |
| 2030 |
</listitem> |
| 2031 |
<listitem> |
| 2032 |
<para> |
| 2033 |
<replaceable>ndoc</replaceable> <type>integer</type> — 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> — 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 — 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ñ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ógico-matemá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ógico</literal> or <literal>matemática</literal> |
| 2145 |
in the context <literal>lógico-matemá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><a href="dictionaries.html"></literal></entry> |
| 2212 |
</row> |
| 2213 |
<row> |
| 2214 |
<entry><literal>entity</literal></entry> |
| 2215 |
<entry>XML entity</entry> |
| 2216 |
<entry><literal>&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><!--</literal> and ending |
| 2251 |
with <literal>--></literal>, and XML declarations (but note that this |
| 2252 |
includes anything starting with <literal><?x</literal> and ending with |
| 2253 |
<literal>></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 — 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 & stop')); |
| 2463 |
ts_rank_cd |
| 2464 |
------------ |
| 2465 |
0.05 |
| 2466 |
|
| 2467 |
SELECT ts_rank_cd (to_tsvector('english', 'list stop words'), to_tsquery('list & 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 |
— <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' & 'supernova' & '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 — <ulink |
| 2934 |
url="https://en.wikipedia.org/wiki/MySpell">MySpell</ulink> (OO < 2.0.1) |
| 2935 |
and <ulink url="https://hunspell.github.io/">Hunspell</ulink> |
| 2936 |
(OO >= 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 |
=> \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> — short name of the token type |
| 3347 |
</para> |
| 3348 |
</listitem> |
| 3349 |
<listitem> |
| 3350 |
<para> |
| 3351 |
<replaceable>description</replaceable> <type>text</type> — description of the |
| 3352 |
token type |
| 3353 |
</para> |
| 3354 |
</listitem> |
| 3355 |
<listitem> |
| 3356 |
<para> |
| 3357 |
<replaceable>token</replaceable> <type>text</type> — text of the token |
| 3358 |
</para> |
| 3359 |
</listitem> |
| 3360 |
<listitem> |
| 3361 |
<para> |
| 3362 |
<replaceable>dictionaries</replaceable> <type>regdictionary[]</type> — 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> — 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> — 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 |
=> \dF *fulltext* |
| 3805 |
List of text search configurations |
| 3806 |
Schema | Name | Description |
| 3807 |
--------+--------------+------------- |
| 3808 |
public | fulltext_cfg | |
| 3809 |
</screen> |
| 3810 |
|
| 3811 |
<screen> |
| 3812 |
=> \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 |
=> \dF russian |
| 3831 |
List of text search configurations |
| 3832 |
Schema | Name | Description |
| 3833 |
------------+---------+------------------------------------ |
| 3834 |
pg_catalog | russian | configuration for russian language |
| 3835 |
|
| 3836 |
=> \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 |
=> \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 |
=> \dFp |
| 3917 |
List of text search parsers |
| 3918 |
Schema | Name | Description |
| 3919 |
------------+---------+--------------------- |
| 3920 |
pg_catalog | default | default word parser |
| 3921 |
=> \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 |
=> \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><<replaceable>N</replaceable>></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 — 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> |