1-- 2-- Sanity checks for text search catalogs 3-- 4-- NB: we assume the oidjoins test will have caught any dangling links, 5-- that is OID or REGPROC fields that are not zero and do not match some 6-- row in the linked-to table. However, if we want to enforce that a link 7-- field can't be 0, we have to check it here. 8-- Find unexpected zero link entries 9SELECT oid, prsname 10FROM pg_ts_parser 11WHERE prsnamespace = 0 OR prsstart = 0 OR prstoken = 0 OR prsend = 0 OR 12 -- prsheadline is optional 13 prslextype = 0; 14 oid | prsname 15-----+--------- 16(0 rows) 17 18SELECT oid, dictname 19FROM pg_ts_dict 20WHERE dictnamespace = 0 OR dictowner = 0 OR dicttemplate = 0; 21 oid | dictname 22-----+---------- 23(0 rows) 24 25SELECT oid, tmplname 26FROM pg_ts_template 27WHERE tmplnamespace = 0 OR tmpllexize = 0; -- tmplinit is optional 28 oid | tmplname 29-----+---------- 30(0 rows) 31 32SELECT oid, cfgname 33FROM pg_ts_config 34WHERE cfgnamespace = 0 OR cfgowner = 0 OR cfgparser = 0; 35 oid | cfgname 36-----+--------- 37(0 rows) 38 39SELECT mapcfg, maptokentype, mapseqno 40FROM pg_ts_config_map 41WHERE mapcfg = 0 OR mapdict = 0; 42 mapcfg | maptokentype | mapseqno 43--------+--------------+---------- 44(0 rows) 45 46-- Look for pg_ts_config_map entries that aren't one of parser's token types 47SELECT * FROM 48 ( SELECT oid AS cfgid, (ts_token_type(cfgparser)).tokid AS tokid 49 FROM pg_ts_config ) AS tt 50RIGHT JOIN pg_ts_config_map AS m 51 ON (tt.cfgid=m.mapcfg AND tt.tokid=m.maptokentype) 52WHERE 53 tt.cfgid IS NULL OR tt.tokid IS NULL; 54 cfgid | tokid | mapcfg | maptokentype | mapseqno | mapdict 55-------+-------+--------+--------------+----------+--------- 56(0 rows) 57 58-- test basic text search behavior without indexes, then with 59SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 60 count 61------- 62 158 63(1 row) 64 65SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 66 count 67------- 68 17 69(1 row) 70 71SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 72 count 73------- 74 6 75(1 row) 76 77SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 78 count 79------- 80 98 81(1 row) 82 83SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 84 count 85------- 86 23 87(1 row) 88 89SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 90 count 91------- 92 39 93(1 row) 94 95SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 96 count 97------- 98 494 99(1 row) 100 101SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 102 count 103------- 104 158 105(1 row) 106 107SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 108 count 109------- 110 0 111(1 row) 112 113SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 114 count 115------- 116 508 117(1 row) 118 119SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 120 count 121------- 122 1 123(1 row) 124 125SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 126 count 127------- 128 0 129(1 row) 130 131SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 132 count 133------- 134 1 135(1 row) 136 137SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 138 count 139------- 140 3 141(1 row) 142 143SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 144 count 145------- 146 432 147(1 row) 148 149SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 150 count 151------- 152 1 153(1 row) 154 155SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 156 count 157------- 158 6 159(1 row) 160 161SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 162 count 163------- 164 507 165(1 row) 166 167SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 168 count 169------- 170 508 171(1 row) 172 173SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 174 count 175------- 176 507 177(1 row) 178 179create index wowidx on test_tsvector using gist (a); 180SET enable_seqscan=OFF; 181SET enable_indexscan=ON; 182SET enable_bitmapscan=OFF; 183explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 184 QUERY PLAN 185------------------------------------------------------- 186 Aggregate 187 -> Index Scan using wowidx on test_tsvector 188 Index Cond: (a @@ '''wr'' | ''qh'''::tsquery) 189(3 rows) 190 191SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 192 count 193------- 194 158 195(1 row) 196 197SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 198 count 199------- 200 17 201(1 row) 202 203SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 204 count 205------- 206 6 207(1 row) 208 209SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 210 count 211------- 212 98 213(1 row) 214 215SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 216 count 217------- 218 23 219(1 row) 220 221SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 222 count 223------- 224 39 225(1 row) 226 227SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 228 count 229------- 230 494 231(1 row) 232 233SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 234 count 235------- 236 158 237(1 row) 238 239SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 240 count 241------- 242 0 243(1 row) 244 245SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 246 count 247------- 248 508 249(1 row) 250 251SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 252 count 253------- 254 1 255(1 row) 256 257SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 258 count 259------- 260 0 261(1 row) 262 263SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 264 count 265------- 266 1 267(1 row) 268 269SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 270 count 271------- 272 3 273(1 row) 274 275SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 276 count 277------- 278 432 279(1 row) 280 281SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 282 count 283------- 284 1 285(1 row) 286 287SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 288 count 289------- 290 6 291(1 row) 292 293SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 294 count 295------- 296 507 297(1 row) 298 299SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 300 count 301------- 302 508 303(1 row) 304 305SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 306 count 307------- 308 507 309(1 row) 310 311SET enable_indexscan=OFF; 312SET enable_bitmapscan=ON; 313explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 314 QUERY PLAN 315------------------------------------------------------------- 316 Aggregate 317 -> Bitmap Heap Scan on test_tsvector 318 Recheck Cond: (a @@ '''wr'' | ''qh'''::tsquery) 319 -> Bitmap Index Scan on wowidx 320 Index Cond: (a @@ '''wr'' | ''qh'''::tsquery) 321(5 rows) 322 323SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 324 count 325------- 326 158 327(1 row) 328 329SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 330 count 331------- 332 17 333(1 row) 334 335SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 336 count 337------- 338 6 339(1 row) 340 341SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 342 count 343------- 344 98 345(1 row) 346 347SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 348 count 349------- 350 23 351(1 row) 352 353SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 354 count 355------- 356 39 357(1 row) 358 359SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 360 count 361------- 362 494 363(1 row) 364 365SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 366 count 367------- 368 158 369(1 row) 370 371SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 372 count 373------- 374 0 375(1 row) 376 377SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 378 count 379------- 380 508 381(1 row) 382 383SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 384 count 385------- 386 1 387(1 row) 388 389SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 390 count 391------- 392 0 393(1 row) 394 395SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 396 count 397------- 398 1 399(1 row) 400 401SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 402 count 403------- 404 3 405(1 row) 406 407SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 408 count 409------- 410 432 411(1 row) 412 413SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 414 count 415------- 416 1 417(1 row) 418 419SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 420 count 421------- 422 6 423(1 row) 424 425SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 426 count 427------- 428 507 429(1 row) 430 431SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 432 count 433------- 434 508 435(1 row) 436 437SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 438 count 439------- 440 507 441(1 row) 442 443RESET enable_seqscan; 444RESET enable_indexscan; 445RESET enable_bitmapscan; 446DROP INDEX wowidx; 447CREATE INDEX wowidx ON test_tsvector USING gin (a); 448SET enable_seqscan=OFF; 449-- GIN only supports bitmapscan, so no need to test plain indexscan 450explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 451 QUERY PLAN 452------------------------------------------------------------- 453 Aggregate 454 -> Bitmap Heap Scan on test_tsvector 455 Recheck Cond: (a @@ '''wr'' | ''qh'''::tsquery) 456 -> Bitmap Index Scan on wowidx 457 Index Cond: (a @@ '''wr'' | ''qh'''::tsquery) 458(5 rows) 459 460SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 461 count 462------- 463 158 464(1 row) 465 466SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 467 count 468------- 469 17 470(1 row) 471 472SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 473 count 474------- 475 6 476(1 row) 477 478SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 479 count 480------- 481 98 482(1 row) 483 484SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 485 count 486------- 487 23 488(1 row) 489 490SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 491 count 492------- 493 39 494(1 row) 495 496SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 497 count 498------- 499 494 500(1 row) 501 502SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 503 count 504------- 505 158 506(1 row) 507 508SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 509 count 510------- 511 0 512(1 row) 513 514SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 515 count 516------- 517 508 518(1 row) 519 520SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 521 count 522------- 523 1 524(1 row) 525 526SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 527 count 528------- 529 0 530(1 row) 531 532SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 533 count 534------- 535 1 536(1 row) 537 538SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 539 count 540------- 541 3 542(1 row) 543 544SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 545 count 546------- 547 432 548(1 row) 549 550SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 551 count 552------- 553 1 554(1 row) 555 556SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 557 count 558------- 559 6 560(1 row) 561 562SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 563 count 564------- 565 507 566(1 row) 567 568SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 569 count 570------- 571 508 572(1 row) 573 574SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 575 count 576------- 577 507 578(1 row) 579 580RESET enable_seqscan; 581INSERT INTO test_tsvector VALUES ('???', 'DFG:1A,2B,6C,10 FGH'); 582SELECT * FROM ts_stat('SELECT a FROM test_tsvector') ORDER BY ndoc DESC, nentry DESC, word LIMIT 10; 583 word | ndoc | nentry 584------+------+-------- 585 qq | 108 | 108 586 qt | 102 | 102 587 qe | 100 | 101 588 qh | 98 | 99 589 qw | 98 | 98 590 qa | 97 | 97 591 ql | 94 | 94 592 qs | 94 | 94 593 qr | 92 | 93 594 qi | 92 | 92 595(10 rows) 596 597SELECT * FROM ts_stat('SELECT a FROM test_tsvector', 'AB') ORDER BY ndoc DESC, nentry DESC, word; 598 word | ndoc | nentry 599------+------+-------- 600 DFG | 1 | 2 601(1 row) 602 603--dictionaries and to_tsvector 604SELECT ts_lexize('english_stem', 'skies'); 605 ts_lexize 606----------- 607 {sky} 608(1 row) 609 610SELECT ts_lexize('english_stem', 'identity'); 611 ts_lexize 612----------- 613 {ident} 614(1 row) 615 616SELECT * FROM ts_token_type('default'); 617 tokid | alias | description 618-------+-----------------+------------------------------------------ 619 1 | asciiword | Word, all ASCII 620 2 | word | Word, all letters 621 3 | numword | Word, letters and digits 622 4 | email | Email address 623 5 | url | URL 624 6 | host | Host 625 7 | sfloat | Scientific notation 626 8 | version | Version number 627 9 | hword_numpart | Hyphenated word part, letters and digits 628 10 | hword_part | Hyphenated word part, all letters 629 11 | hword_asciipart | Hyphenated word part, all ASCII 630 12 | blank | Space symbols 631 13 | tag | XML tag 632 14 | protocol | Protocol head 633 15 | numhword | Hyphenated word, letters and digits 634 16 | asciihword | Hyphenated word, all ASCII 635 17 | hword | Hyphenated word, all letters 636 18 | url_path | URL path 637 19 | file | File or path name 638 20 | float | Decimal notation 639 21 | int | Signed integer 640 22 | uint | Unsigned integer 641 23 | entity | XML entity 642(23 rows) 643 644SELECT * FROM ts_parse('default', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net teodor@123-stack.net 123_teodor@stack.net 123-teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 645/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234 646<i <b> wow < jqw <> qwerty'); 647 tokid | token 648-------+-------------------------------------- 649 22 | 345 650 12 | 651 1 | qwe 652 12 | @ 653 19 | efd.r 654 12 | ' 655 14 | http:// 656 6 | www.com 657 12 | / 658 14 | http:// 659 5 | aew.werc.ewr/?ad=qwe&dw 660 6 | aew.werc.ewr 661 18 | /?ad=qwe&dw 662 12 | 663 5 | 1aew.werc.ewr/?ad=qwe&dw 664 6 | 1aew.werc.ewr 665 18 | /?ad=qwe&dw 666 12 | 667 6 | 2aew.werc.ewr 668 12 | 669 14 | http:// 670 5 | 3aew.werc.ewr/?ad=qwe&dw 671 6 | 3aew.werc.ewr 672 18 | /?ad=qwe&dw 673 12 | 674 14 | http:// 675 6 | 4aew.werc.ewr 676 12 | 677 14 | http:// 678 5 | 5aew.werc.ewr:8100/? 679 6 | 5aew.werc.ewr:8100 680 18 | /? 681 12 | 682 1 | ad 683 12 | = 684 1 | qwe 685 12 | & 686 1 | dw 687 12 | 688 5 | 6aew.werc.ewr:8100/?ad=qwe&dw 689 6 | 6aew.werc.ewr:8100 690 18 | /?ad=qwe&dw 691 12 | 692 5 | 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 693 6 | 7aew.werc.ewr:8100 694 18 | /?ad=qwe&dw=%20%32 695 12 | 696 7 | +4.0e-10 697 12 | 698 1 | qwe 699 12 | 700 1 | qwe 701 12 | 702 1 | qwqwe 703 12 | 704 20 | 234.435 705 12 | 706 22 | 455 707 12 | 708 20 | 5.005 709 12 | 710 4 | teodor@stack.net 711 12 | 712 4 | teodor@123-stack.net 713 12 | 714 4 | 123_teodor@stack.net 715 12 | 716 4 | 123-teodor@stack.net 717 12 | 718 16 | qwe-wer 719 11 | qwe 720 12 | - 721 11 | wer 722 12 | 723 1 | asdf 724 12 | 725 13 | <fr> 726 1 | qwer 727 12 | 728 1 | jf 729 12 | 730 1 | sdjk 731 12 | < 732 1 | we 733 12 | 734 1 | hjwer 735 12 | 736 13 | <werrwe> 737 12 | 738 3 | ewr1 739 12 | > 740 3 | ewri2 741 12 | 742 13 | <a href="qwe<qwe>"> 743 12 | + 744 | 745 19 | /usr/local/fff 746 12 | 747 19 | /awdf/dwqe/4325 748 12 | 749 19 | rewt/ewr 750 12 | 751 1 | wefjn 752 12 | 753 19 | /wqe-324/ewr 754 12 | 755 19 | gist.h 756 12 | 757 19 | gist.h.c 758 12 | 759 19 | gist.c 760 12 | . 761 1 | readline 762 12 | 763 20 | 4.2 764 12 | 765 20 | 4.2 766 12 | . 767 20 | 4.2 768 12 | , 769 1 | readline 770 20 | -4.2 771 12 | 772 1 | readline 773 20 | -4.2 774 12 | . 775 22 | 234 776 12 | + 777 | 778 12 | < 779 1 | i 780 12 | 781 13 | <b> 782 12 | 783 1 | wow 784 12 | 785 12 | < 786 1 | jqw 787 12 | 788 12 | <> 789 1 | qwerty 790(139 rows) 791 792SELECT to_tsvector('english', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net teodor@123-stack.net 123_teodor@stack.net 123-teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 793/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234 794<i <b> wow < jqw <> qwerty'); 795 to_tsvector 796------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 797 '+4.0e-10':28 '-4.2':63,65 '/?':18 '/?ad=qwe&dw':7,10,14,24 '/?ad=qwe&dw=%20%32':27 '/awdf/dwqe/4325':51 '/usr/local/fff':50 '/wqe-324/ewr':54 '123-teodor@stack.net':38 '123_teodor@stack.net':37 '1aew.werc.ewr':9 '1aew.werc.ewr/?ad=qwe&dw':8 '234':66 '234.435':32 '2aew.werc.ewr':11 '345':1 '3aew.werc.ewr':13 '3aew.werc.ewr/?ad=qwe&dw':12 '4.2':59,60,61 '455':33 '4aew.werc.ewr':15 '5.005':34 '5aew.werc.ewr:8100':17 '5aew.werc.ewr:8100/?':16 '6aew.werc.ewr:8100':23 '6aew.werc.ewr:8100/?ad=qwe&dw':22 '7aew.werc.ewr:8100':26 '7aew.werc.ewr:8100/?ad=qwe&dw=%20%32':25 'ad':19 'aew.werc.ewr':6 'aew.werc.ewr/?ad=qwe&dw':5 'asdf':42 'dw':21 'efd.r':3 'ewr1':48 'ewri2':49 'gist.c':57 'gist.h':55 'gist.h.c':56 'hjwer':47 'jf':44 'jqw':69 'qwe':2,20,29,30,40 'qwe-wer':39 'qwer':43 'qwerti':70 'qwqwe':31 'readlin':58,62,64 'rewt/ewr':52 'sdjk':45 'teodor@123-stack.net':36 'teodor@stack.net':35 'wefjn':53 'wer':41 'wow':68 'www.com':4 798(1 row) 799 800SELECT length(to_tsvector('english', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net teodor@123-stack.net 123_teodor@stack.net 123-teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 801/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234 802<i <b> wow < jqw <> qwerty')); 803 length 804-------- 805 56 806(1 row) 807 808-- ts_debug 809SELECT * from ts_debug('english', '<myns:foo-bar_baz.blurfl>abc&nm1;def©ghiõjkl</myns:foo-bar_baz.blurfl>'); 810 alias | description | token | dictionaries | dictionary | lexemes 811-----------+-----------------+----------------------------+----------------+--------------+--------- 812 tag | XML tag | <myns:foo-bar_baz.blurfl> | {} | | 813 asciiword | Word, all ASCII | abc | {english_stem} | english_stem | {abc} 814 entity | XML entity | &nm1; | {} | | 815 asciiword | Word, all ASCII | def | {english_stem} | english_stem | {def} 816 entity | XML entity | © | {} | | 817 asciiword | Word, all ASCII | ghi | {english_stem} | english_stem | {ghi} 818 entity | XML entity | õ | {} | | 819 asciiword | Word, all ASCII | jkl | {english_stem} | english_stem | {jkl} 820 tag | XML tag | </myns:foo-bar_baz.blurfl> | {} | | 821(9 rows) 822 823-- check parsing of URLs 824SELECT * from ts_debug('english', 'http://www.harewoodsolutions.co.uk/press.aspx</span>'); 825 alias | description | token | dictionaries | dictionary | lexemes 826----------+---------------+----------------------------------------+--------------+------------+------------------------------------------ 827 protocol | Protocol head | http:// | {} | | 828 url | URL | www.harewoodsolutions.co.uk/press.aspx | {simple} | simple | {www.harewoodsolutions.co.uk/press.aspx} 829 host | Host | www.harewoodsolutions.co.uk | {simple} | simple | {www.harewoodsolutions.co.uk} 830 url_path | URL path | /press.aspx | {simple} | simple | {/press.aspx} 831 tag | XML tag | </span> | {} | | 832(5 rows) 833 834SELECT * from ts_debug('english', 'http://aew.wer0c.ewr/id?ad=qwe&dw<span>'); 835 alias | description | token | dictionaries | dictionary | lexemes 836----------+---------------+----------------------------+--------------+------------+------------------------------ 837 protocol | Protocol head | http:// | {} | | 838 url | URL | aew.wer0c.ewr/id?ad=qwe&dw | {simple} | simple | {aew.wer0c.ewr/id?ad=qwe&dw} 839 host | Host | aew.wer0c.ewr | {simple} | simple | {aew.wer0c.ewr} 840 url_path | URL path | /id?ad=qwe&dw | {simple} | simple | {/id?ad=qwe&dw} 841 tag | XML tag | <span> | {} | | 842(5 rows) 843 844SELECT * from ts_debug('english', 'http://5aew.werc.ewr:8100/?'); 845 alias | description | token | dictionaries | dictionary | lexemes 846----------+---------------+----------------------+--------------+------------+------------------------ 847 protocol | Protocol head | http:// | {} | | 848 url | URL | 5aew.werc.ewr:8100/? | {simple} | simple | {5aew.werc.ewr:8100/?} 849 host | Host | 5aew.werc.ewr:8100 | {simple} | simple | {5aew.werc.ewr:8100} 850 url_path | URL path | /? | {simple} | simple | {/?} 851(4 rows) 852 853SELECT * from ts_debug('english', '5aew.werc.ewr:8100/?xx'); 854 alias | description | token | dictionaries | dictionary | lexemes 855----------+-------------+------------------------+--------------+------------+-------------------------- 856 url | URL | 5aew.werc.ewr:8100/?xx | {simple} | simple | {5aew.werc.ewr:8100/?xx} 857 host | Host | 5aew.werc.ewr:8100 | {simple} | simple | {5aew.werc.ewr:8100} 858 url_path | URL path | /?xx | {simple} | simple | {/?xx} 859(3 rows) 860 861SELECT token, alias, 862 dictionaries, dictionaries is null as dnull, array_dims(dictionaries) as ddims, 863 lexemes, lexemes is null as lnull, array_dims(lexemes) as ldims 864from ts_debug('english', 'a title'); 865 token | alias | dictionaries | dnull | ddims | lexemes | lnull | ldims 866-------+-----------+----------------+-------+-------+---------+-------+------- 867 a | asciiword | {english_stem} | f | [1:1] | {} | f | 868 | blank | {} | f | | | t | 869 title | asciiword | {english_stem} | f | [1:1] | {titl} | f | [1:1] 870(3 rows) 871 872-- to_tsquery 873SELECT to_tsquery('english', 'qwe & sKies '); 874 to_tsquery 875--------------- 876 'qwe' & 'sky' 877(1 row) 878 879SELECT to_tsquery('simple', 'qwe & sKies '); 880 to_tsquery 881----------------- 882 'qwe' & 'skies' 883(1 row) 884 885SELECT to_tsquery('english', '''the wether'':dc & '' sKies '':BC '); 886 to_tsquery 887------------------------ 888 'wether':CD & 'sky':BC 889(1 row) 890 891SELECT to_tsquery('english', 'asd&(and|fghj)'); 892 to_tsquery 893---------------- 894 'asd' & 'fghj' 895(1 row) 896 897SELECT to_tsquery('english', '(asd&and)|fghj'); 898 to_tsquery 899---------------- 900 'asd' | 'fghj' 901(1 row) 902 903SELECT to_tsquery('english', '(asd&!and)|fghj'); 904 to_tsquery 905---------------- 906 'asd' | 'fghj' 907(1 row) 908 909SELECT to_tsquery('english', '(the|and&(i&1))&fghj'); 910 to_tsquery 911-------------- 912 '1' & 'fghj' 913(1 row) 914 915SELECT plainto_tsquery('english', 'the and z 1))& fghj'); 916 plainto_tsquery 917-------------------- 918 'z' & '1' & 'fghj' 919(1 row) 920 921SELECT plainto_tsquery('english', 'foo bar') && plainto_tsquery('english', 'asd'); 922 ?column? 923----------------------- 924 'foo' & 'bar' & 'asd' 925(1 row) 926 927SELECT plainto_tsquery('english', 'foo bar') || plainto_tsquery('english', 'asd fg'); 928 ?column? 929------------------------------ 930 'foo' & 'bar' | 'asd' & 'fg' 931(1 row) 932 933SELECT plainto_tsquery('english', 'foo bar') || !!plainto_tsquery('english', 'asd fg'); 934 ?column? 935----------------------------------- 936 'foo' & 'bar' | !( 'asd' & 'fg' ) 937(1 row) 938 939SELECT plainto_tsquery('english', 'foo bar') && 'asd | fg'; 940 ?column? 941---------------------------------- 942 'foo' & 'bar' & ( 'asd' | 'fg' ) 943(1 row) 944 945-- Check stop word deletion, a and s are stop-words 946SELECT to_tsquery('english', '!(a & !b) & c'); 947 to_tsquery 948------------- 949 !!'b' & 'c' 950(1 row) 951 952SELECT to_tsquery('english', '!(a & !b)'); 953 to_tsquery 954------------ 955 !!'b' 956(1 row) 957 958SELECT to_tsquery('english', '(1 <-> 2) <-> a'); 959 to_tsquery 960------------- 961 '1' <-> '2' 962(1 row) 963 964SELECT to_tsquery('english', '(1 <-> a) <-> 2'); 965 to_tsquery 966------------- 967 '1' <2> '2' 968(1 row) 969 970SELECT to_tsquery('english', '(a <-> 1) <-> 2'); 971 to_tsquery 972------------- 973 '1' <-> '2' 974(1 row) 975 976SELECT to_tsquery('english', 'a <-> (1 <-> 2)'); 977 to_tsquery 978------------- 979 '1' <-> '2' 980(1 row) 981 982SELECT to_tsquery('english', '1 <-> (a <-> 2)'); 983 to_tsquery 984------------- 985 '1' <2> '2' 986(1 row) 987 988SELECT to_tsquery('english', '1 <-> (2 <-> a)'); 989 to_tsquery 990------------- 991 '1' <-> '2' 992(1 row) 993 994SELECT to_tsquery('english', '(1 <-> 2) <3> a'); 995 to_tsquery 996------------- 997 '1' <-> '2' 998(1 row) 999 1000SELECT to_tsquery('english', '(1 <-> a) <3> 2'); 1001 to_tsquery 1002------------- 1003 '1' <4> '2' 1004(1 row) 1005 1006SELECT to_tsquery('english', '(a <-> 1) <3> 2'); 1007 to_tsquery 1008------------- 1009 '1' <3> '2' 1010(1 row) 1011 1012SELECT to_tsquery('english', 'a <3> (1 <-> 2)'); 1013 to_tsquery 1014------------- 1015 '1' <-> '2' 1016(1 row) 1017 1018SELECT to_tsquery('english', '1 <3> (a <-> 2)'); 1019 to_tsquery 1020------------- 1021 '1' <4> '2' 1022(1 row) 1023 1024SELECT to_tsquery('english', '1 <3> (2 <-> a)'); 1025 to_tsquery 1026------------- 1027 '1' <3> '2' 1028(1 row) 1029 1030SELECT to_tsquery('english', '(1 <3> 2) <-> a'); 1031 to_tsquery 1032------------- 1033 '1' <3> '2' 1034(1 row) 1035 1036SELECT to_tsquery('english', '(1 <3> a) <-> 2'); 1037 to_tsquery 1038------------- 1039 '1' <4> '2' 1040(1 row) 1041 1042SELECT to_tsquery('english', '(a <3> 1) <-> 2'); 1043 to_tsquery 1044------------- 1045 '1' <-> '2' 1046(1 row) 1047 1048SELECT to_tsquery('english', 'a <-> (1 <3> 2)'); 1049 to_tsquery 1050------------- 1051 '1' <3> '2' 1052(1 row) 1053 1054SELECT to_tsquery('english', '1 <-> (a <3> 2)'); 1055 to_tsquery 1056------------- 1057 '1' <4> '2' 1058(1 row) 1059 1060SELECT to_tsquery('english', '1 <-> (2 <3> a)'); 1061 to_tsquery 1062------------- 1063 '1' <-> '2' 1064(1 row) 1065 1066SELECT to_tsquery('english', '((a <-> 1) <-> 2) <-> s'); 1067 to_tsquery 1068------------- 1069 '1' <-> '2' 1070(1 row) 1071 1072SELECT to_tsquery('english', '(2 <-> (a <-> 1)) <-> s'); 1073 to_tsquery 1074------------- 1075 '2' <2> '1' 1076(1 row) 1077 1078SELECT to_tsquery('english', '((1 <-> a) <-> 2) <-> s'); 1079 to_tsquery 1080------------- 1081 '1' <2> '2' 1082(1 row) 1083 1084SELECT to_tsquery('english', '(2 <-> (1 <-> a)) <-> s'); 1085 to_tsquery 1086------------- 1087 '2' <-> '1' 1088(1 row) 1089 1090SELECT to_tsquery('english', 's <-> ((a <-> 1) <-> 2)'); 1091 to_tsquery 1092------------- 1093 '1' <-> '2' 1094(1 row) 1095 1096SELECT to_tsquery('english', 's <-> (2 <-> (a <-> 1))'); 1097 to_tsquery 1098------------- 1099 '2' <2> '1' 1100(1 row) 1101 1102SELECT to_tsquery('english', 's <-> ((1 <-> a) <-> 2)'); 1103 to_tsquery 1104------------- 1105 '1' <2> '2' 1106(1 row) 1107 1108SELECT to_tsquery('english', 's <-> (2 <-> (1 <-> a))'); 1109 to_tsquery 1110------------- 1111 '2' <-> '1' 1112(1 row) 1113 1114SELECT to_tsquery('english', '((a <-> 1) <-> s) <-> 2'); 1115 to_tsquery 1116------------- 1117 '1' <2> '2' 1118(1 row) 1119 1120SELECT to_tsquery('english', '(s <-> (a <-> 1)) <-> 2'); 1121 to_tsquery 1122------------- 1123 '1' <-> '2' 1124(1 row) 1125 1126SELECT to_tsquery('english', '((1 <-> a) <-> s) <-> 2'); 1127 to_tsquery 1128------------- 1129 '1' <3> '2' 1130(1 row) 1131 1132SELECT to_tsquery('english', '(s <-> (1 <-> a)) <-> 2'); 1133 to_tsquery 1134------------- 1135 '1' <2> '2' 1136(1 row) 1137 1138SELECT to_tsquery('english', '2 <-> ((a <-> 1) <-> s)'); 1139 to_tsquery 1140------------- 1141 '2' <2> '1' 1142(1 row) 1143 1144SELECT to_tsquery('english', '2 <-> (s <-> (a <-> 1))'); 1145 to_tsquery 1146------------- 1147 '2' <3> '1' 1148(1 row) 1149 1150SELECT to_tsquery('english', '2 <-> ((1 <-> a) <-> s)'); 1151 to_tsquery 1152------------- 1153 '2' <-> '1' 1154(1 row) 1155 1156SELECT to_tsquery('english', '2 <-> (s <-> (1 <-> a))'); 1157 to_tsquery 1158------------- 1159 '2' <2> '1' 1160(1 row) 1161 1162SELECT to_tsquery('english', 'foo <-> (a <-> (the <-> bar))'); 1163 to_tsquery 1164----------------- 1165 'foo' <3> 'bar' 1166(1 row) 1167 1168SELECT to_tsquery('english', '((foo <-> a) <-> the) <-> bar'); 1169 to_tsquery 1170----------------- 1171 'foo' <3> 'bar' 1172(1 row) 1173 1174SELECT to_tsquery('english', 'foo <-> a <-> the <-> bar'); 1175 to_tsquery 1176----------------- 1177 'foo' <3> 'bar' 1178(1 row) 1179 1180SELECT phraseto_tsquery('english', 'PostgreSQL can be extended by the user in many ways'); 1181 phraseto_tsquery 1182----------------------------------------------------------- 1183 'postgresql' <3> 'extend' <3> 'user' <2> 'mani' <-> 'way' 1184(1 row) 1185 1186SELECT ts_rank_cd(to_tsvector('english', ' 1187Day after day, day after day, 1188 We stuck, nor breath nor motion, 1189As idle as a painted Ship 1190 Upon a painted Ocean. 1191Water, water, every where 1192 And all the boards did shrink; 1193Water, water, every where, 1194 Nor any drop to drink. 1195S. T. Coleridge (1772-1834) 1196'), to_tsquery('english', 'paint&water')); 1197 ts_rank_cd 1198------------ 1199 0.05 1200(1 row) 1201 1202SELECT ts_rank_cd(to_tsvector('english', ' 1203Day after day, day after day, 1204 We stuck, nor breath nor motion, 1205As idle as a painted Ship 1206 Upon a painted Ocean. 1207Water, water, every where 1208 And all the boards did shrink; 1209Water, water, every where, 1210 Nor any drop to drink. 1211S. T. Coleridge (1772-1834) 1212'), to_tsquery('english', 'breath&motion&water')); 1213 ts_rank_cd 1214------------- 1215 0.008333334 1216(1 row) 1217 1218SELECT ts_rank_cd(to_tsvector('english', ' 1219Day after day, day after day, 1220 We stuck, nor breath nor motion, 1221As idle as a painted Ship 1222 Upon a painted Ocean. 1223Water, water, every where 1224 And all the boards did shrink; 1225Water, water, every where, 1226 Nor any drop to drink. 1227S. T. Coleridge (1772-1834) 1228'), to_tsquery('english', 'ocean')); 1229 ts_rank_cd 1230------------ 1231 0.1 1232(1 row) 1233 1234SELECT ts_rank_cd(to_tsvector('english', ' 1235Day after day, day after day, 1236 We stuck, nor breath nor motion, 1237As idle as a painted Ship 1238 Upon a painted Ocean. 1239Water, water, every where 1240 And all the boards did shrink; 1241Water, water, every where, 1242 Nor any drop to drink. 1243S. T. Coleridge (1772-1834) 1244'), to_tsquery('english', 'painted <-> Ship')); 1245 ts_rank_cd 1246------------ 1247 0.1 1248(1 row) 1249 1250SELECT ts_rank_cd(strip(to_tsvector('both stripped')), 1251 to_tsquery('both & stripped')); 1252 ts_rank_cd 1253------------ 1254 0 1255(1 row) 1256 1257SELECT ts_rank_cd(to_tsvector('unstripped') || strip(to_tsvector('stripped')), 1258 to_tsquery('unstripped & stripped')); 1259 ts_rank_cd 1260------------ 1261 0 1262(1 row) 1263 1264--headline tests 1265SELECT ts_headline('english', ' 1266Day after day, day after day, 1267 We stuck, nor breath nor motion, 1268As idle as a painted Ship 1269 Upon a painted Ocean. 1270Water, water, every where 1271 And all the boards did shrink; 1272Water, water, every where, 1273 Nor any drop to drink. 1274S. T. Coleridge (1772-1834) 1275', to_tsquery('english', 'paint&water')); 1276 ts_headline 1277----------------------------------------- 1278 <b>painted</b> Ocean. + 1279 <b>Water</b>, <b>water</b>, every where+ 1280 And all the boards did shrink; + 1281 <b>Water</b>, <b>water</b>, every 1282(1 row) 1283 1284SELECT ts_headline('english', ' 1285Day after day, day after day, 1286 We stuck, nor breath nor motion, 1287As idle as a painted Ship 1288 Upon a painted Ocean. 1289Water, water, every where 1290 And all the boards did shrink; 1291Water, water, every where, 1292 Nor any drop to drink. 1293S. T. Coleridge (1772-1834) 1294', to_tsquery('english', 'breath&motion&water')); 1295 ts_headline 1296---------------------------------- 1297 <b>breath</b> nor <b>motion</b>,+ 1298 As idle as a painted Ship + 1299 Upon a painted Ocean. + 1300 <b>Water</b>, <b>water</b> 1301(1 row) 1302 1303SELECT ts_headline('english', ' 1304Day after day, day after day, 1305 We stuck, nor breath nor motion, 1306As idle as a painted Ship 1307 Upon a painted Ocean. 1308Water, water, every where 1309 And all the boards did shrink; 1310Water, water, every where, 1311 Nor any drop to drink. 1312S. T. Coleridge (1772-1834) 1313', to_tsquery('english', 'ocean')); 1314 ts_headline 1315---------------------------------- 1316 <b>Ocean</b>. + 1317 Water, water, every where + 1318 And all the boards did shrink;+ 1319 Water, water, every where 1320(1 row) 1321 1322SELECT ts_headline('english', ' 1323Day after day, day after day, 1324 We stuck, nor breath nor motion, 1325As idle as a painted Ship 1326 Upon a painted Ocean. 1327Water, water, every where 1328 And all the boards did shrink; 1329Water, water, every where, 1330 Nor any drop to drink. 1331S. T. Coleridge (1772-1834) 1332', phraseto_tsquery('english', 'painted Ocean')); 1333 ts_headline 1334--------------------------------------- 1335 <b>painted</b> Ship + 1336 Upon a <b>painted</b> <b>Ocean</b>.+ 1337 Water, water, every where + 1338 And all the boards did shrink 1339(1 row) 1340 1341SELECT ts_headline('english', ' 1342Day after day, day after day, 1343 We stuck, nor breath nor motion, 1344As idle as a painted Ship 1345 Upon a painted Ocean. 1346Water, water, every where 1347 And all the boards did shrink; 1348Water, water, every where, 1349 Nor any drop to drink. 1350S. T. Coleridge (1772-1834) 1351', phraseto_tsquery('english', 'idle as a painted Ship')); 1352 ts_headline 1353--------------------------------------------- 1354 <b>idle</b> as a <b>painted</b> <b>Ship</b>+ 1355 Upon a <b>painted</b> Ocean. + 1356 Water, water, every where + 1357 And all the boards 1358(1 row) 1359 1360SELECT ts_headline('english', 1361'Lorem ipsum urna. Nullam nullam ullamcorper urna.', 1362to_tsquery('english','Lorem') && phraseto_tsquery('english','ullamcorper urna'), 1363'MaxWords=100, MinWords=1'); 1364 ts_headline 1365------------------------------------------------------------------------------- 1366 <b>Lorem</b> ipsum <b>urna</b>. Nullam nullam <b>ullamcorper</b> <b>urna</b> 1367(1 row) 1368 1369SELECT ts_headline('english', ' 1370<html> 1371<!-- some comment --> 1372<body> 1373Sea view wow <u>foo bar</u> <i>qq</i> 1374<a href="http://www.google.com/foo.bar.html" target="_blank">YES </a> 1375ff-bg 1376<script> 1377 document.write(15); 1378</script> 1379</body> 1380</html>', 1381to_tsquery('english', 'sea&foo'), 'HighlightAll=true'); 1382 ts_headline 1383----------------------------------------------------------------------------- 1384 + 1385 <html> + 1386 <!-- some comment --> + 1387 <body> + 1388 <b>Sea</b> view wow <u><b>foo</b> bar</u> <i>qq</i> + 1389 <a href="http://www.google.com/foo.bar.html" target="_blank">YES </a>+ 1390 ff-bg + 1391 <script> + 1392 document.write(15); + 1393 </script> + 1394 </body> + 1395 </html> 1396(1 row) 1397 1398SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 <-> 3', 'MaxWords=2, MinWords=1'); 1399 ts_headline 1400------------------- 1401 <b>1</b> <b>3</b> 1402(1 row) 1403 1404SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 & 3', 'MaxWords=4, MinWords=1'); 1405 ts_headline 1406--------------------- 1407 <b>1</b> 2 <b>3</b> 1408(1 row) 1409 1410SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 <-> 3', 'MaxWords=4, MinWords=1'); 1411 ts_headline 1412---------------------------- 1413 <b>3</b> <b>1</b> <b>3</b> 1414(1 row) 1415 1416--Check if headline fragments work 1417SELECT ts_headline('english', ' 1418Day after day, day after day, 1419 We stuck, nor breath nor motion, 1420As idle as a painted Ship 1421 Upon a painted Ocean. 1422Water, water, every where 1423 And all the boards did shrink; 1424Water, water, every where, 1425 Nor any drop to drink. 1426S. T. Coleridge (1772-1834) 1427', to_tsquery('english', 'ocean'), 'MaxFragments=1'); 1428 ts_headline 1429------------------------------------ 1430 after day, + 1431 We stuck, nor breath nor motion,+ 1432 As idle as a painted Ship + 1433 Upon a painted <b>Ocean</b>. + 1434 Water, water, every where + 1435 And all the boards did shrink; + 1436 Water, water, every where, + 1437 Nor any drop 1438(1 row) 1439 1440--Check if more than one fragments are displayed 1441SELECT ts_headline('english', ' 1442Day after day, day after day, 1443 We stuck, nor breath nor motion, 1444As idle as a painted Ship 1445 Upon a painted Ocean. 1446Water, water, every where 1447 And all the boards did shrink; 1448Water, water, every where, 1449 Nor any drop to drink. 1450S. T. Coleridge (1772-1834) 1451', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2'); 1452 ts_headline 1453---------------------------------------------- 1454 after day, day after day, + 1455 We <b>stuck</b>, nor breath nor motion, + 1456 As idle as a painted Ship + 1457 Upon a painted Ocean. + 1458 Water, water, every where + 1459 And all the boards did shrink; + 1460 Water, water, every where ... drop to drink.+ 1461 S. T. <b>Coleridge</b> 1462(1 row) 1463 1464--Fragments when there all query words are not in the document 1465SELECT ts_headline('english', ' 1466Day after day, day after day, 1467 We stuck, nor breath nor motion, 1468As idle as a painted Ship 1469 Upon a painted Ocean. 1470Water, water, every where 1471 And all the boards did shrink; 1472Water, water, every where, 1473 Nor any drop to drink. 1474S. T. Coleridge (1772-1834) 1475', to_tsquery('english', 'ocean & seahorse'), 'MaxFragments=1'); 1476 ts_headline 1477------------------------------------ 1478 + 1479 Day after day, day after day, + 1480 We stuck, nor breath nor motion,+ 1481 As idle as 1482(1 row) 1483 1484--FragmentDelimiter option 1485SELECT ts_headline('english', ' 1486Day after day, day after day, 1487 We stuck, nor breath nor motion, 1488As idle as a painted Ship 1489 Upon a painted Ocean. 1490Water, water, every where 1491 And all the boards did shrink; 1492Water, water, every where, 1493 Nor any drop to drink. 1494S. T. Coleridge (1772-1834) 1495', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2,FragmentDelimiter=***'); 1496 ts_headline 1497-------------------------------------------- 1498 after day, day after day, + 1499 We <b>stuck</b>, nor breath nor motion, + 1500 As idle as a painted Ship + 1501 Upon a painted Ocean. + 1502 Water, water, every where + 1503 And all the boards did shrink; + 1504 Water, water, every where***drop to drink.+ 1505 S. T. <b>Coleridge</b> 1506(1 row) 1507 1508--Fragments with phrase search 1509SELECT ts_headline('english', 1510'Lorem ipsum urna. Nullam nullam ullamcorper urna.', 1511to_tsquery('english','Lorem') && phraseto_tsquery('english','ullamcorper urna'), 1512'MaxFragments=100, MaxWords=100, MinWords=1'); 1513 ts_headline 1514------------------------------------------------------------------------------- 1515 <b>Lorem</b> ipsum <b>urna</b>. Nullam nullam <b>ullamcorper</b> <b>urna</b> 1516(1 row) 1517 1518--Rewrite sub system 1519CREATE TABLE test_tsquery (txtkeyword TEXT, txtsample TEXT); 1520\set ECHO none 1521ALTER TABLE test_tsquery ADD COLUMN keyword tsquery; 1522UPDATE test_tsquery SET keyword = to_tsquery('english', txtkeyword); 1523ALTER TABLE test_tsquery ADD COLUMN sample tsquery; 1524UPDATE test_tsquery SET sample = to_tsquery('english', txtsample::text); 1525SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york'; 1526 count 1527------- 1528 2 1529(1 row) 1530 1531SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york'; 1532 count 1533------- 1534 3 1535(1 row) 1536 1537SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york'; 1538 count 1539------- 1540 1 1541(1 row) 1542 1543SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york'; 1544 count 1545------- 1546 4 1547(1 row) 1548 1549SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york'; 1550 count 1551------- 1552 3 1553(1 row) 1554 1555CREATE UNIQUE INDEX bt_tsq ON test_tsquery (keyword); 1556SET enable_seqscan=OFF; 1557SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york'; 1558 count 1559------- 1560 2 1561(1 row) 1562 1563SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york'; 1564 count 1565------- 1566 3 1567(1 row) 1568 1569SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york'; 1570 count 1571------- 1572 1 1573(1 row) 1574 1575SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york'; 1576 count 1577------- 1578 4 1579(1 row) 1580 1581SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york'; 1582 count 1583------- 1584 3 1585(1 row) 1586 1587RESET enable_seqscan; 1588SELECT ts_rewrite('foo & bar & qq & new & york', 'new & york'::tsquery, 'big & apple | nyc | new & york & city'); 1589 ts_rewrite 1590------------------------------------------------------------------------------ 1591 'foo' & 'bar' & 'qq' & ( 'city' & 'new' & 'york' | 'nyc' | 'big' & 'apple' ) 1592(1 row) 1593 1594SELECT ts_rewrite(ts_rewrite('new & !york ', 'york', '!jersey'), 1595 'jersey', 'mexico'); 1596 ts_rewrite 1597-------------------- 1598 'new' & !!'mexico' 1599(1 row) 1600 1601SELECT ts_rewrite('moscow', 'SELECT keyword, sample FROM test_tsquery'::text ); 1602 ts_rewrite 1603--------------------- 1604 'moskva' | 'moscow' 1605(1 row) 1606 1607SELECT ts_rewrite('moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'::text ); 1608 ts_rewrite 1609----------------------------------- 1610 'hotel' & ( 'moskva' | 'moscow' ) 1611(1 row) 1612 1613SELECT ts_rewrite('bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery'::text ); 1614 ts_rewrite 1615--------------------------------------------------------------------------------- 1616 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' & 'appl' | 'new' & 'york' ) 1617(1 row) 1618 1619SELECT ts_rewrite( 'moscow', 'SELECT keyword, sample FROM test_tsquery'); 1620 ts_rewrite 1621--------------------- 1622 'moskva' | 'moscow' 1623(1 row) 1624 1625SELECT ts_rewrite( 'moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'); 1626 ts_rewrite 1627----------------------------------- 1628 'hotel' & ( 'moskva' | 'moscow' ) 1629(1 row) 1630 1631SELECT ts_rewrite( 'bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery'); 1632 ts_rewrite 1633--------------------------------------------------------------------------------- 1634 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' & 'appl' | 'new' & 'york' ) 1635(1 row) 1636 1637SELECT ts_rewrite('1 & (2 <-> 3)', 'SELECT keyword, sample FROM test_tsquery'::text ); 1638 ts_rewrite 1639------------- 1640 '2' <-> '4' 1641(1 row) 1642 1643SELECT ts_rewrite('1 & (2 <2> 3)', 'SELECT keyword, sample FROM test_tsquery'::text ); 1644 ts_rewrite 1645------------------- 1646 '1' & '2' <2> '3' 1647(1 row) 1648 1649SELECT ts_rewrite('5 <-> (1 & (2 <-> 3))', 'SELECT keyword, sample FROM test_tsquery'::text ); 1650 ts_rewrite 1651------------------------- 1652 '5' <-> ( '2' <-> '4' ) 1653(1 row) 1654 1655SELECT ts_rewrite('5 <-> (6 | 8)', 'SELECT keyword, sample FROM test_tsquery'::text ); 1656 ts_rewrite 1657----------------------- 1658 '5' <-> ( '6' | '8' ) 1659(1 row) 1660 1661-- Check empty substitution 1662SELECT ts_rewrite(to_tsquery('5 & (6 | 5)'), to_tsquery('5'), to_tsquery('')); 1663NOTICE: text-search query doesn't contain lexemes: "" 1664 ts_rewrite 1665------------ 1666 '6' 1667(1 row) 1668 1669SELECT ts_rewrite(to_tsquery('!5'), to_tsquery('5'), to_tsquery('')); 1670NOTICE: text-search query doesn't contain lexemes: "" 1671 ts_rewrite 1672------------ 1673 1674(1 row) 1675 1676SELECT keyword FROM test_tsquery WHERE keyword @> 'new'; 1677 keyword 1678---------------- 1679 'new' & 'york' 1680(1 row) 1681 1682SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow'; 1683 keyword 1684---------- 1685 'moscow' 1686(1 row) 1687 1688SELECT keyword FROM test_tsquery WHERE keyword <@ 'new'; 1689 keyword 1690--------- 1691(0 rows) 1692 1693SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow'; 1694 keyword 1695---------- 1696 'moscow' 1697(1 row) 1698 1699SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 1700 ts_rewrite 1701--------------------- 1702 'moskva' | 'moscow' 1703(1 row) 1704 1705SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 1706 ts_rewrite 1707----------------------------------- 1708 'hotel' & ( 'moskva' | 'moscow' ) 1709(1 row) 1710 1711SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; 1712 ts_rewrite 1713--------------------------------------------------------------------------------- 1714 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' & 'appl' | 'new' & 'york' ) 1715(1 row) 1716 1717SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 1718 ts_rewrite 1719--------------------- 1720 'moskva' | 'moscow' 1721(1 row) 1722 1723SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 1724 ts_rewrite 1725----------------------------------- 1726 'hotel' & ( 'moskva' | 'moscow' ) 1727(1 row) 1728 1729SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; 1730 ts_rewrite 1731--------------------------------------------------------------------------------- 1732 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' & 'appl' | 'new' & 'york' ) 1733(1 row) 1734 1735CREATE INDEX qq ON test_tsquery USING gist (keyword tsquery_ops); 1736SET enable_seqscan=OFF; 1737SELECT keyword FROM test_tsquery WHERE keyword @> 'new'; 1738 keyword 1739---------------- 1740 'new' & 'york' 1741(1 row) 1742 1743SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow'; 1744 keyword 1745---------- 1746 'moscow' 1747(1 row) 1748 1749SELECT keyword FROM test_tsquery WHERE keyword <@ 'new'; 1750 keyword 1751--------- 1752(0 rows) 1753 1754SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow'; 1755 keyword 1756---------- 1757 'moscow' 1758(1 row) 1759 1760SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 1761 ts_rewrite 1762--------------------- 1763 'moskva' | 'moscow' 1764(1 row) 1765 1766SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 1767 ts_rewrite 1768----------------------------------- 1769 'hotel' & ( 'moskva' | 'moscow' ) 1770(1 row) 1771 1772SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; 1773 ts_rewrite 1774--------------------------------------------------------------------------------- 1775 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' & 'appl' | 'new' & 'york' ) 1776(1 row) 1777 1778SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 1779 ts_rewrite 1780--------------------- 1781 'moskva' | 'moscow' 1782(1 row) 1783 1784SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 1785 ts_rewrite 1786----------------------------------- 1787 'hotel' & ( 'moskva' | 'moscow' ) 1788(1 row) 1789 1790SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; 1791 ts_rewrite 1792--------------------------------------------------------------------------------- 1793 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' & 'appl' | 'new' & 'york' ) 1794(1 row) 1795 1796SELECT ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz'); 1797 ts_rewrite 1798----------------------------------------- 1799 ( 'bar' | 'baz' ) <-> ( 'bar' | 'baz' ) 1800(1 row) 1801 1802SELECT to_tsvector('foo bar') @@ 1803 ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz'); 1804 ?column? 1805---------- 1806 f 1807(1 row) 1808 1809SELECT to_tsvector('bar baz') @@ 1810 ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz'); 1811 ?column? 1812---------- 1813 t 1814(1 row) 1815 1816RESET enable_seqscan; 1817--test GUC 1818SET default_text_search_config=simple; 1819SELECT to_tsvector('SKIES My booKs'); 1820 to_tsvector 1821---------------------------- 1822 'books':3 'my':2 'skies':1 1823(1 row) 1824 1825SELECT plainto_tsquery('SKIES My booKs'); 1826 plainto_tsquery 1827-------------------------- 1828 'skies' & 'my' & 'books' 1829(1 row) 1830 1831SELECT to_tsquery('SKIES & My | booKs'); 1832 to_tsquery 1833-------------------------- 1834 'skies' & 'my' | 'books' 1835(1 row) 1836 1837SET default_text_search_config=english; 1838SELECT to_tsvector('SKIES My booKs'); 1839 to_tsvector 1840------------------ 1841 'book':3 'sky':1 1842(1 row) 1843 1844SELECT plainto_tsquery('SKIES My booKs'); 1845 plainto_tsquery 1846----------------- 1847 'sky' & 'book' 1848(1 row) 1849 1850SELECT to_tsquery('SKIES & My | booKs'); 1851 to_tsquery 1852---------------- 1853 'sky' | 'book' 1854(1 row) 1855 1856--trigger 1857CREATE TRIGGER tsvectorupdate 1858BEFORE UPDATE OR INSERT ON test_tsvector 1859FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(a, 'pg_catalog.english', t); 1860SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 1861 count 1862------- 1863 0 1864(1 row) 1865 1866INSERT INTO test_tsvector (t) VALUES ('345 qwerty'); 1867SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 1868 count 1869------- 1870 1 1871(1 row) 1872 1873UPDATE test_tsvector SET t = null WHERE t = '345 qwerty'; 1874SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 1875 count 1876------- 1877 0 1878(1 row) 1879 1880INSERT INTO test_tsvector (t) VALUES ('345 qwerty'); 1881SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 1882 count 1883------- 1884 1 1885(1 row) 1886 1887-- test finding items in GIN's pending list 1888create temp table pendtest (ts tsvector); 1889create index pendtest_idx on pendtest using gin(ts); 1890insert into pendtest values (to_tsvector('Lore ipsam')); 1891insert into pendtest values (to_tsvector('Lore ipsum')); 1892select * from pendtest where 'ipsu:*'::tsquery @@ ts; 1893 ts 1894-------------------- 1895 'ipsum':2 'lore':1 1896(1 row) 1897 1898select * from pendtest where 'ipsa:*'::tsquery @@ ts; 1899 ts 1900-------------------- 1901 'ipsam':2 'lore':1 1902(1 row) 1903 1904select * from pendtest where 'ips:*'::tsquery @@ ts; 1905 ts 1906-------------------- 1907 'ipsam':2 'lore':1 1908 'ipsum':2 'lore':1 1909(2 rows) 1910 1911select * from pendtest where 'ipt:*'::tsquery @@ ts; 1912 ts 1913---- 1914(0 rows) 1915 1916select * from pendtest where 'ipi:*'::tsquery @@ ts; 1917 ts 1918---- 1919(0 rows) 1920 1921--check OP_PHRASE on index 1922create temp table phrase_index_test(fts tsvector); 1923insert into phrase_index_test values ('A fat cat has just eaten a rat.'); 1924insert into phrase_index_test values (to_tsvector('english', 'A fat cat has just eaten a rat.')); 1925create index phrase_index_test_idx on phrase_index_test using gin(fts); 1926set enable_seqscan = off; 1927select * from phrase_index_test where fts @@ phraseto_tsquery('english', 'fat cat'); 1928 fts 1929----------------------------------- 1930 'cat':3 'eaten':6 'fat':2 'rat':8 1931(1 row) 1932 1933set enable_seqscan = on; 1934-- test websearch_to_tsquery function 1935select websearch_to_tsquery('simple', 'I have a fat:*ABCD cat'); 1936 websearch_to_tsquery 1937--------------------------------------------- 1938 'i' & 'have' & 'a' & 'fat' & 'abcd' & 'cat' 1939(1 row) 1940 1941select websearch_to_tsquery('simple', 'orange:**AABBCCDD'); 1942 websearch_to_tsquery 1943----------------------- 1944 'orange' & 'aabbccdd' 1945(1 row) 1946 1947select websearch_to_tsquery('simple', 'fat:A!cat:B|rat:C<'); 1948 websearch_to_tsquery 1949----------------------------------------- 1950 'fat' & 'a' & 'cat' & 'b' & 'rat' & 'c' 1951(1 row) 1952 1953select websearch_to_tsquery('simple', 'fat:A : cat:B'); 1954 websearch_to_tsquery 1955--------------------------- 1956 'fat' & 'a' & 'cat' & 'b' 1957(1 row) 1958 1959select websearch_to_tsquery('simple', 'fat*rat'); 1960 websearch_to_tsquery 1961---------------------- 1962 'fat' & 'rat' 1963(1 row) 1964 1965select websearch_to_tsquery('simple', 'fat-rat'); 1966 websearch_to_tsquery 1967--------------------------- 1968 'fat-rat' & 'fat' & 'rat' 1969(1 row) 1970 1971select websearch_to_tsquery('simple', 'fat_rat'); 1972 websearch_to_tsquery 1973---------------------- 1974 'fat' & 'rat' 1975(1 row) 1976 1977-- weights are completely ignored 1978select websearch_to_tsquery('simple', 'abc : def'); 1979 websearch_to_tsquery 1980---------------------- 1981 'abc' & 'def' 1982(1 row) 1983 1984select websearch_to_tsquery('simple', 'abc:def'); 1985 websearch_to_tsquery 1986---------------------- 1987 'abc' & 'def' 1988(1 row) 1989 1990select websearch_to_tsquery('simple', 'a:::b'); 1991 websearch_to_tsquery 1992---------------------- 1993 'a' & 'b' 1994(1 row) 1995 1996select websearch_to_tsquery('simple', 'abc:d'); 1997 websearch_to_tsquery 1998---------------------- 1999 'abc' & 'd' 2000(1 row) 2001 2002select websearch_to_tsquery('simple', ':'); 2003NOTICE: text-search query contains only stop words or doesn't contain lexemes, ignored 2004 websearch_to_tsquery 2005---------------------- 2006 2007(1 row) 2008 2009-- these operators are ignored 2010select websearch_to_tsquery('simple', 'abc & def'); 2011 websearch_to_tsquery 2012---------------------- 2013 'abc' & 'def' 2014(1 row) 2015 2016select websearch_to_tsquery('simple', 'abc | def'); 2017 websearch_to_tsquery 2018---------------------- 2019 'abc' & 'def' 2020(1 row) 2021 2022select websearch_to_tsquery('simple', 'abc <-> def'); 2023 websearch_to_tsquery 2024---------------------- 2025 'abc' & 'def' 2026(1 row) 2027 2028select websearch_to_tsquery('simple', 'abc (pg or class)'); 2029 websearch_to_tsquery 2030------------------------ 2031 'abc' & 'pg' | 'class' 2032(1 row) 2033 2034-- NOT is ignored in quotes 2035select websearch_to_tsquery('english', 'My brand new smartphone'); 2036 websearch_to_tsquery 2037------------------------------- 2038 'brand' & 'new' & 'smartphon' 2039(1 row) 2040 2041select websearch_to_tsquery('english', 'My brand "new smartphone"'); 2042 websearch_to_tsquery 2043--------------------------------- 2044 'brand' & 'new' <-> 'smartphon' 2045(1 row) 2046 2047select websearch_to_tsquery('english', 'My brand "new -smartphone"'); 2048 websearch_to_tsquery 2049--------------------------------- 2050 'brand' & 'new' <-> 'smartphon' 2051(1 row) 2052 2053-- test OR operator 2054select websearch_to_tsquery('simple', 'cat or rat'); 2055 websearch_to_tsquery 2056---------------------- 2057 'cat' | 'rat' 2058(1 row) 2059 2060select websearch_to_tsquery('simple', 'cat OR rat'); 2061 websearch_to_tsquery 2062---------------------- 2063 'cat' | 'rat' 2064(1 row) 2065 2066select websearch_to_tsquery('simple', 'cat "OR" rat'); 2067 websearch_to_tsquery 2068---------------------- 2069 'cat' & 'or' & 'rat' 2070(1 row) 2071 2072select websearch_to_tsquery('simple', 'cat OR'); 2073 websearch_to_tsquery 2074---------------------- 2075 'cat' & 'or' 2076(1 row) 2077 2078select websearch_to_tsquery('simple', 'OR rat'); 2079 websearch_to_tsquery 2080---------------------- 2081 'or' & 'rat' 2082(1 row) 2083 2084select websearch_to_tsquery('simple', '"fat cat OR rat"'); 2085 websearch_to_tsquery 2086------------------------------------ 2087 'fat' <-> 'cat' <-> 'or' <-> 'rat' 2088(1 row) 2089 2090select websearch_to_tsquery('simple', 'fat (cat OR rat'); 2091 websearch_to_tsquery 2092----------------------- 2093 'fat' & 'cat' | 'rat' 2094(1 row) 2095 2096select websearch_to_tsquery('simple', 'or OR or'); 2097 websearch_to_tsquery 2098---------------------- 2099 'or' | 'or' 2100(1 row) 2101 2102-- OR is an operator here ... 2103select websearch_to_tsquery('simple', '"fat cat"or"fat rat"'); 2104 websearch_to_tsquery 2105----------------------------------- 2106 'fat' <-> 'cat' | 'fat' <-> 'rat' 2107(1 row) 2108 2109select websearch_to_tsquery('simple', 'fat or(rat'); 2110 websearch_to_tsquery 2111---------------------- 2112 'fat' | 'rat' 2113(1 row) 2114 2115select websearch_to_tsquery('simple', 'fat or)rat'); 2116 websearch_to_tsquery 2117---------------------- 2118 'fat' | 'rat' 2119(1 row) 2120 2121select websearch_to_tsquery('simple', 'fat or&rat'); 2122 websearch_to_tsquery 2123---------------------- 2124 'fat' | 'rat' 2125(1 row) 2126 2127select websearch_to_tsquery('simple', 'fat or|rat'); 2128 websearch_to_tsquery 2129---------------------- 2130 'fat' | 'rat' 2131(1 row) 2132 2133select websearch_to_tsquery('simple', 'fat or!rat'); 2134 websearch_to_tsquery 2135---------------------- 2136 'fat' | 'rat' 2137(1 row) 2138 2139select websearch_to_tsquery('simple', 'fat or<rat'); 2140 websearch_to_tsquery 2141---------------------- 2142 'fat' | 'rat' 2143(1 row) 2144 2145select websearch_to_tsquery('simple', 'fat or>rat'); 2146 websearch_to_tsquery 2147---------------------- 2148 'fat' | 'rat' 2149(1 row) 2150 2151select websearch_to_tsquery('simple', 'fat or '); 2152 websearch_to_tsquery 2153---------------------- 2154 'fat' & 'or' 2155(1 row) 2156 2157-- ... but not here 2158select websearch_to_tsquery('simple', 'abc orange'); 2159 websearch_to_tsquery 2160---------------------- 2161 'abc' & 'orange' 2162(1 row) 2163 2164select websearch_to_tsquery('simple', 'abc OR1234'); 2165 websearch_to_tsquery 2166---------------------- 2167 'abc' & 'or1234' 2168(1 row) 2169 2170select websearch_to_tsquery('simple', 'abc or-abc'); 2171 websearch_to_tsquery 2172--------------------------------- 2173 'abc' & 'or-abc' & 'or' & 'abc' 2174(1 row) 2175 2176select websearch_to_tsquery('simple', 'abc OR_abc'); 2177 websearch_to_tsquery 2178---------------------- 2179 'abc' & 'or' & 'abc' 2180(1 row) 2181 2182-- test quotes 2183select websearch_to_tsquery('english', '"pg_class pg'); 2184 websearch_to_tsquery 2185----------------------- 2186 'pg' & 'class' & 'pg' 2187(1 row) 2188 2189select websearch_to_tsquery('english', 'pg_class pg"'); 2190 websearch_to_tsquery 2191----------------------- 2192 'pg' & 'class' & 'pg' 2193(1 row) 2194 2195select websearch_to_tsquery('english', '"pg_class pg"'); 2196 websearch_to_tsquery 2197----------------------------- 2198 ( 'pg' & 'class' ) <-> 'pg' 2199(1 row) 2200 2201select websearch_to_tsquery('english', 'abc "pg_class pg"'); 2202 websearch_to_tsquery 2203------------------------------------- 2204 'abc' & ( 'pg' & 'class' ) <-> 'pg' 2205(1 row) 2206 2207select websearch_to_tsquery('english', '"pg_class pg" def'); 2208 websearch_to_tsquery 2209------------------------------------- 2210 ( 'pg' & 'class' ) <-> 'pg' & 'def' 2211(1 row) 2212 2213select websearch_to_tsquery('english', 'abc "pg pg_class pg" def'); 2214 websearch_to_tsquery 2215------------------------------------------------------ 2216 'abc' & 'pg' <-> ( 'pg' & 'class' ) <-> 'pg' & 'def' 2217(1 row) 2218 2219select websearch_to_tsquery('english', ' or "pg pg_class pg" or '); 2220 websearch_to_tsquery 2221-------------------------------------- 2222 'pg' <-> ( 'pg' & 'class' ) <-> 'pg' 2223(1 row) 2224 2225select websearch_to_tsquery('english', '""pg pg_class pg""'); 2226 websearch_to_tsquery 2227------------------------------ 2228 'pg' & 'pg' & 'class' & 'pg' 2229(1 row) 2230 2231select websearch_to_tsquery('english', 'abc """"" def'); 2232 websearch_to_tsquery 2233---------------------- 2234 'abc' & 'def' 2235(1 row) 2236 2237select websearch_to_tsquery('english', 'cat -"fat rat"'); 2238 websearch_to_tsquery 2239------------------------------ 2240 'cat' & !( 'fat' <-> 'rat' ) 2241(1 row) 2242 2243select websearch_to_tsquery('english', 'cat -"fat rat" cheese'); 2244 websearch_to_tsquery 2245---------------------------------------- 2246 'cat' & !( 'fat' <-> 'rat' ) & 'chees' 2247(1 row) 2248 2249select websearch_to_tsquery('english', 'abc "def -"'); 2250 websearch_to_tsquery 2251---------------------- 2252 'abc' & 'def' 2253(1 row) 2254 2255select websearch_to_tsquery('english', 'abc "def :"'); 2256 websearch_to_tsquery 2257---------------------- 2258 'abc' & 'def' 2259(1 row) 2260 2261select websearch_to_tsquery('english', '"A fat cat" has just eaten a -rat.'); 2262 websearch_to_tsquery 2263------------------------------------ 2264 'fat' <-> 'cat' & 'eaten' & !'rat' 2265(1 row) 2266 2267select websearch_to_tsquery('english', '"A fat cat" has just eaten OR !rat.'); 2268 websearch_to_tsquery 2269----------------------------------- 2270 'fat' <-> 'cat' & 'eaten' | 'rat' 2271(1 row) 2272 2273select websearch_to_tsquery('english', '"A fat cat" has just (+eaten OR -rat)'); 2274 websearch_to_tsquery 2275------------------------------------ 2276 'fat' <-> 'cat' & 'eaten' | !'rat' 2277(1 row) 2278 2279select websearch_to_tsquery('english', 'this is ----fine'); 2280 websearch_to_tsquery 2281---------------------- 2282 !!!!'fine' 2283(1 row) 2284 2285select websearch_to_tsquery('english', '(()) )))) this ||| is && -fine, "dear friend" OR good'); 2286 websearch_to_tsquery 2287---------------------------------------- 2288 !'fine' & 'dear' <-> 'friend' | 'good' 2289(1 row) 2290 2291select websearch_to_tsquery('english', 'an old <-> cat " is fine &&& too'); 2292 websearch_to_tsquery 2293------------------------ 2294 'old' & 'cat' & 'fine' 2295(1 row) 2296 2297select websearch_to_tsquery('english', '"A the" OR just on'); 2298NOTICE: text-search query contains only stop words or doesn't contain lexemes, ignored 2299 websearch_to_tsquery 2300---------------------- 2301 2302(1 row) 2303 2304select websearch_to_tsquery('english', '"a fat cat" ate a rat'); 2305 websearch_to_tsquery 2306--------------------------------- 2307 'fat' <-> 'cat' & 'ate' & 'rat' 2308(1 row) 2309 2310select to_tsvector('english', 'A fat cat ate a rat') @@ 2311 websearch_to_tsquery('english', '"a fat cat" ate a rat'); 2312 ?column? 2313---------- 2314 t 2315(1 row) 2316 2317select to_tsvector('english', 'A fat grey cat ate a rat') @@ 2318 websearch_to_tsquery('english', '"a fat cat" ate a rat'); 2319 ?column? 2320---------- 2321 f 2322(1 row) 2323 2324-- cases handled by gettoken_tsvector() 2325select websearch_to_tsquery(''''); 2326NOTICE: text-search query contains only stop words or doesn't contain lexemes, ignored 2327 websearch_to_tsquery 2328---------------------- 2329 2330(1 row) 2331 2332select websearch_to_tsquery('''abc''''def'''); 2333 websearch_to_tsquery 2334---------------------- 2335 'abc' & 'def' 2336(1 row) 2337 2338select websearch_to_tsquery('\abc'); 2339 websearch_to_tsquery 2340---------------------- 2341 'abc' 2342(1 row) 2343 2344select websearch_to_tsquery('\'); 2345NOTICE: text-search query contains only stop words or doesn't contain lexemes, ignored 2346 websearch_to_tsquery 2347---------------------- 2348 2349(1 row) 2350 2351