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-- test altering boolean parameters 474SELECT dictinitoption FROM pg_ts_dict WHERE dictname = 'synonym'; 475 dictinitoption 476----------------------------- 477 synonyms = 'synonym_sample' 478(1 row) 479 480ALTER TEXT SEARCH DICTIONARY synonym (CaseSensitive = 1); 481SELECT ts_lexize('synonym', 'PoStGrEs'); 482 ts_lexize 483----------- 484 485(1 row) 486 487SELECT dictinitoption FROM pg_ts_dict WHERE dictname = 'synonym'; 488 dictinitoption 489------------------------------------------------ 490 synonyms = 'synonym_sample', casesensitive = 1 491(1 row) 492 493ALTER TEXT SEARCH DICTIONARY synonym (CaseSensitive = 2); -- fail 494ERROR: casesensitive requires a Boolean value 495ALTER TEXT SEARCH DICTIONARY synonym (CaseSensitive = off); 496SELECT ts_lexize('synonym', 'PoStGrEs'); 497 ts_lexize 498----------- 499 {pgsql} 500(1 row) 501 502SELECT dictinitoption FROM pg_ts_dict WHERE dictname = 'synonym'; 503 dictinitoption 504---------------------------------------------------- 505 synonyms = 'synonym_sample', casesensitive = 'off' 506(1 row) 507 508-- Create and simple test thesaurus dictionary 509-- More tests in configuration checks because ts_lexize() 510-- cannot pass more than one word to thesaurus. 511CREATE TEXT SEARCH DICTIONARY thesaurus ( 512 Template=thesaurus, 513 DictFile=thesaurus_sample, 514 Dictionary=english_stem 515); 516SELECT ts_lexize('thesaurus', 'one'); 517 ts_lexize 518----------- 519 {1} 520(1 row) 521 522-- Test ispell dictionary in configuration 523CREATE TEXT SEARCH CONFIGURATION ispell_tst ( 524 COPY=english 525); 526ALTER TEXT SEARCH CONFIGURATION ispell_tst ALTER MAPPING FOR 527 word, numword, asciiword, hword, numhword, asciihword, hword_part, hword_numpart, hword_asciipart 528 WITH ispell, english_stem; 529SELECT to_tsvector('ispell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 530 to_tsvector 531---------------------------------------------------------------------------------------------------- 532 'ball':7 'book':1,5 'booking':1,5 'foot':7,10 'football':7 'footballklubber':7 'klubber':7 'sky':3 533(1 row) 534 535SELECT to_tsquery('ispell_tst', 'footballklubber'); 536 to_tsquery 537-------------------------------------------------------------------------- 538 'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber' 539(1 row) 540 541SELECT to_tsquery('ispell_tst', 'footballyklubber:b & rebookings:A & sky'); 542 to_tsquery 543------------------------------------------------------------------------ 544 'foot':B & 'ball':B & 'klubber':B & ( 'booking':A | 'book':A ) & 'sky' 545(1 row) 546 547-- Test ispell dictionary with hunspell affix in configuration 548CREATE TEXT SEARCH CONFIGURATION hunspell_tst ( 549 COPY=ispell_tst 550); 551ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING 552 REPLACE ispell WITH hunspell; 553SELECT to_tsvector('hunspell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 554 to_tsvector 555---------------------------------------------------------------------------------------------------- 556 'ball':7 'book':1,5 'booking':1,5 'foot':7,10 'football':7 'footballklubber':7 'klubber':7 'sky':3 557(1 row) 558 559SELECT to_tsquery('hunspell_tst', 'footballklubber'); 560 to_tsquery 561-------------------------------------------------------------------------- 562 'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber' 563(1 row) 564 565SELECT to_tsquery('hunspell_tst', 'footballyklubber:b & rebookings:A & sky'); 566 to_tsquery 567------------------------------------------------------------------------ 568 'foot':B & 'ball':B & 'klubber':B & ( 'booking':A | 'book':A ) & 'sky' 569(1 row) 570 571SELECT to_tsquery('hunspell_tst', 'footballyklubber:b <-> sky'); 572 to_tsquery 573------------------------------------------------- 574 ( 'foot':B & 'ball':B & 'klubber':B ) <-> 'sky' 575(1 row) 576 577SELECT phraseto_tsquery('hunspell_tst', 'footballyklubber sky'); 578 phraseto_tsquery 579------------------------------------------- 580 ( 'foot' & 'ball' & 'klubber' ) <-> 'sky' 581(1 row) 582 583-- Test ispell dictionary with hunspell affix with FLAG long in configuration 584ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING 585 REPLACE hunspell WITH hunspell_long; 586SELECT to_tsvector('hunspell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 587 to_tsvector 588---------------------------------------------------------------------------------------------------- 589 'ball':7 'book':1,5 'booking':1,5 'foot':7,10 'football':7 'footballklubber':7 'klubber':7 'sky':3 590(1 row) 591 592SELECT to_tsquery('hunspell_tst', 'footballklubber'); 593 to_tsquery 594-------------------------------------------------------------------------- 595 'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber' 596(1 row) 597 598SELECT to_tsquery('hunspell_tst', 'footballyklubber:b & rebookings:A & sky'); 599 to_tsquery 600------------------------------------------------------------------------ 601 'foot':B & 'ball':B & 'klubber':B & ( 'booking':A | 'book':A ) & 'sky' 602(1 row) 603 604-- Test ispell dictionary with hunspell affix with FLAG num in configuration 605ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING 606 REPLACE hunspell_long WITH hunspell_num; 607SELECT to_tsvector('hunspell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 608 to_tsvector 609---------------------------------------------------------------------------------------------------- 610 'ball':7 'book':1,5 'booking':1,5 'foot':7,10 'football':7 'footballklubber':7 'klubber':7 'sky':3 611(1 row) 612 613SELECT to_tsquery('hunspell_tst', 'footballklubber'); 614 to_tsquery 615-------------------------------------------------------------------------- 616 'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber' 617(1 row) 618 619SELECT to_tsquery('hunspell_tst', 'footballyklubber:b & rebookings:A & sky'); 620 to_tsquery 621------------------------------------------------------------------------ 622 'foot':B & 'ball':B & 'klubber':B & ( 'booking':A | 'book':A ) & 'sky' 623(1 row) 624 625-- Test synonym dictionary in configuration 626CREATE TEXT SEARCH CONFIGURATION synonym_tst ( 627 COPY=english 628); 629ALTER TEXT SEARCH CONFIGURATION synonym_tst ALTER MAPPING FOR 630 asciiword, hword_asciipart, asciihword 631 WITH synonym, english_stem; 632SELECT to_tsvector('synonym_tst', 'Postgresql is often called as postgres or pgsql and pronounced as postgre'); 633 to_tsvector 634--------------------------------------------------- 635 'call':4 'often':3 'pgsql':1,6,8,12 'pronounc':10 636(1 row) 637 638SELECT to_tsvector('synonym_tst', 'Most common mistake is to write Gogle instead of Google'); 639 to_tsvector 640---------------------------------------------------------- 641 'common':2 'googl':7,10 'instead':8 'mistak':3 'write':6 642(1 row) 643 644SELECT to_tsvector('synonym_tst', 'Indexes or indices - Which is right plural form of index?'); 645 to_tsvector 646---------------------------------------------- 647 'form':8 'index':1,3,10 'plural':7 'right':6 648(1 row) 649 650SELECT to_tsquery('synonym_tst', 'Index & indices'); 651 to_tsquery 652--------------------- 653 'index' & 'index':* 654(1 row) 655 656-- test thesaurus in configuration 657-- see thesaurus_sample.ths to understand 'odd' resulting tsvector 658CREATE TEXT SEARCH CONFIGURATION thesaurus_tst ( 659 COPY=synonym_tst 660); 661ALTER TEXT SEARCH CONFIGURATION thesaurus_tst ALTER MAPPING FOR 662 asciiword, hword_asciipart, asciihword 663 WITH synonym, thesaurus, english_stem; 664SELECT to_tsvector('thesaurus_tst', 'one postgres one two one two three one'); 665 to_tsvector 666---------------------------------- 667 '1':1,5 '12':3 '123':4 'pgsql':2 668(1 row) 669 670SELECT to_tsvector('thesaurus_tst', 'Supernovae star is very new star and usually called supernovae (abbreviation SN)'); 671 to_tsvector 672-------------------------------------------------------------- 673 'abbrevi':10 'call':8 'new':4 'sn':1,9,11 'star':5 'usual':7 674(1 row) 675 676SELECT to_tsvector('thesaurus_tst', 'Booking tickets is looking like a booking a tickets'); 677 to_tsvector 678------------------------------------------------------- 679 'card':3,10 'invit':2,9 'like':6 'look':5 'order':1,8 680(1 row) 681 682-- invalid: non-lowercase quoted identifiers 683CREATE TEXT SEARCH DICTIONARY tsdict_case 684( 685 Template = ispell, 686 "DictFile" = ispell_sample, 687 "AffFile" = ispell_sample 688); 689ERROR: unrecognized Ispell parameter: "DictFile" 690