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', 'booked'); 267 ts_lexize 268----------- 269 {book} 270(1 row) 271 272SELECT ts_lexize('hunspell_long', 'footklubber'); 273 ts_lexize 274---------------- 275 {foot,klubber} 276(1 row) 277 278SELECT ts_lexize('hunspell_long', 'footballklubber'); 279 ts_lexize 280------------------------------------------------------ 281 {footballklubber,foot,ball,klubber,football,klubber} 282(1 row) 283 284SELECT ts_lexize('hunspell_long', 'ballyklubber'); 285 ts_lexize 286---------------- 287 {ball,klubber} 288(1 row) 289 290SELECT ts_lexize('hunspell_long', 'ballsklubber'); 291 ts_lexize 292---------------- 293 {ball,klubber} 294(1 row) 295 296SELECT ts_lexize('hunspell_long', 'footballyklubber'); 297 ts_lexize 298--------------------- 299 {foot,ball,klubber} 300(1 row) 301 302SELECT ts_lexize('hunspell_long', 'ex-machina'); 303 ts_lexize 304--------------- 305 {ex-,machina} 306(1 row) 307 308-- Test ISpell dictionary with hunspell affix file with FLAG num parameter 309CREATE TEXT SEARCH DICTIONARY hunspell_num ( 310 Template=ispell, 311 DictFile=hunspell_sample_num, 312 AffFile=hunspell_sample_num 313); 314SELECT ts_lexize('hunspell_num', 'skies'); 315 ts_lexize 316----------- 317 {sky} 318(1 row) 319 320SELECT ts_lexize('hunspell_num', 'sk'); 321 ts_lexize 322----------- 323 {sky} 324(1 row) 325 326SELECT ts_lexize('hunspell_num', 'bookings'); 327 ts_lexize 328---------------- 329 {booking,book} 330(1 row) 331 332SELECT ts_lexize('hunspell_num', 'booking'); 333 ts_lexize 334---------------- 335 {booking,book} 336(1 row) 337 338SELECT ts_lexize('hunspell_num', 'foot'); 339 ts_lexize 340----------- 341 {foot} 342(1 row) 343 344SELECT ts_lexize('hunspell_num', 'foots'); 345 ts_lexize 346----------- 347 {foot} 348(1 row) 349 350SELECT ts_lexize('hunspell_num', 'rebookings'); 351 ts_lexize 352---------------- 353 {booking,book} 354(1 row) 355 356SELECT ts_lexize('hunspell_num', 'rebooking'); 357 ts_lexize 358---------------- 359 {booking,book} 360(1 row) 361 362SELECT ts_lexize('hunspell_num', 'rebook'); 363 ts_lexize 364----------- 365 366(1 row) 367 368SELECT ts_lexize('hunspell_num', 'unbookings'); 369 ts_lexize 370----------- 371 {book} 372(1 row) 373 374SELECT ts_lexize('hunspell_num', 'unbooking'); 375 ts_lexize 376----------- 377 {book} 378(1 row) 379 380SELECT ts_lexize('hunspell_num', 'unbook'); 381 ts_lexize 382----------- 383 {book} 384(1 row) 385 386SELECT ts_lexize('hunspell_num', 'booked'); 387 ts_lexize 388----------- 389 {book} 390(1 row) 391 392SELECT ts_lexize('hunspell_num', 'footklubber'); 393 ts_lexize 394---------------- 395 {foot,klubber} 396(1 row) 397 398SELECT ts_lexize('hunspell_num', 'footballklubber'); 399 ts_lexize 400------------------------------------------------------ 401 {footballklubber,foot,ball,klubber,football,klubber} 402(1 row) 403 404SELECT ts_lexize('hunspell_num', 'ballyklubber'); 405 ts_lexize 406---------------- 407 {ball,klubber} 408(1 row) 409 410SELECT ts_lexize('hunspell_num', 'footballyklubber'); 411 ts_lexize 412--------------------- 413 {foot,ball,klubber} 414(1 row) 415 416-- Test suitability of affix and dict files 417CREATE TEXT SEARCH DICTIONARY hunspell_err ( 418 Template=ispell, 419 DictFile=ispell_sample, 420 AffFile=hunspell_sample_long 421); 422ERROR: invalid affix alias "GJUS" 423CREATE TEXT SEARCH DICTIONARY hunspell_err ( 424 Template=ispell, 425 DictFile=ispell_sample, 426 AffFile=hunspell_sample_num 427); 428ERROR: invalid affix flag "SZ\" 429CREATE TEXT SEARCH DICTIONARY hunspell_invalid_1 ( 430 Template=ispell, 431 DictFile=hunspell_sample_long, 432 AffFile=ispell_sample 433); 434CREATE TEXT SEARCH DICTIONARY hunspell_invalid_2 ( 435 Template=ispell, 436 DictFile=hunspell_sample_long, 437 AffFile=hunspell_sample_num 438); 439CREATE TEXT SEARCH DICTIONARY hunspell_invalid_3 ( 440 Template=ispell, 441 DictFile=hunspell_sample_num, 442 AffFile=ispell_sample 443); 444CREATE TEXT SEARCH DICTIONARY hunspell_err ( 445 Template=ispell, 446 DictFile=hunspell_sample_num, 447 AffFile=hunspell_sample_long 448); 449ERROR: invalid affix alias "302,301,202,303" 450-- Synonym dictionary 451CREATE TEXT SEARCH DICTIONARY synonym ( 452 Template=synonym, 453 Synonyms=synonym_sample 454); 455SELECT ts_lexize('synonym', 'PoStGrEs'); 456 ts_lexize 457----------- 458 {pgsql} 459(1 row) 460 461SELECT ts_lexize('synonym', 'Gogle'); 462 ts_lexize 463----------- 464 {googl} 465(1 row) 466 467SELECT ts_lexize('synonym', 'indices'); 468 ts_lexize 469----------- 470 {index} 471(1 row) 472 473-- Create and simple test thesaurus dictionary 474-- More tests in configuration checks because ts_lexize() 475-- cannot pass more than one word to thesaurus. 476CREATE TEXT SEARCH DICTIONARY thesaurus ( 477 Template=thesaurus, 478 DictFile=thesaurus_sample, 479 Dictionary=english_stem 480); 481SELECT ts_lexize('thesaurus', 'one'); 482 ts_lexize 483----------- 484 {1} 485(1 row) 486 487-- Test ispell dictionary in configuration 488CREATE TEXT SEARCH CONFIGURATION ispell_tst ( 489 COPY=english 490); 491ALTER TEXT SEARCH CONFIGURATION ispell_tst ALTER MAPPING FOR 492 word, numword, asciiword, hword, numhword, asciihword, hword_part, hword_numpart, hword_asciipart 493 WITH ispell, english_stem; 494SELECT to_tsvector('ispell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 495 to_tsvector 496---------------------------------------------------------------------------------------------------- 497 'ball':7 'book':1,5 'booking':1,5 'foot':7,10 'football':7 'footballklubber':7 'klubber':7 'sky':3 498(1 row) 499 500SELECT to_tsquery('ispell_tst', 'footballklubber'); 501 to_tsquery 502-------------------------------------------------------------------------- 503 'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber' 504(1 row) 505 506SELECT to_tsquery('ispell_tst', 'footballyklubber:b & rebookings:A & sky'); 507 to_tsquery 508------------------------------------------------------------------------ 509 'foot':B & 'ball':B & 'klubber':B & ( 'booking':A | 'book':A ) & 'sky' 510(1 row) 511 512-- Test ispell dictionary with hunspell affix in configuration 513CREATE TEXT SEARCH CONFIGURATION hunspell_tst ( 514 COPY=ispell_tst 515); 516ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING 517 REPLACE ispell WITH hunspell; 518SELECT to_tsvector('hunspell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 519 to_tsvector 520---------------------------------------------------------------------------------------------------- 521 'ball':7 'book':1,5 'booking':1,5 'foot':7,10 'football':7 'footballklubber':7 'klubber':7 'sky':3 522(1 row) 523 524SELECT to_tsquery('hunspell_tst', 'footballklubber'); 525 to_tsquery 526-------------------------------------------------------------------------- 527 'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber' 528(1 row) 529 530SELECT to_tsquery('hunspell_tst', 'footballyklubber:b & rebookings:A & sky'); 531 to_tsquery 532------------------------------------------------------------------------ 533 'foot':B & 'ball':B & 'klubber':B & ( 'booking':A | 'book':A ) & 'sky' 534(1 row) 535 536SELECT to_tsquery('hunspell_tst', 'footballyklubber:b <-> sky'); 537 to_tsquery 538------------------------------------------------- 539 ( 'foot':B & 'ball':B & 'klubber':B ) <-> 'sky' 540(1 row) 541 542SELECT phraseto_tsquery('hunspell_tst', 'footballyklubber sky'); 543 phraseto_tsquery 544------------------------------------------- 545 ( 'foot' & 'ball' & 'klubber' ) <-> 'sky' 546(1 row) 547 548-- Test ispell dictionary with hunspell affix with FLAG long in configuration 549ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING 550 REPLACE hunspell WITH hunspell_long; 551SELECT to_tsvector('hunspell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 552 to_tsvector 553---------------------------------------------------------------------------------------------------- 554 'ball':7 'book':1,5 'booking':1,5 'foot':7,10 'football':7 'footballklubber':7 'klubber':7 'sky':3 555(1 row) 556 557SELECT to_tsquery('hunspell_tst', 'footballklubber'); 558 to_tsquery 559-------------------------------------------------------------------------- 560 'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber' 561(1 row) 562 563SELECT to_tsquery('hunspell_tst', 'footballyklubber:b & rebookings:A & sky'); 564 to_tsquery 565------------------------------------------------------------------------ 566 'foot':B & 'ball':B & 'klubber':B & ( 'booking':A | 'book':A ) & 'sky' 567(1 row) 568 569-- Test ispell dictionary with hunspell affix with FLAG num in configuration 570ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING 571 REPLACE hunspell_long WITH hunspell_num; 572SELECT to_tsvector('hunspell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 573 to_tsvector 574---------------------------------------------------------------------------------------------------- 575 'ball':7 'book':1,5 'booking':1,5 'foot':7,10 'football':7 'footballklubber':7 'klubber':7 'sky':3 576(1 row) 577 578SELECT to_tsquery('hunspell_tst', 'footballklubber'); 579 to_tsquery 580-------------------------------------------------------------------------- 581 'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber' 582(1 row) 583 584SELECT to_tsquery('hunspell_tst', 'footballyklubber:b & rebookings:A & sky'); 585 to_tsquery 586------------------------------------------------------------------------ 587 'foot':B & 'ball':B & 'klubber':B & ( 'booking':A | 'book':A ) & 'sky' 588(1 row) 589 590-- Test synonym dictionary in configuration 591CREATE TEXT SEARCH CONFIGURATION synonym_tst ( 592 COPY=english 593); 594ALTER TEXT SEARCH CONFIGURATION synonym_tst ALTER MAPPING FOR 595 asciiword, hword_asciipart, asciihword 596 WITH synonym, english_stem; 597SELECT to_tsvector('synonym_tst', 'Postgresql is often called as postgres or pgsql and pronounced as postgre'); 598 to_tsvector 599--------------------------------------------------- 600 'call':4 'often':3 'pgsql':1,6,8,12 'pronounc':10 601(1 row) 602 603SELECT to_tsvector('synonym_tst', 'Most common mistake is to write Gogle instead of Google'); 604 to_tsvector 605---------------------------------------------------------- 606 'common':2 'googl':7,10 'instead':8 'mistak':3 'write':6 607(1 row) 608 609SELECT to_tsvector('synonym_tst', 'Indexes or indices - Which is right plural form of index?'); 610 to_tsvector 611---------------------------------------------- 612 'form':8 'index':1,3,10 'plural':7 'right':6 613(1 row) 614 615SELECT to_tsquery('synonym_tst', 'Index & indices'); 616 to_tsquery 617--------------------- 618 'index' & 'index':* 619(1 row) 620 621-- test thesaurus in configuration 622-- see thesaurus_sample.ths to understand 'odd' resulting tsvector 623CREATE TEXT SEARCH CONFIGURATION thesaurus_tst ( 624 COPY=synonym_tst 625); 626ALTER TEXT SEARCH CONFIGURATION thesaurus_tst ALTER MAPPING FOR 627 asciiword, hword_asciipart, asciihword 628 WITH synonym, thesaurus, english_stem; 629SELECT to_tsvector('thesaurus_tst', 'one postgres one two one two three one'); 630 to_tsvector 631---------------------------------- 632 '1':1,5 '12':3 '123':4 'pgsql':2 633(1 row) 634 635SELECT to_tsvector('thesaurus_tst', 'Supernovae star is very new star and usually called supernovae (abbreviation SN)'); 636 to_tsvector 637-------------------------------------------------------------- 638 'abbrevi':10 'call':8 'new':4 'sn':1,9,11 'star':5 'usual':7 639(1 row) 640 641SELECT to_tsvector('thesaurus_tst', 'Booking tickets is looking like a booking a tickets'); 642 to_tsvector 643------------------------------------------------------- 644 'card':3,10 'invit':2,9 'like':6 'look':5 'order':1,8 645(1 row) 646 647-- invalid: non-lowercase quoted identifiers 648CREATE TEXT SEARCH DICTIONARY tsdict_case 649( 650 Template = ispell, 651 "DictFile" = ispell_sample, 652 "AffFile" = ispell_sample 653); 654ERROR: unrecognized Ispell parameter: "DictFile" 655