src/test/regress/expected/tsdicts.out

   1 --Test text search dictionaries and configurations
   2 -- Test ISpell dictionary with ispell affix file
   3 CREATE TEXT SEARCH DICTIONARY ispell (
   4                         Template=ispell,
   5                         DictFile=ispell_sample,
   6                         AffFile=ispell_sample
   7 );
   8 SELECT ts_lexize('ispell', 'skies');
   9  ts_lexize
  10 -----------
  11  {sky}
  12 (1 row)
  13
  14 SELECT ts_lexize('ispell', 'bookings');
  15    ts_lexize
  16 ----------------
  17  {booking,book}
  18 (1 row)
  19
  20 SELECT ts_lexize('ispell', 'booking');
  21    ts_lexize
  22 ----------------
  23  {booking,book}
  24 (1 row)
  25
  26 SELECT ts_lexize('ispell', 'foot');
  27  ts_lexize
  28 -----------
  29  {foot}
  30 (1 row)
  31
  32 SELECT ts_lexize('ispell', 'foots');
  33  ts_lexize
  34 -----------
  35  {foot}
  36 (1 row)
  37
  38 SELECT ts_lexize('ispell', 'rebookings');
  39    ts_lexize
  40 ----------------
  41  {booking,book}
  42 (1 row)
  43
  44 SELECT ts_lexize('ispell', 'rebooking');
  45    ts_lexize
  46 ----------------
  47  {booking,book}
  48 (1 row)
  49
  50 SELECT ts_lexize('ispell', 'rebook');
  51  ts_lexize
  52 -----------
  53
  54 (1 row)
  55
  56 SELECT ts_lexize('ispell', 'unbookings');
  57  ts_lexize
  58 -----------
  59  {book}
  60 (1 row)
  61
  62 SELECT ts_lexize('ispell', 'unbooking');
  63  ts_lexize
  64 -----------
  65  {book}
  66 (1 row)
  67
  68 SELECT ts_lexize('ispell', 'unbook');
  69  ts_lexize
  70 -----------
  71  {book}
  72 (1 row)
  73
  74 SELECT ts_lexize('ispell', 'footklubber');
  75    ts_lexize
  76 ----------------
  77  {foot,klubber}
  78 (1 row)
  79
  80 SELECT ts_lexize('ispell', 'footballklubber');
  81                       ts_lexize
  82 ------------------------------------------------------
  83  {footballklubber,foot,ball,klubber,football,klubber}
  84 (1 row)
  85
  86 SELECT ts_lexize('ispell', 'ballyklubber');
  87    ts_lexize
  88 ----------------
  89  {ball,klubber}
  90 (1 row)
  91
  92 SELECT ts_lexize('ispell', 'footballyklubber');
  93       ts_lexize
  94 ---------------------
  95  {foot,ball,klubber}
  96 (1 row)
  97
  98 -- Test ISpell dictionary with hunspell affix file
  99 CREATE TEXT SEARCH DICTIONARY hunspell (
 100                         Template=ispell,
 101                         DictFile=ispell_sample,
 102                         AffFile=hunspell_sample
 103 );
 104 SELECT ts_lexize('hunspell', 'skies');
 105  ts_lexize
 106 -----------
 107  {sky}
 108 (1 row)
 109
 110 SELECT ts_lexize('hunspell', 'bookings');
 111    ts_lexize
 112 ----------------
 113  {booking,book}
 114 (1 row)
 115
 116 SELECT ts_lexize('hunspell', 'booking');
 117    ts_lexize
 118 ----------------
 119  {booking,book}
 120 (1 row)
 121
 122 SELECT ts_lexize('hunspell', 'foot');
 123  ts_lexize
 124 -----------
 125  {foot}
 126 (1 row)
 127
 128 SELECT ts_lexize('hunspell', 'foots');
 129  ts_lexize
 130 -----------
 131  {foot}
 132 (1 row)
 133
 134 SELECT ts_lexize('hunspell', 'rebookings');
 135    ts_lexize
 136 ----------------
 137  {booking,book}
 138 (1 row)
 139
 140 SELECT ts_lexize('hunspell', 'rebooking');
 141    ts_lexize
 142 ----------------
 143  {booking,book}
 144 (1 row)
 145
 146 SELECT ts_lexize('hunspell', 'rebook');
 147  ts_lexize
 148 -----------
 149
 150 (1 row)
 151
 152 SELECT ts_lexize('hunspell', 'unbookings');
 153  ts_lexize
 154 -----------
 155  {book}
 156 (1 row)
 157
 158 SELECT ts_lexize('hunspell', 'unbooking');
 159  ts_lexize
 160 -----------
 161  {book}
 162 (1 row)
 163
 164 SELECT ts_lexize('hunspell', 'unbook');
 165  ts_lexize
 166 -----------
 167  {book}
 168 (1 row)
 169
 170 SELECT ts_lexize('hunspell', 'footklubber');
 171    ts_lexize
 172 ----------------
 173  {foot,klubber}
 174 (1 row)
 175
 176 SELECT ts_lexize('hunspell', 'footballklubber');
 177                       ts_lexize
 178 ------------------------------------------------------
 179  {footballklubber,foot,ball,klubber,football,klubber}
 180 (1 row)
 181
 182 SELECT ts_lexize('hunspell', 'ballyklubber');
 183    ts_lexize
 184 ----------------
 185  {ball,klubber}
 186 (1 row)
 187
 188 SELECT 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
 195 CREATE TEXT SEARCH DICTIONARY hunspell_long (
 196                         Template=ispell,
 197                         DictFile=hunspell_sample_long,
 198                         AffFile=hunspell_sample_long
 199 );
 200 SELECT ts_lexize('hunspell_long', 'skies');
 201  ts_lexize
 202 -----------
 203  {sky}
 204 (1 row)
 205
 206 SELECT ts_lexize('hunspell_long', 'bookings');
 207    ts_lexize
 208 ----------------
 209  {booking,book}
 210 (1 row)
 211
 212 SELECT ts_lexize('hunspell_long', 'booking');
 213    ts_lexize
 214 ----------------
 215  {booking,book}
 216 (1 row)
 217
 218 SELECT ts_lexize('hunspell_long', 'foot');
 219  ts_lexize
 220 -----------
 221  {foot}
 222 (1 row)
 223
 224 SELECT ts_lexize('hunspell_long', 'foots');
 225  ts_lexize
 226 -----------
 227  {foot}
 228 (1 row)
 229
 230 SELECT ts_lexize('hunspell_long', 'rebookings');
 231    ts_lexize
 232 ----------------
 233  {booking,book}
 234 (1 row)
 235
 236 SELECT ts_lexize('hunspell_long', 'rebooking');
 237    ts_lexize
 238 ----------------
 239  {booking,book}
 240 (1 row)
 241
 242 SELECT ts_lexize('hunspell_long', 'rebook');
 243  ts_lexize
 244 -----------
 245
 246 (1 row)
 247
 248 SELECT ts_lexize('hunspell_long', 'unbookings');
 249  ts_lexize
 250 -----------
 251  {book}
 252 (1 row)
 253
 254 SELECT ts_lexize('hunspell_long', 'unbooking');
 255  ts_lexize
 256 -----------
 257  {book}
 258 (1 row)
 259
 260 SELECT ts_lexize('hunspell_long', 'unbook');
 261  ts_lexize
 262 -----------
 263  {book}
 264 (1 row)
 265
 266 SELECT ts_lexize('hunspell_long', 'booked');
 267  ts_lexize
 268 -----------
 269  {book}
 270 (1 row)
 271
 272 SELECT ts_lexize('hunspell_long', 'footklubber');
 273    ts_lexize
 274 ----------------
 275  {foot,klubber}
 276 (1 row)
 277
 278 SELECT ts_lexize('hunspell_long', 'footballklubber');
 279                       ts_lexize
 280 ------------------------------------------------------
 281  {footballklubber,foot,ball,klubber,football,klubber}
 282 (1 row)
 283
 284 SELECT ts_lexize('hunspell_long', 'ballyklubber');
 285    ts_lexize
 286 ----------------
 287  {ball,klubber}
 288 (1 row)
 289
 290 SELECT ts_lexize('hunspell_long', 'ballsklubber');
 291    ts_lexize
 292 ----------------
 293  {ball,klubber}
 294 (1 row)
 295
 296 SELECT ts_lexize('hunspell_long', 'footballyklubber');
 297       ts_lexize
 298 ---------------------
 299  {foot,ball,klubber}
 300 (1 row)
 301
 302 SELECT 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
 309 CREATE TEXT SEARCH DICTIONARY hunspell_num (
 310                         Template=ispell,
 311                         DictFile=hunspell_sample_num,
 312                         AffFile=hunspell_sample_num
 313 );
 314 SELECT ts_lexize('hunspell_num', 'skies');
 315  ts_lexize
 316 -----------
 317  {sky}
 318 (1 row)
 319
 320 SELECT ts_lexize('hunspell_num', 'sk');
 321  ts_lexize
 322 -----------
 323  {sky}
 324 (1 row)
 325
 326 SELECT ts_lexize('hunspell_num', 'bookings');
 327    ts_lexize
 328 ----------------
 329  {booking,book}
 330 (1 row)
 331
 332 SELECT ts_lexize('hunspell_num', 'booking');
 333    ts_lexize
 334 ----------------
 335  {booking,book}
 336 (1 row)
 337
 338 SELECT ts_lexize('hunspell_num', 'foot');
 339  ts_lexize
 340 -----------
 341  {foot}
 342 (1 row)
 343
 344 SELECT ts_lexize('hunspell_num', 'foots');
 345  ts_lexize
 346 -----------
 347  {foot}
 348 (1 row)
 349
 350 SELECT ts_lexize('hunspell_num', 'rebookings');
 351    ts_lexize
 352 ----------------
 353  {booking,book}
 354 (1 row)
 355
 356 SELECT ts_lexize('hunspell_num', 'rebooking');
 357    ts_lexize
 358 ----------------
 359  {booking,book}
 360 (1 row)
 361
 362 SELECT ts_lexize('hunspell_num', 'rebook');
 363  ts_lexize
 364 -----------
 365
 366 (1 row)
 367
 368 SELECT ts_lexize('hunspell_num', 'unbookings');
 369  ts_lexize
 370 -----------
 371  {book}
 372 (1 row)
 373
 374 SELECT ts_lexize('hunspell_num', 'unbooking');
 375  ts_lexize
 376 -----------
 377  {book}
 378 (1 row)
 379
 380 SELECT ts_lexize('hunspell_num', 'unbook');
 381  ts_lexize
 382 -----------
 383  {book}
 384 (1 row)
 385
 386 SELECT ts_lexize('hunspell_num', 'booked');
 387  ts_lexize
 388 -----------
 389  {book}
 390 (1 row)
 391
 392 SELECT ts_lexize('hunspell_num', 'footklubber');
 393    ts_lexize
 394 ----------------
 395  {foot,klubber}
 396 (1 row)
 397
 398 SELECT ts_lexize('hunspell_num', 'footballklubber');
 399                       ts_lexize
 400 ------------------------------------------------------
 401  {footballklubber,foot,ball,klubber,football,klubber}
 402 (1 row)
 403
 404 SELECT ts_lexize('hunspell_num', 'ballyklubber');
 405    ts_lexize
 406 ----------------
 407  {ball,klubber}
 408 (1 row)
 409
 410 SELECT 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
 417 CREATE TEXT SEARCH DICTIONARY hunspell_err (
 418                                                 Template=ispell,
 419                                                 DictFile=ispell_sample,
 420                                                 AffFile=hunspell_sample_long
 421 );
 422 ERROR:  invalid affix alias "GJUS"
 423 CREATE TEXT SEARCH DICTIONARY hunspell_err (
 424                                                 Template=ispell,
 425                                                 DictFile=ispell_sample,
 426                                                 AffFile=hunspell_sample_num
 427 );
 428 ERROR:  invalid affix flag "SZ\"
 429 CREATE TEXT SEARCH DICTIONARY hunspell_invalid_1 (
 430                                                 Template=ispell,
 431                                                 DictFile=hunspell_sample_long,
 432                                                 AffFile=ispell_sample
 433 );
 434 CREATE TEXT SEARCH DICTIONARY hunspell_invalid_2 (
 435                                                 Template=ispell,
 436                                                 DictFile=hunspell_sample_long,
 437                                                 AffFile=hunspell_sample_num
 438 );
 439 CREATE TEXT SEARCH DICTIONARY hunspell_invalid_3 (
 440                                                 Template=ispell,
 441                                                 DictFile=hunspell_sample_num,
 442                                                 AffFile=ispell_sample
 443 );
 444 CREATE TEXT SEARCH DICTIONARY hunspell_err (
 445                                                 Template=ispell,
 446                                                 DictFile=hunspell_sample_num,
 447                                                 AffFile=hunspell_sample_long
 448 );
 449 ERROR:  invalid affix alias "302,301,202,303"
 450 -- Synonym dictionary
 451 CREATE TEXT SEARCH DICTIONARY synonym (
 452                                                 Template=synonym,
 453                                                 Synonyms=synonym_sample
 454 );
 455 SELECT ts_lexize('synonym', 'PoStGrEs');
 456  ts_lexize
 457 -----------
 458  {pgsql}
 459 (1 row)
 460
 461 SELECT ts_lexize('synonym', 'Gogle');
 462  ts_lexize
 463 -----------
 464  {googl}
 465 (1 row)
 466
 467 SELECT ts_lexize('synonym', 'indices');
 468  ts_lexize
 469 -----------
 470  {index}
 471 (1 row)
 472
 473 -- test altering boolean parameters
 474 SELECT dictinitoption FROM pg_ts_dict WHERE dictname = 'synonym';
 475        dictinitoption
 476 -----------------------------
 477  synonyms = 'synonym_sample'
 478 (1 row)
 479
 480 ALTER TEXT SEARCH DICTIONARY synonym (CaseSensitive = 1);
 481 SELECT ts_lexize('synonym', 'PoStGrEs');
 482  ts_lexize
 483 -----------
 484
 485 (1 row)
 486
 487 SELECT dictinitoption FROM pg_ts_dict WHERE dictname = 'synonym';
 488                  dictinitoption
 489 ------------------------------------------------
 490  synonyms = 'synonym_sample', casesensitive = 1
 491 (1 row)
 492
 493 ALTER TEXT SEARCH DICTIONARY synonym (CaseSensitive = 2);  -- fail
 494 ERROR:  casesensitive requires a Boolean value
 495 ALTER TEXT SEARCH DICTIONARY synonym (CaseSensitive = off);
 496 SELECT ts_lexize('synonym', 'PoStGrEs');
 497  ts_lexize
 498 -----------
 499  {pgsql}
 500 (1 row)
 501
 502 SELECT 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.
 511 CREATE TEXT SEARCH DICTIONARY thesaurus (
 512                         Template=thesaurus,
 513                                                 DictFile=thesaurus_sample,
 514                                                 Dictionary=english_stem
 515 );
 516 SELECT ts_lexize('thesaurus', 'one');
 517  ts_lexize
 518 -----------
 519  {1}
 520 (1 row)
 521
 522 -- Test ispell dictionary in configuration
 523 CREATE TEXT SEARCH CONFIGURATION ispell_tst (
 524                                                 COPY=english
 525 );
 526 ALTER 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;
 529 SELECT 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
 535 SELECT to_tsquery('ispell_tst', 'footballklubber');
 536                                 to_tsquery
 537 --------------------------------------------------------------------------
 538  'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber'
 539 (1 row)
 540
 541 SELECT 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
 548 CREATE TEXT SEARCH CONFIGURATION hunspell_tst (
 549                                                 COPY=ispell_tst
 550 );
 551 ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING
 552         REPLACE ispell WITH hunspell;
 553 SELECT 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
 559 SELECT to_tsquery('hunspell_tst', 'footballklubber');
 560                                 to_tsquery
 561 --------------------------------------------------------------------------
 562  'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber'
 563 (1 row)
 564
 565 SELECT 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
 571 SELECT to_tsquery('hunspell_tst', 'footballyklubber:b <-> sky');
 572                    to_tsquery
 573 -------------------------------------------------
 574  ( 'foot':B & 'ball':B & 'klubber':B ) <-> 'sky'
 575 (1 row)
 576
 577 SELECT 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
 584 ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING
 585         REPLACE hunspell WITH hunspell_long;
 586 SELECT 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
 592 SELECT to_tsquery('hunspell_tst', 'footballklubber');
 593                                 to_tsquery
 594 --------------------------------------------------------------------------
 595  'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber'
 596 (1 row)
 597
 598 SELECT 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
 605 ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING
 606         REPLACE hunspell_long WITH hunspell_num;
 607 SELECT 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
 613 SELECT to_tsquery('hunspell_tst', 'footballklubber');
 614                                 to_tsquery
 615 --------------------------------------------------------------------------
 616  'footballklubber' | 'foot' & 'ball' & 'klubber' | 'football' & 'klubber'
 617 (1 row)
 618
 619 SELECT 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
 626 CREATE TEXT SEARCH CONFIGURATION synonym_tst (
 627                                                 COPY=english
 628 );
 629 ALTER TEXT SEARCH CONFIGURATION synonym_tst ALTER MAPPING FOR
 630         asciiword, hword_asciipart, asciihword
 631         WITH synonym, english_stem;
 632 SELECT 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
 638 SELECT 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
 644 SELECT 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
 650 SELECT 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
 658 CREATE TEXT SEARCH CONFIGURATION thesaurus_tst (
 659                                                 COPY=synonym_tst
 660 );
 661 ALTER TEXT SEARCH CONFIGURATION thesaurus_tst ALTER MAPPING FOR
 662         asciiword, hword_asciipart, asciihword
 663         WITH synonym, thesaurus, english_stem;
 664 SELECT 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
 670 SELECT 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
 676 SELECT 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
 683 CREATE TEXT SEARCH DICTIONARY tsdict_case
 684 (
 685         Template = ispell,
 686         "DictFile" = ispell_sample,
 687         "AffFile" = ispell_sample
 688 );
 689 ERROR:  unrecognized Ispell parameter: "DictFile"