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 create index wowidx on test_tsvector using gist (a);
96 SET enable_seqscan=OFF;
97 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
103 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
109 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
115 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
121 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
127 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
133 RESET enable_seqscan;
135 CREATE INDEX wowidx ON test_tsvector USING gin (a);
136 SET enable_seqscan=OFF;
137 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
143 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
149 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
155 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
161 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
167 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
174 RESET enable_seqscan;
175 INSERT INTO test_tsvector VALUES ('???', 'DFG:1A,2B,6C,10 FGH');
176 SELECT * FROM ts_stat('SELECT a FROM test_tsvector') ORDER BY ndoc DESC, nentry DESC, word LIMIT 10;
178 ------+------+--------
191 SELECT * FROM ts_stat('SELECT a FROM test_tsvector', 'AB') ORDER BY ndoc DESC, nentry DESC, word;
193 ------+------+--------
197 --dictionaries and to_tsvector
198 SELECT ts_lexize('english_stem', 'skies');
204 SELECT ts_lexize('english_stem', 'identity');
210 SELECT * FROM ts_token_type('default');
211 tokid | alias | description
212 -------+-----------------+------------------------------------------
213 1 | asciiword | Word, all ASCII
214 2 | word | Word, all letters
215 3 | numword | Word, letters and digits
216 4 | email | Email address
219 7 | sfloat | Scientific notation
220 8 | version | Version number
221 9 | hword_numpart | Hyphenated word part, letters and digits
222 10 | hword_part | Hyphenated word part, all letters
223 11 | hword_asciipart | Hyphenated word part, all ASCII
224 12 | blank | Space symbols
226 14 | protocol | Protocol head
227 15 | numhword | Hyphenated word, letters and digits
228 16 | asciihword | Hyphenated word, all ASCII
229 17 | hword | Hyphenated word, all letters
230 18 | url_path | URL path
231 19 | file | File or path name
232 20 | float | Decimal notation
233 21 | int | Signed integer
234 22 | uint | Unsigned integer
235 23 | entity | XML entity
238 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 qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
239 /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
240 <i <b> wow < jqw <> qwerty');
242 -------+--------------------------------------
253 5 | aew.werc.ewr/?ad=qwe&dw
257 5 | 1aew.werc.ewr/?ad=qwe&dw
264 5 | 3aew.werc.ewr/?ad=qwe&dw
272 6 | 5aew.werc.ewr:8100
280 5 | 6aew.werc.ewr:8100/?ad=qwe&dw
281 6 | 6aew.werc.ewr:8100
284 5 | 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32
285 6 | 7aew.werc.ewr:8100
286 18 | /?ad=qwe&dw=%20%32
328 13 | <a href="qwe<qwe>">
378 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 qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
379 /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
380 <i <b> wow < jqw <> qwerty');
382 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
383 'ad':17 'dw':19 'jf':39 '234':61 '345':1 '4.2':54,55,56 '455':31 'jqw':64 'qwe':2,18,27,28,35 'wer':36 'wow':63 '-4.2':58,60 'asdf':37 'ewr1':43 'qwer':38 'sdjk':40 '5.005':32 'efd.r':3 'ewri2':44 'hjwer':42 'qwqwe':29 'wefjn':48 'gist.c':52 'gist.h':50 'qwerti':65 '234.435':30 'qwe-wer':34 'readlin':53,57,59 'www.com':4 '+4.0e-10':26 'gist.h.c':51 'rewt/ewr':47 '/?ad=qwe&dw':7,10,14,22 '/wqe-324/ewr':49 'aew.werc.ewr':6 '1aew.werc.ewr':9 '2aew.werc.ewr':11 '3aew.werc.ewr':13 '4aew.werc.ewr':15 '/usr/local/fff':45 '/awdf/dwqe/4325':46 'teodor@stack.net':33 '/?ad=qwe&dw=%20%32':25 '5aew.werc.ewr:8100':16 '6aew.werc.ewr:8100':21 '7aew.werc.ewr:8100':24 'aew.werc.ewr/?ad=qwe&dw':5 '1aew.werc.ewr/?ad=qwe&dw':8 '3aew.werc.ewr/?ad=qwe&dw':12 '6aew.werc.ewr:8100/?ad=qwe&dw':20 '7aew.werc.ewr:8100/?ad=qwe&dw=%20%32':23
386 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 qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
387 /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
388 <i <b> wow < jqw <> qwerty'));
395 SELECT to_tsquery('english', 'qwe & sKies ');
401 SELECT to_tsquery('simple', 'qwe & sKies ');
407 SELECT to_tsquery('english', '''the wether'':dc & '' sKies '':BC ');
409 ------------------------
410 'wether':CD & 'sky':BC
413 SELECT to_tsquery('english', 'asd&(and|fghj)');
419 SELECT to_tsquery('english', '(asd&and)|fghj');
425 SELECT to_tsquery('english', '(asd&!and)|fghj');
431 SELECT to_tsquery('english', '(the|and&(i&1))&fghj');
437 SELECT plainto_tsquery('english', 'the and z 1))& fghj');
443 SELECT plainto_tsquery('english', 'foo bar') && plainto_tsquery('english', 'asd');
445 -----------------------
446 'foo' & 'bar' & 'asd'
449 SELECT plainto_tsquery('english', 'foo bar') || plainto_tsquery('english', 'asd fg');
451 ------------------------------
452 'foo' & 'bar' | 'asd' & 'fg'
455 SELECT plainto_tsquery('english', 'foo bar') || !!plainto_tsquery('english', 'asd fg');
457 -----------------------------------
458 'foo' & 'bar' | !( 'asd' & 'fg' )
461 SELECT plainto_tsquery('english', 'foo bar') && 'asd | fg';
463 ----------------------------------
464 'foo' & 'bar' & ( 'asd' | 'fg' )
467 SELECT ts_rank_cd(to_tsvector('english', 'Erosion It took the sea a thousand years,
468 A thousand years to trace
469 The granite features of this cliff
470 In crag and scarp and base.
471 It took the sea an hour one night
472 An hour of storm to place
473 The sculpture of these granite seams,
474 Upon a woman s face. E. J. Pratt (1882 1964)
475 '), to_tsquery('english', 'sea&thousand&years'));
481 SELECT ts_rank_cd(to_tsvector('english', 'Erosion It took the sea a thousand years,
482 A thousand years to trace
483 The granite features of this cliff
484 In crag and scarp and base.
485 It took the sea an hour one night
486 An hour of storm to place
487 The sculpture of these granite seams,
488 Upon a woman s face. E. J. Pratt (1882 1964)
489 '), to_tsquery('english', 'granite&sea'));
495 SELECT ts_rank_cd(to_tsvector('english', 'Erosion It took the sea a thousand years,
496 A thousand years to trace
497 The granite features of this cliff
498 In crag and scarp and base.
499 It took the sea an hour one night
500 An hour of storm to place
501 The sculpture of these granite seams,
502 Upon a woman s face. E. J. Pratt (1882 1964)
503 '), to_tsquery('english', 'sea'));
510 SELECT ts_headline('english', 'Erosion It took the sea a thousand years,
511 A thousand years to trace
512 The granite features of this cliff
513 In crag and scarp and base.
514 It took the sea an hour one night
515 An hour of storm to place
516 The sculpture of these granite seams,
517 Upon a woman s face. E. J. Pratt (1882 1964)
518 ', to_tsquery('english', 'sea&thousand&years'));
520 --------------------------------------------
521 <b>sea</b> a <b>thousand</b> <b>years</b>,
522 A <b>thousand</b> <b>years</b> to trace
523 The granite features of this cliff
526 SELECT ts_headline('english', 'Erosion It took the sea a thousand years,
527 A thousand years to trace
528 The granite features of this cliff
529 In crag and scarp and base.
530 It took the sea an hour one night
531 An hour of storm to place
532 The sculpture of these granite seams,
533 Upon a woman s face. E. J. Pratt (1882 1964)
534 ', to_tsquery('english', 'granite&sea'));
536 -------------------------------------------
537 <b>sea</b> a thousand years,
538 A thousand years to trace
539 The <b>granite</b> features of this cliff
542 SELECT ts_headline('english', 'Erosion It took the sea a thousand years,
543 A thousand years to trace
544 The granite features of this cliff
545 In crag and scarp and base.
546 It took the sea an hour one night
547 An hour of storm to place
548 The sculpture of these granite seams,
549 Upon a woman s face. E. J. Pratt (1882 1964)
550 ', to_tsquery('english', 'sea'));
552 ------------------------------------
553 <b>sea</b> a thousand years,
554 A thousand years to trace
555 The granite features of this cliff
558 SELECT ts_headline('english', '
560 <!-- some comment -->
562 Sea view wow <u>foo bar</u> <i>qq</i>
563 <a href="http://www.google.com/foo.bar.html" target="_blank">YES </a>
570 to_tsquery('english', 'sea&foo'), 'HighlightAll=true');
572 -----------------------------------------------------------------------------
575 <!-- some comment -->
577 <b>Sea</b> view wow <u><b>foo</b> bar</u> <i>qq</i>
578 <a href="http://www.google.com/foo.bar.html" target="_blank">YES </a>
588 CREATE TABLE test_tsquery (txtkeyword TEXT, txtsample TEXT);
590 ALTER TABLE test_tsquery ADD COLUMN keyword tsquery;
591 UPDATE test_tsquery SET keyword = to_tsquery('english', txtkeyword);
592 ALTER TABLE test_tsquery ADD COLUMN sample tsquery;
593 UPDATE test_tsquery SET sample = to_tsquery('english', txtsample::text);
594 SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york';
600 SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york';
606 SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york';
612 SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york';
618 SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york';
624 CREATE UNIQUE INDEX bt_tsq ON test_tsquery (keyword);
625 SET enable_seqscan=OFF;
626 SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york';
632 SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york';
638 SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york';
644 SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york';
650 SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york';
656 RESET enable_seqscan;
657 SELECT ts_rewrite('foo & bar & qq & new & york', 'new & york'::tsquery, 'big & apple | nyc | new & york & city');
659 ----------------------------------------------------------------------------------
660 'foo' & 'bar' & 'qq' & ( 'city' & 'new' & 'york' | ( 'nyc' | 'big' & 'apple' ) )
663 SELECT ts_rewrite('moscow', 'SELECT keyword, sample FROM test_tsquery'::text );
665 ---------------------
669 SELECT ts_rewrite('moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'::text );
671 -----------------------------------
672 'hotel' & ( 'moskva' | 'moscow' )
675 SELECT ts_rewrite('bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery'::text );
677 -------------------------------------------------------------------------------------
678 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )
681 SELECT ts_rewrite( 'moscow', 'SELECT keyword, sample FROM test_tsquery');
683 ---------------------
687 SELECT ts_rewrite( 'moscow & hotel', 'SELECT keyword, sample FROM test_tsquery');
689 -----------------------------------
690 'hotel' & ( 'moskva' | 'moscow' )
693 SELECT ts_rewrite( 'bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery');
695 -------------------------------------------------------------------------------------
696 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )
699 SELECT keyword FROM test_tsquery WHERE keyword @> 'new';
705 SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow';
711 SELECT keyword FROM test_tsquery WHERE keyword <@ 'new';
716 SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow';
722 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
724 ---------------------
728 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
730 -----------------------------------
731 'hotel' & ( 'moskva' | 'moscow' )
734 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
736 -------------------------------------------------------------------------------------
737 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )
740 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
742 ---------------------
746 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
748 -----------------------------------
749 'hotel' & ( 'moskva' | 'moscow' )
752 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
754 -------------------------------------------------------------------------------------
755 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )
758 CREATE INDEX qq ON test_tsquery USING gist (keyword tsquery_ops);
759 SET enable_seqscan=OFF;
760 SELECT keyword FROM test_tsquery WHERE keyword @> 'new';
766 SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow';
772 SELECT keyword FROM test_tsquery WHERE keyword <@ 'new';
777 SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow';
783 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
785 ---------------------
789 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
791 -----------------------------------
792 'hotel' & ( 'moskva' | 'moscow' )
795 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
797 -------------------------------------------------------------------------------------
798 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )
801 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
803 ---------------------
807 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
809 -----------------------------------
810 'hotel' & ( 'moskva' | 'moscow' )
813 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
815 -------------------------------------------------------------------------------------
816 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )
819 RESET enable_seqscan;
821 SET default_text_search_config=simple;
822 SELECT to_tsvector('SKIES My booKs');
824 ----------------------------
825 'my':2 'books':3 'skies':1
828 SELECT plainto_tsquery('SKIES My booKs');
830 --------------------------
831 'skies' & 'my' & 'books'
834 SELECT to_tsquery('SKIES & My | booKs');
836 --------------------------
837 'skies' & 'my' | 'books'
840 SET default_text_search_config=english;
841 SELECT to_tsvector('SKIES My booKs');
847 SELECT plainto_tsquery('SKIES My booKs');
853 SELECT to_tsquery('SKIES & My | booKs');
860 CREATE TRIGGER tsvectorupdate
861 BEFORE UPDATE OR INSERT ON test_tsvector
862 FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(a, 'pg_catalog.english', t);
863 SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
869 INSERT INTO test_tsvector (t) VALUES ('345 qwerty');
870 SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
876 UPDATE test_tsvector SET t = null WHERE t = '345 qwerty';
877 SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
883 INSERT INTO test_tsvector (t) VALUES ('345 qwerty');
884 SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');