2 -- Sanity checks for text search catalogs
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
11 WHERE prsnamespace = 0 OR prsstart = 0 OR prstoken = 0 OR prsend = 0 OR
12 -- prsheadline is optional
20 WHERE dictnamespace = 0 OR dictowner = 0 OR dicttemplate = 0;
27 WHERE tmplnamespace = 0 OR tmpllexize = 0; -- tmplinit is optional
34 WHERE cfgnamespace = 0 OR cfgowner = 0 OR cfgparser = 0;
39 SELECT mapcfg, maptokentype, mapseqno
41 WHERE mapcfg = 0 OR mapdict = 0;
42 mapcfg | maptokentype | mapseqno
43 --------+--------------+----------
46 -- Look for pg_ts_config_map entries that aren't one of parser's token types
48 ( SELECT oid AS cfgid, (ts_token_type(cfgparser)).tokid AS tokid
49 FROM pg_ts_config ) AS tt
50 RIGHT JOIN pg_ts_config_map AS m
51 ON (tt.cfgid=m.mapcfg AND tt.tokid=m.maptokentype)
53 tt.cfgid IS NULL OR tt.tokid IS NULL;
54 cfgid | tokid | mapcfg | maptokentype | mapseqno | mapdict
55 -------+-------+--------+--------------+----------+---------
58 -- test basic text search behavior without indexes, then with
59 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
65 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
71 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
77 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
83 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
89 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
95 SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*';
101 SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}');
107 SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme';
113 SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme';
119 create index wowidx on test_tsvector using gist (a);
120 SET enable_seqscan=OFF;
121 SET enable_indexscan=ON;
122 SET enable_bitmapscan=OFF;
123 explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
125 -------------------------------------------------------
127 -> Index Scan using wowidx on test_tsvector
128 Index Cond: (a @@ '''wr'' | ''qh'''::tsquery)
131 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
137 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
143 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
149 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
155 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
161 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
167 SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*';
173 SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}');
179 SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme';
185 SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme';
191 SET enable_indexscan=OFF;
192 SET enable_bitmapscan=ON;
193 explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
195 -------------------------------------------------------------
197 -> Bitmap Heap Scan on test_tsvector
198 Recheck Cond: (a @@ '''wr'' | ''qh'''::tsquery)
199 -> Bitmap Index Scan on wowidx
200 Index Cond: (a @@ '''wr'' | ''qh'''::tsquery)
203 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
209 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
215 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
221 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
227 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
233 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
239 SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*';
245 SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}');
251 SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme';
257 SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme';
263 RESET enable_seqscan;
264 RESET enable_indexscan;
265 RESET enable_bitmapscan;
267 CREATE INDEX wowidx ON test_tsvector USING gin (a);
268 SET enable_seqscan=OFF;
269 -- GIN only supports bitmapscan, so no need to test plain indexscan
270 explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
272 -------------------------------------------------------------
274 -> Bitmap Heap Scan on test_tsvector
275 Recheck Cond: (a @@ '''wr'' | ''qh'''::tsquery)
276 -> Bitmap Index Scan on wowidx
277 Index Cond: (a @@ '''wr'' | ''qh'''::tsquery)
280 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
286 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
292 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
298 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
304 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
310 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
316 SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*';
322 SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}');
328 SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme';
334 SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme';
340 RESET enable_seqscan;
341 INSERT INTO test_tsvector VALUES ('???', 'DFG:1A,2B,6C,10 FGH');
342 SELECT * FROM ts_stat('SELECT a FROM test_tsvector') ORDER BY ndoc DESC, nentry DESC, word LIMIT 10;
344 ------+------+--------
357 SELECT * FROM ts_stat('SELECT a FROM test_tsvector', 'AB') ORDER BY ndoc DESC, nentry DESC, word;
359 ------+------+--------
363 --dictionaries and to_tsvector
364 SELECT ts_lexize('english_stem', 'skies');
370 SELECT ts_lexize('english_stem', 'identity');
376 SELECT * FROM ts_token_type('default');
377 tokid | alias | description
378 -------+-----------------+------------------------------------------
379 1 | asciiword | Word, all ASCII
380 2 | word | Word, all letters
381 3 | numword | Word, letters and digits
382 4 | email | Email address
385 7 | sfloat | Scientific notation
386 8 | version | Version number
387 9 | hword_numpart | Hyphenated word part, letters and digits
388 10 | hword_part | Hyphenated word part, all letters
389 11 | hword_asciipart | Hyphenated word part, all ASCII
390 12 | blank | Space symbols
392 14 | protocol | Protocol head
393 15 | numhword | Hyphenated word, letters and digits
394 16 | asciihword | Hyphenated word, all ASCII
395 17 | hword | Hyphenated word, all letters
396 18 | url_path | URL path
397 19 | file | File or path name
398 20 | float | Decimal notation
399 21 | int | Signed integer
400 22 | uint | Unsigned integer
401 23 | entity | XML entity
404 SELECT * 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>">
405 /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
406 <i <b> wow < jqw <> qwerty');
408 -------+--------------------------------------
419 5 | aew.werc.ewr/?ad=qwe&dw
423 5 | 1aew.werc.ewr/?ad=qwe&dw
430 5 | 3aew.werc.ewr/?ad=qwe&dw
438 5 | 5aew.werc.ewr:8100/?
439 6 | 5aew.werc.ewr:8100
448 5 | 6aew.werc.ewr:8100/?ad=qwe&dw
449 6 | 6aew.werc.ewr:8100
452 5 | 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32
453 6 | 7aew.werc.ewr:8100
454 18 | /?ad=qwe&dw=%20%32
472 4 | teodor@123-stack.net
474 4 | 123_teodor@stack.net
476 4 | 123-teodor@stack.net
502 13 | <a href="qwe<qwe>">
552 SELECT 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>">
553 /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
554 <i <b> wow < jqw <> qwerty');
556 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
557 '+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
560 SELECT 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>">
561 /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
562 <i <b> wow < jqw <> qwerty'));
569 SELECT * from ts_debug('english', '<myns:foo-bar_baz.blurfl>abc&nm1;def©ghiõjkl</myns:foo-bar_baz.blurfl>');
570 alias | description | token | dictionaries | dictionary | lexemes
571 -----------+-----------------+----------------------------+----------------+--------------+---------
572 tag | XML tag | <myns:foo-bar_baz.blurfl> | {} | |
573 asciiword | Word, all ASCII | abc | {english_stem} | english_stem | {abc}
574 entity | XML entity | &nm1; | {} | |
575 asciiword | Word, all ASCII | def | {english_stem} | english_stem | {def}
576 entity | XML entity | © | {} | |
577 asciiword | Word, all ASCII | ghi | {english_stem} | english_stem | {ghi}
578 entity | XML entity | õ | {} | |
579 asciiword | Word, all ASCII | jkl | {english_stem} | english_stem | {jkl}
580 tag | XML tag | </myns:foo-bar_baz.blurfl> | {} | |
583 -- check parsing of URLs
584 SELECT * from ts_debug('english', 'http://www.harewoodsolutions.co.uk/press.aspx</span>');
585 alias | description | token | dictionaries | dictionary | lexemes
586 ----------+---------------+----------------------------------------+--------------+------------+------------------------------------------
587 protocol | Protocol head | http:// | {} | |
588 url | URL | www.harewoodsolutions.co.uk/press.aspx | {simple} | simple | {www.harewoodsolutions.co.uk/press.aspx}
589 host | Host | www.harewoodsolutions.co.uk | {simple} | simple | {www.harewoodsolutions.co.uk}
590 url_path | URL path | /press.aspx | {simple} | simple | {/press.aspx}
591 tag | XML tag | </span> | {} | |
594 SELECT * from ts_debug('english', 'http://aew.wer0c.ewr/id?ad=qwe&dw<span>');
595 alias | description | token | dictionaries | dictionary | lexemes
596 ----------+---------------+----------------------------+--------------+------------+------------------------------
597 protocol | Protocol head | http:// | {} | |
598 url | URL | aew.wer0c.ewr/id?ad=qwe&dw | {simple} | simple | {aew.wer0c.ewr/id?ad=qwe&dw}
599 host | Host | aew.wer0c.ewr | {simple} | simple | {aew.wer0c.ewr}
600 url_path | URL path | /id?ad=qwe&dw | {simple} | simple | {/id?ad=qwe&dw}
601 tag | XML tag | <span> | {} | |
604 SELECT * from ts_debug('english', 'http://5aew.werc.ewr:8100/?');
605 alias | description | token | dictionaries | dictionary | lexemes
606 ----------+---------------+----------------------+--------------+------------+------------------------
607 protocol | Protocol head | http:// | {} | |
608 url | URL | 5aew.werc.ewr:8100/? | {simple} | simple | {5aew.werc.ewr:8100/?}
609 host | Host | 5aew.werc.ewr:8100 | {simple} | simple | {5aew.werc.ewr:8100}
610 url_path | URL path | /? | {simple} | simple | {/?}
613 SELECT * from ts_debug('english', '5aew.werc.ewr:8100/?xx');
614 alias | description | token | dictionaries | dictionary | lexemes
615 ----------+-------------+------------------------+--------------+------------+--------------------------
616 url | URL | 5aew.werc.ewr:8100/?xx | {simple} | simple | {5aew.werc.ewr:8100/?xx}
617 host | Host | 5aew.werc.ewr:8100 | {simple} | simple | {5aew.werc.ewr:8100}
618 url_path | URL path | /?xx | {simple} | simple | {/?xx}
622 SELECT to_tsquery('english', 'qwe & sKies ');
628 SELECT to_tsquery('simple', 'qwe & sKies ');
634 SELECT to_tsquery('english', '''the wether'':dc & '' sKies '':BC ');
636 ------------------------
637 'wether':CD & 'sky':BC
640 SELECT to_tsquery('english', 'asd&(and|fghj)');
646 SELECT to_tsquery('english', '(asd&and)|fghj');
652 SELECT to_tsquery('english', '(asd&!and)|fghj');
658 SELECT to_tsquery('english', '(the|and&(i&1))&fghj');
664 SELECT plainto_tsquery('english', 'the and z 1))& fghj');
670 SELECT plainto_tsquery('english', 'foo bar') && plainto_tsquery('english', 'asd');
672 -----------------------
673 'foo' & 'bar' & 'asd'
676 SELECT plainto_tsquery('english', 'foo bar') || plainto_tsquery('english', 'asd fg');
678 ------------------------------
679 'foo' & 'bar' | 'asd' & 'fg'
682 SELECT plainto_tsquery('english', 'foo bar') || !!plainto_tsquery('english', 'asd fg');
684 -----------------------------------
685 'foo' & 'bar' | !( 'asd' & 'fg' )
688 SELECT plainto_tsquery('english', 'foo bar') && 'asd | fg';
690 ----------------------------------
691 'foo' & 'bar' & ( 'asd' | 'fg' )
694 -- Check stop word deletion, a and s are stop-words
695 SELECT to_tsquery('english', '!(a & !b) & c');
701 SELECT to_tsquery('english', '!(a & !b)');
707 SELECT to_tsquery('english', '(1 <-> 2) <-> a');
713 SELECT to_tsquery('english', '(1 <-> a) <-> 2');
719 SELECT to_tsquery('english', '(a <-> 1) <-> 2');
725 SELECT to_tsquery('english', 'a <-> (1 <-> 2)');
731 SELECT to_tsquery('english', '1 <-> (a <-> 2)');
737 SELECT to_tsquery('english', '1 <-> (2 <-> a)');
743 SELECT to_tsquery('english', '(1 <-> 2) <3> a');
749 SELECT to_tsquery('english', '(1 <-> a) <3> 2');
755 SELECT to_tsquery('english', '(a <-> 1) <3> 2');
761 SELECT to_tsquery('english', 'a <3> (1 <-> 2)');
767 SELECT to_tsquery('english', '1 <3> (a <-> 2)');
773 SELECT to_tsquery('english', '1 <3> (2 <-> a)');
779 SELECT to_tsquery('english', '(1 <3> 2) <-> a');
785 SELECT to_tsquery('english', '(1 <3> a) <-> 2');
791 SELECT to_tsquery('english', '(a <3> 1) <-> 2');
797 SELECT to_tsquery('english', 'a <-> (1 <3> 2)');
803 SELECT to_tsquery('english', '1 <-> (a <3> 2)');
809 SELECT to_tsquery('english', '1 <-> (2 <3> a)');
815 SELECT to_tsquery('english', '((a <-> 1) <-> 2) <-> s');
821 SELECT to_tsquery('english', '(2 <-> (a <-> 1)) <-> s');
827 SELECT to_tsquery('english', '((1 <-> a) <-> 2) <-> s');
833 SELECT to_tsquery('english', '(2 <-> (1 <-> a)) <-> s');
839 SELECT to_tsquery('english', 's <-> ((a <-> 1) <-> 2)');
845 SELECT to_tsquery('english', 's <-> (2 <-> (a <-> 1))');
851 SELECT to_tsquery('english', 's <-> ((1 <-> a) <-> 2)');
857 SELECT to_tsquery('english', 's <-> (2 <-> (1 <-> a))');
863 SELECT to_tsquery('english', '((a <-> 1) <-> s) <-> 2');
869 SELECT to_tsquery('english', '(s <-> (a <-> 1)) <-> 2');
875 SELECT to_tsquery('english', '((1 <-> a) <-> s) <-> 2');
881 SELECT to_tsquery('english', '(s <-> (1 <-> a)) <-> 2');
887 SELECT to_tsquery('english', '2 <-> ((a <-> 1) <-> s)');
893 SELECT to_tsquery('english', '2 <-> (s <-> (a <-> 1))');
899 SELECT to_tsquery('english', '2 <-> ((1 <-> a) <-> s)');
905 SELECT to_tsquery('english', '2 <-> (s <-> (1 <-> a))');
911 SELECT to_tsquery('english', 'foo <-> (a <-> (the <-> bar))');
917 SELECT to_tsquery('english', '((foo <-> a) <-> the) <-> bar');
923 SELECT to_tsquery('english', 'foo <-> a <-> the <-> bar');
929 SELECT phraseto_tsquery('english', 'PostgreSQL can be extended by the user in many ways');
931 -----------------------------------------------------------
932 'postgresql' <3> 'extend' <3> 'user' <2> 'mani' <-> 'way'
935 SELECT ts_rank_cd(to_tsvector('english', '
936 Day after day, day after day,
937 We stuck, nor breath nor motion,
938 As idle as a painted Ship
939 Upon a painted Ocean.
940 Water, water, every where
941 And all the boards did shrink;
942 Water, water, every where,
943 Nor any drop to drink.
944 S. T. Coleridge (1772-1834)
945 '), to_tsquery('english', 'paint&water'));
951 SELECT ts_rank_cd(to_tsvector('english', '
952 Day after day, day after day,
953 We stuck, nor breath nor motion,
954 As idle as a painted Ship
955 Upon a painted Ocean.
956 Water, water, every where
957 And all the boards did shrink;
958 Water, water, every where,
959 Nor any drop to drink.
960 S. T. Coleridge (1772-1834)
961 '), to_tsquery('english', 'breath&motion&water'));
967 SELECT ts_rank_cd(to_tsvector('english', '
968 Day after day, day after day,
969 We stuck, nor breath nor motion,
970 As idle as a painted Ship
971 Upon a painted Ocean.
972 Water, water, every where
973 And all the boards did shrink;
974 Water, water, every where,
975 Nor any drop to drink.
976 S. T. Coleridge (1772-1834)
977 '), to_tsquery('english', 'ocean'));
983 SELECT ts_rank_cd(to_tsvector('english', '
984 Day after day, day after day,
985 We stuck, nor breath nor motion,
986 As idle as a painted Ship
987 Upon a painted Ocean.
988 Water, water, every where
989 And all the boards did shrink;
990 Water, water, every where,
991 Nor any drop to drink.
992 S. T. Coleridge (1772-1834)
993 '), to_tsquery('english', 'painted <-> Ship'));
999 SELECT ts_rank_cd(strip(to_tsvector('both stripped')),
1000 to_tsquery('both & stripped'));
1006 SELECT ts_rank_cd(to_tsvector('unstripped') || strip(to_tsvector('stripped')),
1007 to_tsquery('unstripped & stripped'));
1014 SELECT ts_headline('english', '
1015 Day after day, day after day,
1016 We stuck, nor breath nor motion,
1017 As idle as a painted Ship
1018 Upon a painted Ocean.
1019 Water, water, every where
1020 And all the boards did shrink;
1021 Water, water, every where,
1022 Nor any drop to drink.
1023 S. T. Coleridge (1772-1834)
1024 ', to_tsquery('english', 'paint&water'));
1026 -----------------------------------------
1027 <b>painted</b> Ocean. +
1028 <b>Water</b>, <b>water</b>, every where+
1029 And all the boards did shrink; +
1030 <b>Water</b>, <b>water</b>, every
1033 SELECT ts_headline('english', '
1034 Day after day, day after day,
1035 We stuck, nor breath nor motion,
1036 As idle as a painted Ship
1037 Upon a painted Ocean.
1038 Water, water, every where
1039 And all the boards did shrink;
1040 Water, water, every where,
1041 Nor any drop to drink.
1042 S. T. Coleridge (1772-1834)
1043 ', to_tsquery('english', 'breath&motion&water'));
1045 ----------------------------------
1046 <b>breath</b> nor <b>motion</b>,+
1047 As idle as a painted Ship +
1048 Upon a painted Ocean. +
1049 <b>Water</b>, <b>water</b>
1052 SELECT ts_headline('english', '
1053 Day after day, day after day,
1054 We stuck, nor breath nor motion,
1055 As idle as a painted Ship
1056 Upon a painted Ocean.
1057 Water, water, every where
1058 And all the boards did shrink;
1059 Water, water, every where,
1060 Nor any drop to drink.
1061 S. T. Coleridge (1772-1834)
1062 ', to_tsquery('english', 'ocean'));
1064 ----------------------------------
1066 Water, water, every where +
1067 And all the boards did shrink;+
1068 Water, water, every where
1071 SELECT ts_headline('english', '
1072 Day after day, day after day,
1073 We stuck, nor breath nor motion,
1074 As idle as a painted Ship
1075 Upon a painted Ocean.
1076 Water, water, every where
1077 And all the boards did shrink;
1078 Water, water, every where,
1079 Nor any drop to drink.
1080 S. T. Coleridge (1772-1834)
1081 ', phraseto_tsquery('english', 'painted Ocean'));
1083 ----------------------------------
1084 <b>painted</b> <b>Ocean</b>. +
1085 Water, water, every where +
1086 And all the boards did shrink;+
1090 SELECT ts_headline('english', '
1091 Day after day, day after day,
1092 We stuck, nor breath nor motion,
1093 As idle as a painted Ship
1094 Upon a painted Ocean.
1095 Water, water, every where
1096 And all the boards did shrink;
1097 Water, water, every where,
1098 Nor any drop to drink.
1099 S. T. Coleridge (1772-1834)
1100 ', phraseto_tsquery('english', 'idle as a painted Ship'));
1102 ---------------------------------------------
1103 <b>idle</b> as a <b>painted</b> <b>Ship</b>+
1104 Upon a <b>painted</b> Ocean. +
1105 Water, water, every where +
1109 SELECT ts_headline('english', '
1111 <!-- some comment -->
1113 Sea view wow <u>foo bar</u> <i>qq</i>
1114 <a href="http://www.google.com/foo.bar.html" target="_blank">YES </a>
1121 to_tsquery('english', 'sea&foo'), 'HighlightAll=true');
1123 -----------------------------------------------------------------------------
1126 <!-- some comment --> +
1128 <b>Sea</b> view wow <u><b>foo</b> bar</u> <i>qq</i> +
1129 <a href="http://www.google.com/foo.bar.html" target="_blank">YES </a>+
1132 document.write(15); +
1138 SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 <-> 3', 'MaxWords=2, MinWords=1');
1144 SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 & 3', 'MaxWords=4, MinWords=1');
1146 ------------------------------
1147 <b>1</b> 2 <b>3</b> <b>1</b>
1150 SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 <-> 3', 'MaxWords=4, MinWords=1');
1156 --Check if headline fragments work
1157 SELECT ts_headline('english', '
1158 Day after day, day after day,
1159 We stuck, nor breath nor motion,
1160 As idle as a painted Ship
1161 Upon a painted Ocean.
1162 Water, water, every where
1163 And all the boards did shrink;
1164 Water, water, every where,
1165 Nor any drop to drink.
1166 S. T. Coleridge (1772-1834)
1167 ', to_tsquery('english', 'ocean'), 'MaxFragments=1');
1169 ------------------------------------
1171 We stuck, nor breath nor motion,+
1172 As idle as a painted Ship +
1173 Upon a painted <b>Ocean</b>. +
1174 Water, water, every where +
1175 And all the boards did shrink; +
1176 Water, water, every where, +
1180 --Check if more than one fragments are displayed
1181 SELECT ts_headline('english', '
1182 Day after day, day after day,
1183 We stuck, nor breath nor motion,
1184 As idle as a painted Ship
1185 Upon a painted Ocean.
1186 Water, water, every where
1187 And all the boards did shrink;
1188 Water, water, every where,
1189 Nor any drop to drink.
1190 S. T. Coleridge (1772-1834)
1191 ', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2');
1193 ----------------------------------------------
1194 after day, day after day, +
1195 We <b>stuck</b>, nor breath nor motion, +
1196 As idle as a painted Ship +
1197 Upon a painted Ocean. +
1198 Water, water, every where +
1199 And all the boards did shrink; +
1200 Water, water, every where ... drop to drink.+
1201 S. T. <b>Coleridge</b>
1204 --Fragments when there all query words are not in the document
1205 SELECT ts_headline('english', '
1206 Day after day, day after day,
1207 We stuck, nor breath nor motion,
1208 As idle as a painted Ship
1209 Upon a painted Ocean.
1210 Water, water, every where
1211 And all the boards did shrink;
1212 Water, water, every where,
1213 Nor any drop to drink.
1214 S. T. Coleridge (1772-1834)
1215 ', to_tsquery('english', 'ocean & seahorse'), 'MaxFragments=1');
1217 ------------------------------------
1219 Day after day, day after day, +
1220 We stuck, nor breath nor motion,+
1224 --FragmentDelimiter option
1225 SELECT ts_headline('english', '
1226 Day after day, day after day,
1227 We stuck, nor breath nor motion,
1228 As idle as a painted Ship
1229 Upon a painted Ocean.
1230 Water, water, every where
1231 And all the boards did shrink;
1232 Water, water, every where,
1233 Nor any drop to drink.
1234 S. T. Coleridge (1772-1834)
1235 ', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2,FragmentDelimiter=***');
1237 --------------------------------------------
1238 after day, day after day, +
1239 We <b>stuck</b>, nor breath nor motion, +
1240 As idle as a painted Ship +
1241 Upon a painted Ocean. +
1242 Water, water, every where +
1243 And all the boards did shrink; +
1244 Water, water, every where***drop to drink.+
1245 S. T. <b>Coleridge</b>
1248 --Rewrite sub system
1249 CREATE TABLE test_tsquery (txtkeyword TEXT, txtsample TEXT);
1251 ALTER TABLE test_tsquery ADD COLUMN keyword tsquery;
1252 UPDATE test_tsquery SET keyword = to_tsquery('english', txtkeyword);
1253 ALTER TABLE test_tsquery ADD COLUMN sample tsquery;
1254 UPDATE test_tsquery SET sample = to_tsquery('english', txtsample::text);
1255 SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york';
1261 SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york';
1267 SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york';
1273 SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york';
1279 SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york';
1285 CREATE UNIQUE INDEX bt_tsq ON test_tsquery (keyword);
1286 SET enable_seqscan=OFF;
1287 SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york';
1293 SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york';
1299 SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york';
1305 SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york';
1311 SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york';
1317 RESET enable_seqscan;
1318 SELECT ts_rewrite('foo & bar & qq & new & york', 'new & york'::tsquery, 'big & apple | nyc | new & york & city');
1320 ------------------------------------------------------------------------------
1321 'foo' & 'bar' & 'qq' & ( 'city' & 'new' & 'york' | 'nyc' | 'big' & 'apple' )
1324 SELECT ts_rewrite(ts_rewrite('new & !york ', 'york', '!jersey'),
1325 'jersey', 'mexico');
1327 --------------------
1331 SELECT ts_rewrite('moscow', 'SELECT keyword, sample FROM test_tsquery'::text );
1333 ---------------------
1337 SELECT ts_rewrite('moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'::text );
1339 -----------------------------------
1340 'hotel' & ( 'moskva' | 'moscow' )
1343 SELECT ts_rewrite('bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery'::text );
1345 ---------------------------------------------------------------------------------
1346 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' & 'appl' | 'new' & 'york' )
1349 SELECT ts_rewrite( 'moscow', 'SELECT keyword, sample FROM test_tsquery');
1351 ---------------------
1355 SELECT ts_rewrite( 'moscow & hotel', 'SELECT keyword, sample FROM test_tsquery');
1357 -----------------------------------
1358 'hotel' & ( 'moskva' | 'moscow' )
1361 SELECT ts_rewrite( 'bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery');
1363 ---------------------------------------------------------------------------------
1364 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' & 'appl' | 'new' & 'york' )
1367 SELECT ts_rewrite('1 & (2 <-> 3)', 'SELECT keyword, sample FROM test_tsquery'::text );
1373 SELECT ts_rewrite('1 & (2 <2> 3)', 'SELECT keyword, sample FROM test_tsquery'::text );
1379 SELECT ts_rewrite('5 <-> (1 & (2 <-> 3))', 'SELECT keyword, sample FROM test_tsquery'::text );
1381 -------------------------
1382 '5' <-> ( '2' <-> '4' )
1385 SELECT ts_rewrite('5 <-> (6 | 8)', 'SELECT keyword, sample FROM test_tsquery'::text );
1387 -----------------------
1388 '5' <-> ( '6' | '8' )
1391 -- Check empty substitution
1392 SELECT ts_rewrite(to_tsquery('5 & (6 | 5)'), to_tsquery('5'), to_tsquery(''));
1393 NOTICE: text-search query doesn't contain lexemes: ""
1399 SELECT ts_rewrite(to_tsquery('!5'), to_tsquery('5'), to_tsquery(''));
1400 NOTICE: text-search query doesn't contain lexemes: ""
1406 SELECT keyword FROM test_tsquery WHERE keyword @> 'new';
1412 SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow';
1418 SELECT keyword FROM test_tsquery WHERE keyword <@ 'new';
1423 SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow';
1429 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
1431 ---------------------
1435 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
1437 -----------------------------------
1438 'hotel' & ( 'moskva' | 'moscow' )
1441 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
1443 ---------------------------------------------------------------------------------
1444 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' & 'appl' | 'new' & 'york' )
1447 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
1449 ---------------------
1453 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
1455 -----------------------------------
1456 'hotel' & ( 'moskva' | 'moscow' )
1459 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
1461 ---------------------------------------------------------------------------------
1462 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' & 'appl' | 'new' & 'york' )
1465 CREATE INDEX qq ON test_tsquery USING gist (keyword tsquery_ops);
1466 SET enable_seqscan=OFF;
1467 SELECT keyword FROM test_tsquery WHERE keyword @> 'new';
1473 SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow';
1479 SELECT keyword FROM test_tsquery WHERE keyword <@ 'new';
1484 SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow';
1490 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
1492 ---------------------
1496 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
1498 -----------------------------------
1499 'hotel' & ( 'moskva' | 'moscow' )
1502 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
1504 ---------------------------------------------------------------------------------
1505 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' & 'appl' | 'new' & 'york' )
1508 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
1510 ---------------------
1514 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
1516 -----------------------------------
1517 'hotel' & ( 'moskva' | 'moscow' )
1520 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
1522 ---------------------------------------------------------------------------------
1523 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' & 'appl' | 'new' & 'york' )
1526 SELECT ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz');
1528 -----------------------------------------
1529 ( 'bar' | 'baz' ) <-> ( 'bar' | 'baz' )
1532 SELECT to_tsvector('foo bar') @@
1533 ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz');
1539 SELECT to_tsvector('bar baz') @@
1540 ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz');
1546 RESET enable_seqscan;
1548 SET default_text_search_config=simple;
1549 SELECT to_tsvector('SKIES My booKs');
1551 ----------------------------
1552 'books':3 'my':2 'skies':1
1555 SELECT plainto_tsquery('SKIES My booKs');
1557 --------------------------
1558 'skies' & 'my' & 'books'
1561 SELECT to_tsquery('SKIES & My | booKs');
1563 --------------------------
1564 'skies' & 'my' | 'books'
1567 SET default_text_search_config=english;
1568 SELECT to_tsvector('SKIES My booKs');
1574 SELECT plainto_tsquery('SKIES My booKs');
1580 SELECT to_tsquery('SKIES & My | booKs');
1587 CREATE TRIGGER tsvectorupdate
1588 BEFORE UPDATE OR INSERT ON test_tsvector
1589 FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(a, 'pg_catalog.english', t);
1590 SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
1596 INSERT INTO test_tsvector (t) VALUES ('345 qwerty');
1597 SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
1603 UPDATE test_tsvector SET t = null WHERE t = '345 qwerty';
1604 SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
1610 INSERT INTO test_tsvector (t) VALUES ('345 qwerty');
1611 SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
1617 -- test finding items in GIN's pending list
1618 create temp table pendtest (ts tsvector);
1619 create index pendtest_idx on pendtest using gin(ts);
1620 insert into pendtest values (to_tsvector('Lore ipsam'));
1621 insert into pendtest values (to_tsvector('Lore ipsum'));
1622 select * from pendtest where 'ipsu:*'::tsquery @@ ts;
1624 --------------------
1628 select * from pendtest where 'ipsa:*'::tsquery @@ ts;
1630 --------------------
1634 select * from pendtest where 'ips:*'::tsquery @@ ts;
1636 --------------------
1641 select * from pendtest where 'ipt:*'::tsquery @@ ts;
1646 select * from pendtest where 'ipi:*'::tsquery @@ ts;
1651 --check OP_PHRASE on index
1652 create temp table phrase_index_test(fts tsvector);
1653 insert into phrase_index_test values ('A fat cat has just eaten a rat.');
1654 insert into phrase_index_test values (to_tsvector('english', 'A fat cat has just eaten a rat.'));
1655 create index phrase_index_test_idx on phrase_index_test using gin(fts);
1656 set enable_seqscan = off;
1657 select * from phrase_index_test where fts @@ phraseto_tsquery('english', 'fat cat');
1659 -----------------------------------
1660 'cat':3 'eaten':6 'fat':2 'rat':8
1663 set enable_seqscan = on;