1--Test text search dictionaries and configurations 2-- Test ISpell dictionary with ispell affix file 3CREATE TEXT SEARCH DICTIONARY ispell ( 4 Template=ispell, 5 DictFile=ispell_sample, 6 AffFile=ispell_sample 7); 8SELECT ts_lexize('ispell', 'skies'); 9 ts_lexize 10----------- 11 {sky} 12(1 row) 13 14SELECT ts_lexize('ispell', 'bookings'); 15 ts_lexize 16---------------- 17 {booking,book} 18(1 row) 19 20SELECT ts_lexize('ispell', 'booking'); 21 ts_lexize 22---------------- 23 {booking,book} 24(1 row) 25 26SELECT ts_lexize('ispell', 'foot'); 27 ts_lexize 28----------- 29 {foot} 30(1 row) 31 32SELECT ts_lexize('ispell', 'foots'); 33 ts_lexize 34----------- 35 {foot} 36(1 row) 37 38SELECT ts_lexize('ispell', 'rebookings'); 39 ts_lexize 40---------------- 41 {booking,book} 42(1 row) 43 44SELECT ts_lexize('ispell', 'rebooking'); 45 ts_lexize 46---------------- 47 {booking,book} 48(1 row) 49 50SELECT ts_lexize('ispell', 'rebook'); 51 ts_lexize 52----------- 53 54(1 row) 55 56SELECT ts_lexize('ispell', 'unbookings'); 57 ts_lexize 58----------- 59 {book} 60(1 row) 61 62SELECT ts_lexize('ispell', 'unbooking'); 63 ts_lexize 64----------- 65 {book} 66(1 row) 67 68SELECT ts_lexize('ispell', 'unbook'); 69 ts_lexize 70----------- 71 {book} 72(1 row) 73 74SELECT ts_lexize('ispell', 'footklubber'); 75 ts_lexize 76---------------- 77 {foot,klubber} 78(1 row) 79 80SELECT ts_lexize('ispell', 'footballklubber'); 81 ts_lexize 82------------------------------------------------------ 83 {footballklubber,foot,ball,klubber,football,klubber} 84(1 row) 85 86SELECT ts_lexize('ispell', 'ballyklubber'); 87 ts_lexize 88---------------- 89 {ball,klubber} 90(1 row) 91 92SELECT ts_lexize('ispell', 'footballyklubber'); 93 ts_lexize 94--------------------- 95 {foot,ball,klubber} 96(1 row) 97 98-- Test ISpell dictionary with hunspell affix file 99CREATE TEXT SEARCH DICTIONARY hunspell ( 100 Template=ispell, 101 DictFile=ispell_sample, 102 AffFile=hunspell_sample 103); 104SELECT ts_lexize('hunspell', 'skies'); 105 ts_lexize 106----------- 107 {sky} 108(1 row) 109 110SELECT ts_lexize('hunspell', 'bookings'); 111 ts_lexize 112---------------- 113 {booking,book} 114(1 row) 115 116SELECT ts_lexize('hunspell', 'booking'); 117 ts_lexize 118---------------- 119 {booking,book} 120(1 row) 121 122SELECT ts_lexize('hunspell', 'foot'); 123 ts_lexize 124----------- 125 {foot} 126(1 row) 127 128SELECT ts_lexize('hunspell', 'foots'); 129 ts_lexize 130----------- 131 {foot} 132(1 row) 133 134SELECT ts_lexize('hunspell', 'rebookings'); 135 ts_lexize 136---------------- 137 {booking,book} 138(1 row) 139 140SELECT ts_lexize('hunspell', 'rebooking'); 141 ts_lexize 142---------------- 143 {booking,book} 144(1 row) 145 146SELECT ts_lexize('hunspell', 'rebook'); 147 ts_lexize 148----------- 149 150(1 row) 151 152SELECT ts_lexize('hunspell', 'unbookings'); 153 ts_lexize 154----------- 155 {book} 156(1 row) 157 158SELECT ts_lexize('hunspell', 'unbooking'); 159 ts_lexize 160----------- 161 {book} 162(1 row) 163 164SELECT ts_lexize('hunspell', 'unbook'); 165 ts_lexize 166----------- 167 {book} 168(1 row) 169 170SELECT ts_lexize('hunspell', 'footklubber'); 171 ts_lexize 172---------------- 173 {foot,klubber} 174(1 row) 175 176SELECT ts_lexize('hunspell', 'footballklubber'); 177 ts_lexize 178------------------------------------------------------ 179 {footballklubber,foot,ball,klubber,football,klubber} 180(1 row) 181 182SELECT ts_lexize('hunspell', 'ballyklubber'); 183 ts_lexize 184---------------- 185 {ball,klubber} 186(1 row) 187 188SELECT ts_lexize('hunspell', 'footballyklubber'); 189 ts_lexize 190--------------------- 191 {foot,ball,klubber} 192(1 row) 193 194-- Test ISpell dictionary with hunspell affix file with FLAG long parameter 195CREATE TEXT SEARCH DICTIONARY hunspell_long ( 196 Template=ispell, 197 DictFile=hunspell_sample_long, 198 AffFile=hunspell_sample_long 199); 200SELECT ts_lexize('hunspell_long', 'skies'); 201 ts_lexize 202----------- 203 {sky} 204(1 row) 205 206SELECT ts_lexize('hunspell_long', 'bookings'); 207 ts_lexize 208---------------- 209 {booking,book} 210(1 row) 211 212SELECT ts_lexize('hunspell_long', 'booking'); 213 ts_lexize 214---------------- 215 {booking,book} 216(1 row) 217 218SELECT ts_lexize('hunspell_long', 'foot'); 219 ts_lexize 220----------- 221 {foot} 222(1 row) 223 224SELECT ts_lexize('hunspell_long', 'foots'); 225 ts_lexize 226----------- 227 {foot} 228(1 row) 229 230SELECT ts_lexize('hunspell_long', 'rebookings'); 231 ts_lexize 232---------------- 233 {booking,book} 234(1 row) 235 236SELECT ts_lexize('hunspell_long', 'rebooking'); 237 ts_lexize 238---------------- 239 {booking,book} 240(1 row) 241 242SELECT ts_lexize('hunspell_long', 'rebook'); 243 ts_lexize 244----------- 245 246(1 row) 247 248SELECT ts_lexize('hunspell_long', 'unbookings'); 249 ts_lexize 250----------- 251 {book} 252(1 row) 253 254SELECT ts_lexize('hunspell_long', 'unbooking'); 255 ts_lexize 256----------- 257 {book} 258(1 row) 259 260SELECT ts_lexize('hunspell_long', 'unbook'); 261 ts_lexize 262----------- 263 {book} 264(1 row) 265 266SELECT ts_lexize('hunspell_long', 'footklubber'); 267 ts_lexize 268---------------- 269 {foot,klubber} 270(1 row) 271 272SELECT ts_lexize('hunspell_long', 'footballklubber'); 273 ts_lexize 274------------------------------------------------------ 275 {footballklubber,foot,ball,klubber,football,klubber} 276(1 row) 277 278SELECT ts_lexize('hunspell_long', 'ballyklubber'); 279 ts_lexize 280---------------- 281 {ball,klubber} 282(1 row) 283 284SELECT ts_lexize('hunspell_long', 'footballyklubber'); 285 ts_lexize 286--------------------- 287 {foot,ball,klubber} 288(1 row) 289 290-- Test ISpell dictionary with hunspell affix file with FLAG num parameter 291CREATE TEXT SEARCH DICTIONARY hunspell_num ( 292 Template=ispell, 293 DictFile=hunspell_sample_num, 294 AffFile=hunspell_sample_num 295); 296SELECT ts_lexize('hunspell_num', 'skies'); 297 ts_lexize 298----------- 299 {sky} 300(1 row) 301 302SELECT ts_lexize('hunspell_num', 'bookings'); 303 ts_lexize 304---------------- 305 {booking,book} 306(1 row) 307 308SELECT ts_lexize('hunspell_num', 'booking'); 309 ts_lexize 310---------------- 311 {booking,book} 312(1 row) 313 314SELECT ts_lexize('hunspell_num', 'foot'); 315 ts_lexize 316----------- 317 {foot} 318(1 row) 319 320SELECT ts_lexize('hunspell_num', 'foots'); 321 ts_lexize 322----------- 323 {foot} 324(1 row) 325 326SELECT ts_lexize('hunspell_num', 'rebookings'); 327 ts_lexize 328---------------- 329 {booking,book} 330(1 row) 331 332SELECT ts_lexize('hunspell_num', 'rebooking'); 333 ts_lexize 334---------------- 335 {booking,book} 336(1 row) 337 338SELECT ts_lexize('hunspell_num', 'rebook'); 339 ts_lexize 340----------- 341 342(1 row) 343 344SELECT ts_lexize('hunspell_num', 'unbookings'); 345 ts_lexize 346----------- 347 {book} 348(1 row) 349 350SELECT ts_lexize('hunspell_num', 'unbooking'); 351 ts_lexize 352----------- 353 {book} 354(1 row) 355 356SELECT ts_lexize('hunspell_num', 'unbook'); 357 ts_lexize 358----------- 359 {book} 360(1 row) 361 362SELECT ts_lexize('hunspell_num', 'footklubber'); 363 ts_lexize 364---------------- 365 {foot,klubber} 366(1 row) 367 368SELECT ts_lexize('hunspell_num', 'footballklubber'); 369 ts_lexize 370------------------------------------------------------ 371 {footballklubber,foot,ball,klubber,football,klubber} 372(1 row) 373 374SELECT ts_lexize('hunspell_num', 'ballyklubber'); 375 ts_lexize 376---------------- 377 {ball,klubber} 378(1 row) 379 380SELECT ts_lexize('hunspell_num', 'footballyklubber'); 381 ts_lexize 382--------------------- 383 {foot,ball,klubber} 384(1 row) 385 386-- Test suitability of affix and dict files 387CREATE TEXT SEARCH DICTIONARY hunspell_err ( 388 Template=ispell, 389 DictFile=ispell_sample, 390 AffFile=hunspell_sample_long 391); 392ERROR: invalid affix alias "GJUS" 393CREATE TEXT SEARCH DICTIONARY hunspell_err ( 394 Template=ispell, 395 DictFile=ispell_sample, 396 AffFile=hunspell_sample_num 397); 398ERROR: invalid affix flag "SZ\" 399CREATE TEXT SEARCH DICTIONARY hunspell_invalid_1 ( 400 Template=ispell, 401 DictFile=hunspell_sample_long, 402 AffFile=ispell_sample 403); 404CREATE TEXT SEARCH DICTIONARY hunspell_invalid_2 ( 405 Template=ispell, 406 DictFile=hunspell_sample_long, 407 AffFile=hunspell_sample_num 408); 409CREATE TEXT SEARCH DICTIONARY hunspell_invalid_3 ( 410 Template=ispell, 411 DictFile=hunspell_sample_num, 412 AffFile=ispell_sample 413); 414CREATE TEXT SEARCH DICTIONARY hunspell_err ( 415 Template=ispell, 416 DictFile=hunspell_sample_num, 417 AffFile=hunspell_sample_long 418); 419ERROR: invalid affix alias "302,301,202,303" 420-- Synonym dictionary 421CREATE TEXT SEARCH DICTIONARY synonym ( 422 Template=synonym, 423 Synonyms=synonym_sample 424); 425SELECT ts_lexize('synonym', 'PoStGrEs'); 426 ts_lexize 427----------- 428 {pgsql} 429(1 row) 430 431SELECT ts_lexize('synonym', 'Gogle'); 432 ts_lexize 433----------- 434 {googl} 435(1 row) 436 437SELECT ts_lexize('synonym', 'indices'); 438 ts_lexize 439----------- 440 {index} 441(1 row) 442 443-- Create and simple test thesaurus dictionary 444-- More tests in configuration checks because ts_lexize() 445-- cannot pass more than one word to thesaurus. 446CREATE TEXT SEARCH DICTIONARY thesaurus ( 447 Template=thesaurus, 448 DictFile=thesaurus_sample, 449 Dictionary=english_stem 450); 451SELECT ts_lexize('thesaurus', 'one'); 452 ts_lexize 453----------- 454 {1} 455(1 row) 456 457-- Test ispell dictionary in configuration 458CREATE TEXT SEARCH CONFIGURATION ispell_tst ( 459 COPY=english 460); 461ALTER TEXT SEARCH CONFIGURATION ispell_tst ALTER MAPPING FOR 462 word, numword, asciiword, hword, numhword, asciihword, hword_part, hword_numpart, hword_asciipart 463 WITH ispell, english_stem; 464SELECT to_tsvector('ispell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 465 to_tsvector 466---------------------------------------------------------------------------------------------------- 467 'ball':7 'book':1,5 'booking':1,5 'foot':7,10 'football':7 'footballklubber':7 'klubber':7 'sky':3 468(1 row) 469 470SELECT to_tsquery('ispell_tst', 'footballklubber'); 471 to_tsquery 472-------------------------------------------------------------------------- 473 'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber' 474(1 row) 475 476SELECT to_tsquery('ispell_tst', 'footballyklubber:b & rebookings:A & sky'); 477 to_tsquery 478------------------------------------------------------------------------ 479 'foot':B & 'ball':B & 'klubber':B & ( 'booking':A | 'book':A ) & 'sky' 480(1 row) 481 482-- Test ispell dictionary with hunspell affix in configuration 483CREATE TEXT SEARCH CONFIGURATION hunspell_tst ( 484 COPY=ispell_tst 485); 486ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING 487 REPLACE ispell WITH hunspell; 488SELECT to_tsvector('hunspell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 489 to_tsvector 490---------------------------------------------------------------------------------------------------- 491 'ball':7 'book':1,5 'booking':1,5 'foot':7,10 'football':7 'footballklubber':7 'klubber':7 'sky':3 492(1 row) 493 494SELECT to_tsquery('hunspell_tst', 'footballklubber'); 495 to_tsquery 496-------------------------------------------------------------------------- 497 'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber' 498(1 row) 499 500SELECT to_tsquery('hunspell_tst', 'footballyklubber:b & rebookings:A & sky'); 501 to_tsquery 502------------------------------------------------------------------------ 503 'foot':B & 'ball':B & 'klubber':B & ( 'booking':A | 'book':A ) & 'sky' 504(1 row) 505 506SELECT to_tsquery('hunspell_tst', 'footballyklubber:b <-> sky'); 507 to_tsquery 508------------------------------------------------- 509 ( 'foot':B & 'ball':B & 'klubber':B ) <-> 'sky' 510(1 row) 511 512SELECT phraseto_tsquery('hunspell_tst', 'footballyklubber sky'); 513 phraseto_tsquery 514------------------------------------------- 515 ( 'foot' & 'ball' & 'klubber' ) <-> 'sky' 516(1 row) 517 518-- Test ispell dictionary with hunspell affix with FLAG long in configuration 519ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING 520 REPLACE hunspell WITH hunspell_long; 521SELECT to_tsvector('hunspell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 522 to_tsvector 523---------------------------------------------------------------------------------------------------- 524 'ball':7 'book':1,5 'booking':1,5 'foot':7,10 'football':7 'footballklubber':7 'klubber':7 'sky':3 525(1 row) 526 527SELECT to_tsquery('hunspell_tst', 'footballklubber'); 528 to_tsquery 529-------------------------------------------------------------------------- 530 'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber' 531(1 row) 532 533SELECT to_tsquery('hunspell_tst', 'footballyklubber:b & rebookings:A & sky'); 534 to_tsquery 535------------------------------------------------------------------------ 536 'foot':B & 'ball':B & 'klubber':B & ( 'booking':A | 'book':A ) & 'sky' 537(1 row) 538 539-- Test ispell dictionary with hunspell affix with FLAG num in configuration 540ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING 541 REPLACE hunspell_long WITH hunspell_num; 542SELECT to_tsvector('hunspell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 543 to_tsvector 544---------------------------------------------------------------------------------------------------- 545 'ball':7 'book':1,5 'booking':1,5 'foot':7,10 'football':7 'footballklubber':7 'klubber':7 'sky':3 546(1 row) 547 548SELECT to_tsquery('hunspell_tst', 'footballklubber'); 549 to_tsquery 550-------------------------------------------------------------------------- 551 'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber' 552(1 row) 553 554SELECT to_tsquery('hunspell_tst', 'footballyklubber:b & rebookings:A & sky'); 555 to_tsquery 556------------------------------------------------------------------------ 557 'foot':B & 'ball':B & 'klubber':B & ( 'booking':A | 'book':A ) & 'sky' 558(1 row) 559 560-- Test synonym dictionary in configuration 561CREATE TEXT SEARCH CONFIGURATION synonym_tst ( 562 COPY=english 563); 564ALTER TEXT SEARCH CONFIGURATION synonym_tst ALTER MAPPING FOR 565 asciiword, hword_asciipart, asciihword 566 WITH synonym, english_stem; 567SELECT to_tsvector('synonym_tst', 'Postgresql is often called as postgres or pgsql and pronounced as postgre'); 568 to_tsvector 569--------------------------------------------------- 570 'call':4 'often':3 'pgsql':1,6,8,12 'pronounc':10 571(1 row) 572 573SELECT to_tsvector('synonym_tst', 'Most common mistake is to write Gogle instead of Google'); 574 to_tsvector 575---------------------------------------------------------- 576 'common':2 'googl':7,10 'instead':8 'mistak':3 'write':6 577(1 row) 578 579SELECT to_tsvector('synonym_tst', 'Indexes or indices - Which is right plural form of index?'); 580 to_tsvector 581---------------------------------------------- 582 'form':8 'index':1,3,10 'plural':7 'right':6 583(1 row) 584 585SELECT to_tsquery('synonym_tst', 'Index & indices'); 586 to_tsquery 587--------------------- 588 'index' & 'index':* 589(1 row) 590 591-- test thesaurus in configuration 592-- see thesaurus_sample.ths to understand 'odd' resulting tsvector 593CREATE TEXT SEARCH CONFIGURATION thesaurus_tst ( 594 COPY=synonym_tst 595); 596ALTER TEXT SEARCH CONFIGURATION thesaurus_tst ALTER MAPPING FOR 597 asciiword, hword_asciipart, asciihword 598 WITH synonym, thesaurus, english_stem; 599SELECT to_tsvector('thesaurus_tst', 'one postgres one two one two three one'); 600 to_tsvector 601---------------------------------- 602 '1':1,5 '12':3 '123':4 'pgsql':2 603(1 row) 604 605SELECT to_tsvector('thesaurus_tst', 'Supernovae star is very new star and usually called supernovae (abbreviation SN)'); 606 to_tsvector 607-------------------------------------------------------------- 608 'abbrevi':10 'call':8 'new':4 'sn':1,9,11 'star':5 'usual':7 609(1 row) 610 611SELECT to_tsvector('thesaurus_tst', 'Booking tickets is looking like a booking a tickets'); 612 to_tsvector 613------------------------------------------------------- 614 'card':3,10 'invit':2,9 'like':6 'look':5 'order':1,8 615(1 row) 616 617