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 9-- Find unexpected zero link entries 10 11SELECT oid, prsname 12FROM pg_ts_parser 13WHERE prsnamespace = 0 OR prsstart = 0 OR prstoken = 0 OR prsend = 0 OR 14 -- prsheadline is optional 15 prslextype = 0; 16 17SELECT oid, dictname 18FROM pg_ts_dict 19WHERE dictnamespace = 0 OR dictowner = 0 OR dicttemplate = 0; 20 21SELECT oid, tmplname 22FROM pg_ts_template 23WHERE tmplnamespace = 0 OR tmpllexize = 0; -- tmplinit is optional 24 25SELECT oid, cfgname 26FROM pg_ts_config 27WHERE cfgnamespace = 0 OR cfgowner = 0 OR cfgparser = 0; 28 29SELECT mapcfg, maptokentype, mapseqno 30FROM pg_ts_config_map 31WHERE mapcfg = 0 OR mapdict = 0; 32 33-- Look for pg_ts_config_map entries that aren't one of parser's token types 34SELECT * FROM 35 ( SELECT oid AS cfgid, (ts_token_type(cfgparser)).tokid AS tokid 36 FROM pg_ts_config ) AS tt 37RIGHT JOIN pg_ts_config_map AS m 38 ON (tt.cfgid=m.mapcfg AND tt.tokid=m.maptokentype) 39WHERE 40 tt.cfgid IS NULL OR tt.tokid IS NULL; 41 42-- test basic text search behavior without indexes, then with 43 44SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 45SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 46SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 47SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 48SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 49SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 50SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 51SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 52SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 53SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 54SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 55SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 56SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 57SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 58SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 59SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 60SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 61SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 62SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 63SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 64SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:A'; 65SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:D'; 66SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:A'; 67SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:D'; 68 69create index wowidx on test_tsvector using gist (a); 70 71SET enable_seqscan=OFF; 72SET enable_indexscan=ON; 73SET enable_bitmapscan=OFF; 74 75explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 76 77SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 78SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 79SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 80SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 81SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 82SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 83SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 84SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 85SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 86SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 87SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 88SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 89SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 90SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 91SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 92SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 93SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 94SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 95SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 96SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 97SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:A'; 98SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:D'; 99SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:A'; 100SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:D'; 101 102SET enable_indexscan=OFF; 103SET enable_bitmapscan=ON; 104 105explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 106 107SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 108SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 109SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 110SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 111SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 112SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 113SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 114SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 115SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 116SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 117SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 118SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 119SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 120SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 121SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 122SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 123SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 124SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 125SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 126SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 127SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:A'; 128SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:D'; 129SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:A'; 130SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:D'; 131 132-- Test siglen parameter of GiST tsvector_ops 133CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(foo=1)); 134CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(siglen=0)); 135CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(siglen=2048)); 136CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(siglen=100,foo='bar')); 137CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(siglen=100, siglen = 200)); 138 139CREATE INDEX wowidx2 ON test_tsvector USING gist (a tsvector_ops(siglen=1)); 140 141\d test_tsvector 142 143DROP INDEX wowidx; 144 145EXPLAIN (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 146 147SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 148SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 149SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 150SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 151SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 152SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 153SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 154SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 155SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 156SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 157SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 158SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 159SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 160SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 161SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 162SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 163SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 164SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 165SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 166SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 167SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:A'; 168SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:D'; 169SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:A'; 170SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:D'; 171 172DROP INDEX wowidx2; 173 174CREATE INDEX wowidx ON test_tsvector USING gist (a tsvector_ops(siglen=484)); 175 176\d test_tsvector 177 178EXPLAIN (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 179 180SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 181SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 182SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 183SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 184SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 185SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 186SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 187SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 188SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 189SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 190SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 191SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 192SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 193SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 194SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 195SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 196SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 197SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 198SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 199SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 200SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:A'; 201SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:D'; 202SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:A'; 203SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:D'; 204 205RESET enable_seqscan; 206RESET enable_indexscan; 207RESET enable_bitmapscan; 208 209DROP INDEX wowidx; 210 211CREATE INDEX wowidx ON test_tsvector USING gin (a); 212 213SET enable_seqscan=OFF; 214-- GIN only supports bitmapscan, so no need to test plain indexscan 215 216explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 217 218SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 219SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 220SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 221SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 222SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 223SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 224SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 225SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 226SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 227SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 228SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 229SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 230SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 231SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 232SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 233SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 234SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 235SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 236SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 237SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 238SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:A'; 239SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:D'; 240SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:A'; 241SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:D'; 242 243-- Test optimization of non-empty GIN_SEARCH_MODE_ALL queries 244EXPLAIN (COSTS OFF) 245SELECT count(*) FROM test_tsvector WHERE a @@ '!qh'; 246SELECT count(*) FROM test_tsvector WHERE a @@ '!qh'; 247 248EXPLAIN (COSTS OFF) 249SELECT count(*) FROM test_tsvector WHERE a @@ 'wr' AND a @@ '!qh'; 250SELECT count(*) FROM test_tsvector WHERE a @@ 'wr' AND a @@ '!qh'; 251 252RESET enable_seqscan; 253 254INSERT INTO test_tsvector VALUES ('???', 'DFG:1A,2B,6C,10 FGH'); 255SELECT * FROM ts_stat('SELECT a FROM test_tsvector') ORDER BY ndoc DESC, nentry DESC, word LIMIT 10; 256SELECT * FROM ts_stat('SELECT a FROM test_tsvector', 'AB') ORDER BY ndoc DESC, nentry DESC, word; 257 258--dictionaries and to_tsvector 259 260SELECT ts_lexize('english_stem', 'skies'); 261SELECT ts_lexize('english_stem', 'identity'); 262 263SELECT * FROM ts_token_type('default'); 264 265SELECT * 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>"> 266/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 267<i <b> wow < jqw <> qwerty'); 268 269SELECT 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>"> 270/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 271<i <b> wow < jqw <> qwerty'); 272 273SELECT 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>"> 274/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 275<i <b> wow < jqw <> qwerty')); 276 277-- ts_debug 278 279SELECT * from ts_debug('english', '<myns:foo-bar_baz.blurfl>abc&nm1;def©ghiõjkl</myns:foo-bar_baz.blurfl>'); 280 281-- check parsing of URLs 282SELECT * from ts_debug('english', 'http://www.harewoodsolutions.co.uk/press.aspx</span>'); 283SELECT * from ts_debug('english', 'http://aew.wer0c.ewr/id?ad=qwe&dw<span>'); 284SELECT * from ts_debug('english', 'http://5aew.werc.ewr:8100/?'); 285SELECT * from ts_debug('english', '5aew.werc.ewr:8100/?xx'); 286SELECT token, alias, 287 dictionaries, dictionaries is null as dnull, array_dims(dictionaries) as ddims, 288 lexemes, lexemes is null as lnull, array_dims(lexemes) as ldims 289from ts_debug('english', 'a title'); 290 291-- to_tsquery 292 293SELECT to_tsquery('english', 'qwe & sKies '); 294SELECT to_tsquery('simple', 'qwe & sKies '); 295SELECT to_tsquery('english', '''the wether'':dc & '' sKies '':BC '); 296SELECT to_tsquery('english', 'asd&(and|fghj)'); 297SELECT to_tsquery('english', '(asd&and)|fghj'); 298SELECT to_tsquery('english', '(asd&!and)|fghj'); 299SELECT to_tsquery('english', '(the|and&(i&1))&fghj'); 300 301SELECT plainto_tsquery('english', 'the and z 1))& fghj'); 302SELECT plainto_tsquery('english', 'foo bar') && plainto_tsquery('english', 'asd'); 303SELECT plainto_tsquery('english', 'foo bar') || plainto_tsquery('english', 'asd fg'); 304SELECT plainto_tsquery('english', 'foo bar') || !!plainto_tsquery('english', 'asd fg'); 305SELECT plainto_tsquery('english', 'foo bar') && 'asd | fg'; 306 307-- Check stop word deletion, a and s are stop-words 308SELECT to_tsquery('english', '!(a & !b) & c'); 309SELECT to_tsquery('english', '!(a & !b)'); 310 311SELECT to_tsquery('english', '(1 <-> 2) <-> a'); 312SELECT to_tsquery('english', '(1 <-> a) <-> 2'); 313SELECT to_tsquery('english', '(a <-> 1) <-> 2'); 314SELECT to_tsquery('english', 'a <-> (1 <-> 2)'); 315SELECT to_tsquery('english', '1 <-> (a <-> 2)'); 316SELECT to_tsquery('english', '1 <-> (2 <-> a)'); 317 318SELECT to_tsquery('english', '(1 <-> 2) <3> a'); 319SELECT to_tsquery('english', '(1 <-> a) <3> 2'); 320SELECT to_tsquery('english', '(a <-> 1) <3> 2'); 321SELECT to_tsquery('english', 'a <3> (1 <-> 2)'); 322SELECT to_tsquery('english', '1 <3> (a <-> 2)'); 323SELECT to_tsquery('english', '1 <3> (2 <-> a)'); 324 325SELECT to_tsquery('english', '(1 <3> 2) <-> a'); 326SELECT to_tsquery('english', '(1 <3> a) <-> 2'); 327SELECT to_tsquery('english', '(a <3> 1) <-> 2'); 328SELECT to_tsquery('english', 'a <-> (1 <3> 2)'); 329SELECT to_tsquery('english', '1 <-> (a <3> 2)'); 330SELECT to_tsquery('english', '1 <-> (2 <3> a)'); 331 332SELECT to_tsquery('english', '((a <-> 1) <-> 2) <-> s'); 333SELECT to_tsquery('english', '(2 <-> (a <-> 1)) <-> s'); 334SELECT to_tsquery('english', '((1 <-> a) <-> 2) <-> s'); 335SELECT to_tsquery('english', '(2 <-> (1 <-> a)) <-> s'); 336SELECT to_tsquery('english', 's <-> ((a <-> 1) <-> 2)'); 337SELECT to_tsquery('english', 's <-> (2 <-> (a <-> 1))'); 338SELECT to_tsquery('english', 's <-> ((1 <-> a) <-> 2)'); 339SELECT to_tsquery('english', 's <-> (2 <-> (1 <-> a))'); 340 341SELECT to_tsquery('english', '((a <-> 1) <-> s) <-> 2'); 342SELECT to_tsquery('english', '(s <-> (a <-> 1)) <-> 2'); 343SELECT to_tsquery('english', '((1 <-> a) <-> s) <-> 2'); 344SELECT to_tsquery('english', '(s <-> (1 <-> a)) <-> 2'); 345SELECT to_tsquery('english', '2 <-> ((a <-> 1) <-> s)'); 346SELECT to_tsquery('english', '2 <-> (s <-> (a <-> 1))'); 347SELECT to_tsquery('english', '2 <-> ((1 <-> a) <-> s)'); 348SELECT to_tsquery('english', '2 <-> (s <-> (1 <-> a))'); 349 350SELECT to_tsquery('english', 'foo <-> (a <-> (the <-> bar))'); 351SELECT to_tsquery('english', '((foo <-> a) <-> the) <-> bar'); 352SELECT to_tsquery('english', 'foo <-> a <-> the <-> bar'); 353SELECT phraseto_tsquery('english', 'PostgreSQL can be extended by the user in many ways'); 354 355 356SELECT ts_rank_cd(to_tsvector('english', ' 357Day after day, day after day, 358 We stuck, nor breath nor motion, 359As idle as a painted Ship 360 Upon a painted Ocean. 361Water, water, every where 362 And all the boards did shrink; 363Water, water, every where, 364 Nor any drop to drink. 365S. T. Coleridge (1772-1834) 366'), to_tsquery('english', 'paint&water')); 367 368SELECT ts_rank_cd(to_tsvector('english', ' 369Day after day, day after day, 370 We stuck, nor breath nor motion, 371As idle as a painted Ship 372 Upon a painted Ocean. 373Water, water, every where 374 And all the boards did shrink; 375Water, water, every where, 376 Nor any drop to drink. 377S. T. Coleridge (1772-1834) 378'), to_tsquery('english', 'breath&motion&water')); 379 380SELECT ts_rank_cd(to_tsvector('english', ' 381Day after day, day after day, 382 We stuck, nor breath nor motion, 383As idle as a painted Ship 384 Upon a painted Ocean. 385Water, water, every where 386 And all the boards did shrink; 387Water, water, every where, 388 Nor any drop to drink. 389S. T. Coleridge (1772-1834) 390'), to_tsquery('english', 'ocean')); 391 392SELECT ts_rank_cd(to_tsvector('english', ' 393Day after day, day after day, 394 We stuck, nor breath nor motion, 395As idle as a painted Ship 396 Upon a painted Ocean. 397Water, water, every where 398 And all the boards did shrink; 399Water, water, every where, 400 Nor any drop to drink. 401S. T. Coleridge (1772-1834) 402'), to_tsquery('english', 'painted <-> Ship')); 403 404SELECT ts_rank_cd(strip(to_tsvector('both stripped')), 405 to_tsquery('both & stripped')); 406 407SELECT ts_rank_cd(to_tsvector('unstripped') || strip(to_tsvector('stripped')), 408 to_tsquery('unstripped & stripped')); 409 410--headline tests 411SELECT ts_headline('english', ' 412Day after day, day after day, 413 We stuck, nor breath nor motion, 414As idle as a painted Ship 415 Upon a painted Ocean. 416Water, water, every where 417 And all the boards did shrink; 418Water, water, every where, 419 Nor any drop to drink. 420S. T. Coleridge (1772-1834) 421', to_tsquery('english', 'paint&water')); 422 423SELECT ts_headline('english', ' 424Day after day, day after day, 425 We stuck, nor breath nor motion, 426As idle as a painted Ship 427 Upon a painted Ocean. 428Water, water, every where 429 And all the boards did shrink; 430Water, water, every where, 431 Nor any drop to drink. 432S. T. Coleridge (1772-1834) 433', to_tsquery('english', 'breath&motion&water')); 434 435SELECT ts_headline('english', ' 436Day after day, day after day, 437 We stuck, nor breath nor motion, 438As idle as a painted Ship 439 Upon a painted Ocean. 440Water, water, every where 441 And all the boards did shrink; 442Water, water, every where, 443 Nor any drop to drink. 444S. T. Coleridge (1772-1834) 445', to_tsquery('english', 'ocean')); 446 447SELECT ts_headline('english', ' 448Day after day, day after day, 449 We stuck, nor breath nor motion, 450As idle as a painted Ship 451 Upon a painted Ocean. 452Water, water, every where 453 And all the boards did shrink; 454Water, water, every where, 455 Nor any drop to drink. 456S. T. Coleridge (1772-1834) 457', phraseto_tsquery('english', 'painted Ocean')); 458 459SELECT ts_headline('english', ' 460Day after day, day after day, 461 We stuck, nor breath nor motion, 462As idle as a painted Ship 463 Upon a painted Ocean. 464Water, water, every where 465 And all the boards did shrink; 466Water, water, every where, 467 Nor any drop to drink. 468S. T. Coleridge (1772-1834) 469', phraseto_tsquery('english', 'idle as a painted Ship')); 470 471SELECT ts_headline('english', 472'Lorem ipsum urna. Nullam nullam ullamcorper urna.', 473to_tsquery('english','Lorem') && phraseto_tsquery('english','ullamcorper urna'), 474'MaxWords=100, MinWords=1'); 475 476SELECT ts_headline('english', ' 477<html> 478<!-- some comment --> 479<body> 480Sea view wow <u>foo bar</u> <i>qq</i> 481<a href="http://www.google.com/foo.bar.html" target="_blank">YES </a> 482ff-bg 483<script> 484 document.write(15); 485</script> 486</body> 487</html>', 488to_tsquery('english', 'sea&foo'), 'HighlightAll=true'); 489 490SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 <-> 3', 'MaxWords=2, MinWords=1'); 491SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 & 3', 'MaxWords=4, MinWords=1'); 492SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 <-> 3', 'MaxWords=4, MinWords=1'); 493 494--Check if headline fragments work 495SELECT ts_headline('english', ' 496Day after day, day after day, 497 We stuck, nor breath nor motion, 498As idle as a painted Ship 499 Upon a painted Ocean. 500Water, water, every where 501 And all the boards did shrink; 502Water, water, every where, 503 Nor any drop to drink. 504S. T. Coleridge (1772-1834) 505', to_tsquery('english', 'ocean'), 'MaxFragments=1'); 506 507--Check if more than one fragments are displayed 508SELECT ts_headline('english', ' 509Day after day, day after day, 510 We stuck, nor breath nor motion, 511As idle as a painted Ship 512 Upon a painted Ocean. 513Water, water, every where 514 And all the boards did shrink; 515Water, water, every where, 516 Nor any drop to drink. 517S. T. Coleridge (1772-1834) 518', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2'); 519 520--Fragments when there all query words are not in the document 521SELECT ts_headline('english', ' 522Day after day, day after day, 523 We stuck, nor breath nor motion, 524As idle as a painted Ship 525 Upon a painted Ocean. 526Water, water, every where 527 And all the boards did shrink; 528Water, water, every where, 529 Nor any drop to drink. 530S. T. Coleridge (1772-1834) 531', to_tsquery('english', 'ocean & seahorse'), 'MaxFragments=1'); 532 533--FragmentDelimiter option 534SELECT ts_headline('english', ' 535Day after day, day after day, 536 We stuck, nor breath nor motion, 537As idle as a painted Ship 538 Upon a painted Ocean. 539Water, water, every where 540 And all the boards did shrink; 541Water, water, every where, 542 Nor any drop to drink. 543S. T. Coleridge (1772-1834) 544', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2,FragmentDelimiter=***'); 545 546--Fragments with phrase search 547SELECT ts_headline('english', 548'Lorem ipsum urna. Nullam nullam ullamcorper urna.', 549to_tsquery('english','Lorem') && phraseto_tsquery('english','ullamcorper urna'), 550'MaxFragments=100, MaxWords=100, MinWords=1'); 551 552--Rewrite sub system 553 554CREATE TABLE test_tsquery (txtkeyword TEXT, txtsample TEXT); 555\set ECHO none 556\copy test_tsquery from stdin 557'New York' new <-> york | big <-> apple | nyc 558Moscow moskva | moscow 559'Sanct Peter' Peterburg | peter | 'Sanct Peterburg' 560foo & bar & qq foo & (bar | qq) & city 5611 & (2 <-> 3) 2 <-> 4 5625 <-> 6 5 <-> 7 563\. 564\set ECHO all 565 566ALTER TABLE test_tsquery ADD COLUMN keyword tsquery; 567UPDATE test_tsquery SET keyword = to_tsquery('english', txtkeyword); 568ALTER TABLE test_tsquery ADD COLUMN sample tsquery; 569UPDATE test_tsquery SET sample = to_tsquery('english', txtsample::text); 570 571 572SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new <-> york'; 573SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new <-> york'; 574SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new <-> york'; 575SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new <-> york'; 576SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new <-> york'; 577 578CREATE UNIQUE INDEX bt_tsq ON test_tsquery (keyword); 579 580SET enable_seqscan=OFF; 581 582SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new <-> york'; 583SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new <-> york'; 584SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new <-> york'; 585SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new <-> york'; 586SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new <-> york'; 587 588RESET enable_seqscan; 589 590SELECT ts_rewrite('foo & bar & qq & new & york', 'new & york'::tsquery, 'big & apple | nyc | new & york & city'); 591SELECT ts_rewrite(ts_rewrite('new & !york ', 'york', '!jersey'), 592 'jersey', 'mexico'); 593 594SELECT ts_rewrite('moscow', 'SELECT keyword, sample FROM test_tsquery'::text ); 595SELECT ts_rewrite('moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'::text ); 596SELECT ts_rewrite('bar & qq & foo & (new <-> york)', 'SELECT keyword, sample FROM test_tsquery'::text ); 597 598SELECT ts_rewrite( 'moscow', 'SELECT keyword, sample FROM test_tsquery'); 599SELECT ts_rewrite( 'moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'); 600SELECT ts_rewrite( 'bar & qq & foo & (new <-> york)', 'SELECT keyword, sample FROM test_tsquery'); 601 602SELECT ts_rewrite('1 & (2 <-> 3)', 'SELECT keyword, sample FROM test_tsquery'::text ); 603SELECT ts_rewrite('1 & (2 <2> 3)', 'SELECT keyword, sample FROM test_tsquery'::text ); 604SELECT ts_rewrite('5 <-> (1 & (2 <-> 3))', 'SELECT keyword, sample FROM test_tsquery'::text ); 605SELECT ts_rewrite('5 <-> (6 | 8)', 'SELECT keyword, sample FROM test_tsquery'::text ); 606 607-- Check empty substitution 608SELECT ts_rewrite(to_tsquery('5 & (6 | 5)'), to_tsquery('5'), to_tsquery('')); 609SELECT ts_rewrite(to_tsquery('!5'), to_tsquery('5'), to_tsquery('')); 610 611SELECT keyword FROM test_tsquery WHERE keyword @> 'new'; 612SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow'; 613SELECT keyword FROM test_tsquery WHERE keyword <@ 'new'; 614SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow'; 615SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 616SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 617SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & qq & foo & (new <-> york)') AS query; 618SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 619SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 620SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & qq & foo & (new <-> york)') AS query; 621 622CREATE INDEX qq ON test_tsquery USING gist (keyword tsquery_ops); 623SET enable_seqscan=OFF; 624 625SELECT keyword FROM test_tsquery WHERE keyword @> 'new'; 626SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow'; 627SELECT keyword FROM test_tsquery WHERE keyword <@ 'new'; 628SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow'; 629SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 630SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 631SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & qq & foo & (new <-> york)') AS query; 632SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 633SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 634SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & qq & foo & (new <-> york)') AS query; 635 636SELECT ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz'); 637SELECT to_tsvector('foo bar') @@ 638 ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz'); 639SELECT to_tsvector('bar baz') @@ 640 ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz'); 641 642RESET enable_seqscan; 643 644--test GUC 645SET default_text_search_config=simple; 646 647SELECT to_tsvector('SKIES My booKs'); 648SELECT plainto_tsquery('SKIES My booKs'); 649SELECT to_tsquery('SKIES & My | booKs'); 650 651SET default_text_search_config=english; 652 653SELECT to_tsvector('SKIES My booKs'); 654SELECT plainto_tsquery('SKIES My booKs'); 655SELECT to_tsquery('SKIES & My | booKs'); 656 657--trigger 658CREATE TRIGGER tsvectorupdate 659BEFORE UPDATE OR INSERT ON test_tsvector 660FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(a, 'pg_catalog.english', t); 661 662SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 663INSERT INTO test_tsvector (t) VALUES ('345 qwerty'); 664SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 665UPDATE test_tsvector SET t = null WHERE t = '345 qwerty'; 666SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 667 668INSERT INTO test_tsvector (t) VALUES ('345 qwerty'); 669 670SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 671 672-- Test inlining of immutable constant functions 673 674-- to_tsquery(text) is not immutable, so it won't be inlined 675explain (costs off) 676select * from test_tsquery, to_tsquery('new') q where txtsample @@ q; 677 678-- to_tsquery(regconfig, text) is an immutable function. 679-- That allows us to get rid of using function scan and join at all. 680explain (costs off) 681select * from test_tsquery, to_tsquery('english', 'new') q where txtsample @@ q; 682 683-- test finding items in GIN's pending list 684create temp table pendtest (ts tsvector); 685create index pendtest_idx on pendtest using gin(ts); 686insert into pendtest values (to_tsvector('Lore ipsam')); 687insert into pendtest values (to_tsvector('Lore ipsum')); 688select * from pendtest where 'ipsu:*'::tsquery @@ ts; 689select * from pendtest where 'ipsa:*'::tsquery @@ ts; 690select * from pendtest where 'ips:*'::tsquery @@ ts; 691select * from pendtest where 'ipt:*'::tsquery @@ ts; 692select * from pendtest where 'ipi:*'::tsquery @@ ts; 693 694--check OP_PHRASE on index 695create temp table phrase_index_test(fts tsvector); 696insert into phrase_index_test values ('A fat cat has just eaten a rat.'); 697insert into phrase_index_test values (to_tsvector('english', 'A fat cat has just eaten a rat.')); 698create index phrase_index_test_idx on phrase_index_test using gin(fts); 699set enable_seqscan = off; 700select * from phrase_index_test where fts @@ phraseto_tsquery('english', 'fat cat'); 701set enable_seqscan = on; 702 703-- test websearch_to_tsquery function 704select websearch_to_tsquery('simple', 'I have a fat:*ABCD cat'); 705select websearch_to_tsquery('simple', 'orange:**AABBCCDD'); 706select websearch_to_tsquery('simple', 'fat:A!cat:B|rat:C<'); 707select websearch_to_tsquery('simple', 'fat:A : cat:B'); 708 709select websearch_to_tsquery('simple', 'fat*rat'); 710select websearch_to_tsquery('simple', 'fat-rat'); 711select websearch_to_tsquery('simple', 'fat_rat'); 712 713-- weights are completely ignored 714select websearch_to_tsquery('simple', 'abc : def'); 715select websearch_to_tsquery('simple', 'abc:def'); 716select websearch_to_tsquery('simple', 'a:::b'); 717select websearch_to_tsquery('simple', 'abc:d'); 718select websearch_to_tsquery('simple', ':'); 719 720-- these operators are ignored 721select websearch_to_tsquery('simple', 'abc & def'); 722select websearch_to_tsquery('simple', 'abc | def'); 723select websearch_to_tsquery('simple', 'abc <-> def'); 724select websearch_to_tsquery('simple', 'abc (pg or class)'); 725 726-- NOT is ignored in quotes 727select websearch_to_tsquery('english', 'My brand new smartphone'); 728select websearch_to_tsquery('english', 'My brand "new smartphone"'); 729select websearch_to_tsquery('english', 'My brand "new -smartphone"'); 730 731-- test OR operator 732select websearch_to_tsquery('simple', 'cat or rat'); 733select websearch_to_tsquery('simple', 'cat OR rat'); 734select websearch_to_tsquery('simple', 'cat "OR" rat'); 735select websearch_to_tsquery('simple', 'cat OR'); 736select websearch_to_tsquery('simple', 'OR rat'); 737select websearch_to_tsquery('simple', '"fat cat OR rat"'); 738select websearch_to_tsquery('simple', 'fat (cat OR rat'); 739select websearch_to_tsquery('simple', 'or OR or'); 740 741-- OR is an operator here ... 742select websearch_to_tsquery('simple', '"fat cat"or"fat rat"'); 743select websearch_to_tsquery('simple', 'fat or(rat'); 744select websearch_to_tsquery('simple', 'fat or)rat'); 745select websearch_to_tsquery('simple', 'fat or&rat'); 746select websearch_to_tsquery('simple', 'fat or|rat'); 747select websearch_to_tsquery('simple', 'fat or!rat'); 748select websearch_to_tsquery('simple', 'fat or<rat'); 749select websearch_to_tsquery('simple', 'fat or>rat'); 750select websearch_to_tsquery('simple', 'fat or '); 751 752-- ... but not here 753select websearch_to_tsquery('simple', 'abc orange'); 754select websearch_to_tsquery('simple', 'abc OR1234'); 755select websearch_to_tsquery('simple', 'abc or-abc'); 756select websearch_to_tsquery('simple', 'abc OR_abc'); 757 758-- test quotes 759select websearch_to_tsquery('english', '"pg_class pg'); 760select websearch_to_tsquery('english', 'pg_class pg"'); 761select websearch_to_tsquery('english', '"pg_class pg"'); 762select websearch_to_tsquery('english', '"pg_class : pg"'); 763select websearch_to_tsquery('english', 'abc "pg_class pg"'); 764select websearch_to_tsquery('english', '"pg_class pg" def'); 765select websearch_to_tsquery('english', 'abc "pg pg_class pg" def'); 766select websearch_to_tsquery('english', ' or "pg pg_class pg" or '); 767select websearch_to_tsquery('english', '""pg pg_class pg""'); 768select websearch_to_tsquery('english', 'abc """"" def'); 769select websearch_to_tsquery('english', 'cat -"fat rat"'); 770select websearch_to_tsquery('english', 'cat -"fat rat" cheese'); 771select websearch_to_tsquery('english', 'abc "def -"'); 772select websearch_to_tsquery('english', 'abc "def :"'); 773 774select websearch_to_tsquery('english', '"A fat cat" has just eaten a -rat.'); 775select websearch_to_tsquery('english', '"A fat cat" has just eaten OR !rat.'); 776select websearch_to_tsquery('english', '"A fat cat" has just (+eaten OR -rat)'); 777 778select websearch_to_tsquery('english', 'this is ----fine'); 779select websearch_to_tsquery('english', '(()) )))) this ||| is && -fine, "dear friend" OR good'); 780select websearch_to_tsquery('english', 'an old <-> cat " is fine &&& too'); 781 782select websearch_to_tsquery('english', '"A the" OR just on'); 783select websearch_to_tsquery('english', '"a fat cat" ate a rat'); 784 785select to_tsvector('english', 'A fat cat ate a rat') @@ 786 websearch_to_tsquery('english', '"a fat cat" ate a rat'); 787 788select to_tsvector('english', 'A fat grey cat ate a rat') @@ 789 websearch_to_tsquery('english', '"a fat cat" ate a rat'); 790 791-- cases handled by gettoken_tsvector() 792select websearch_to_tsquery(''''); 793select websearch_to_tsquery('''abc''''def'''); 794select websearch_to_tsquery('\abc'); 795select websearch_to_tsquery('\'); 796