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)'; 64 65create index wowidx on test_tsvector using gist (a); 66 67SET enable_seqscan=OFF; 68SET enable_indexscan=ON; 69SET enable_bitmapscan=OFF; 70 71explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 72 73SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 74SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 75SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 76SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 77SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 78SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 79SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 80SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 81SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 82SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 83SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 84SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 85SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 86SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 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 @@ '!(yh <-> pl)'; 92SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 93 94SET enable_indexscan=OFF; 95SET enable_bitmapscan=ON; 96 97explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 98 99SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 100SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 101SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 102SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 103SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 104SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 105SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 106SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 107SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 108SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 109SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 110SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 111SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 112SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 113SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 114SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 115SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 116SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 117SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 118SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 119 120RESET enable_seqscan; 121RESET enable_indexscan; 122RESET enable_bitmapscan; 123 124DROP INDEX wowidx; 125 126CREATE INDEX wowidx ON test_tsvector USING gin (a); 127 128SET enable_seqscan=OFF; 129-- GIN only supports bitmapscan, so no need to test plain indexscan 130 131explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 132 133SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 134SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 135SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 136SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 137SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 138SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 139SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 140SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 141SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 142SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 143SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 144SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 145SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 146SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 147SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 148SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 149SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 150SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 151SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 152SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 153 154RESET enable_seqscan; 155 156INSERT INTO test_tsvector VALUES ('???', 'DFG:1A,2B,6C,10 FGH'); 157SELECT * FROM ts_stat('SELECT a FROM test_tsvector') ORDER BY ndoc DESC, nentry DESC, word LIMIT 10; 158SELECT * FROM ts_stat('SELECT a FROM test_tsvector', 'AB') ORDER BY ndoc DESC, nentry DESC, word; 159 160--dictionaries and to_tsvector 161 162SELECT ts_lexize('english_stem', 'skies'); 163SELECT ts_lexize('english_stem', 'identity'); 164 165SELECT * FROM ts_token_type('default'); 166 167SELECT * 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>"> 168/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 169<i <b> wow < jqw <> qwerty'); 170 171SELECT 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>"> 172/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 173<i <b> wow < jqw <> qwerty'); 174 175SELECT 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>"> 176/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 177<i <b> wow < jqw <> qwerty')); 178 179-- ts_debug 180 181SELECT * from ts_debug('english', '<myns:foo-bar_baz.blurfl>abc&nm1;def©ghiõjkl</myns:foo-bar_baz.blurfl>'); 182 183-- check parsing of URLs 184SELECT * from ts_debug('english', 'http://www.harewoodsolutions.co.uk/press.aspx</span>'); 185SELECT * from ts_debug('english', 'http://aew.wer0c.ewr/id?ad=qwe&dw<span>'); 186SELECT * from ts_debug('english', 'http://5aew.werc.ewr:8100/?'); 187SELECT * from ts_debug('english', '5aew.werc.ewr:8100/?xx'); 188SELECT token, alias, 189 dictionaries, dictionaries is null as dnull, array_dims(dictionaries) as ddims, 190 lexemes, lexemes is null as lnull, array_dims(lexemes) as ldims 191from ts_debug('english', 'a title'); 192 193-- to_tsquery 194 195SELECT to_tsquery('english', 'qwe & sKies '); 196SELECT to_tsquery('simple', 'qwe & sKies '); 197SELECT to_tsquery('english', '''the wether'':dc & '' sKies '':BC '); 198SELECT to_tsquery('english', 'asd&(and|fghj)'); 199SELECT to_tsquery('english', '(asd&and)|fghj'); 200SELECT to_tsquery('english', '(asd&!and)|fghj'); 201SELECT to_tsquery('english', '(the|and&(i&1))&fghj'); 202 203SELECT plainto_tsquery('english', 'the and z 1))& fghj'); 204SELECT plainto_tsquery('english', 'foo bar') && plainto_tsquery('english', 'asd'); 205SELECT plainto_tsquery('english', 'foo bar') || plainto_tsquery('english', 'asd fg'); 206SELECT plainto_tsquery('english', 'foo bar') || !!plainto_tsquery('english', 'asd fg'); 207SELECT plainto_tsquery('english', 'foo bar') && 'asd | fg'; 208 209-- Check stop word deletion, a and s are stop-words 210SELECT to_tsquery('english', '!(a & !b) & c'); 211SELECT to_tsquery('english', '!(a & !b)'); 212 213SELECT to_tsquery('english', '(1 <-> 2) <-> a'); 214SELECT to_tsquery('english', '(1 <-> a) <-> 2'); 215SELECT to_tsquery('english', '(a <-> 1) <-> 2'); 216SELECT to_tsquery('english', 'a <-> (1 <-> 2)'); 217SELECT to_tsquery('english', '1 <-> (a <-> 2)'); 218SELECT to_tsquery('english', '1 <-> (2 <-> a)'); 219 220SELECT to_tsquery('english', '(1 <-> 2) <3> a'); 221SELECT to_tsquery('english', '(1 <-> a) <3> 2'); 222SELECT to_tsquery('english', '(a <-> 1) <3> 2'); 223SELECT to_tsquery('english', 'a <3> (1 <-> 2)'); 224SELECT to_tsquery('english', '1 <3> (a <-> 2)'); 225SELECT to_tsquery('english', '1 <3> (2 <-> a)'); 226 227SELECT to_tsquery('english', '(1 <3> 2) <-> a'); 228SELECT to_tsquery('english', '(1 <3> a) <-> 2'); 229SELECT to_tsquery('english', '(a <3> 1) <-> 2'); 230SELECT to_tsquery('english', 'a <-> (1 <3> 2)'); 231SELECT to_tsquery('english', '1 <-> (a <3> 2)'); 232SELECT to_tsquery('english', '1 <-> (2 <3> a)'); 233 234SELECT to_tsquery('english', '((a <-> 1) <-> 2) <-> s'); 235SELECT to_tsquery('english', '(2 <-> (a <-> 1)) <-> s'); 236SELECT to_tsquery('english', '((1 <-> a) <-> 2) <-> s'); 237SELECT to_tsquery('english', '(2 <-> (1 <-> a)) <-> s'); 238SELECT to_tsquery('english', 's <-> ((a <-> 1) <-> 2)'); 239SELECT to_tsquery('english', 's <-> (2 <-> (a <-> 1))'); 240SELECT to_tsquery('english', 's <-> ((1 <-> a) <-> 2)'); 241SELECT to_tsquery('english', 's <-> (2 <-> (1 <-> a))'); 242 243SELECT to_tsquery('english', '((a <-> 1) <-> s) <-> 2'); 244SELECT to_tsquery('english', '(s <-> (a <-> 1)) <-> 2'); 245SELECT to_tsquery('english', '((1 <-> a) <-> s) <-> 2'); 246SELECT to_tsquery('english', '(s <-> (1 <-> a)) <-> 2'); 247SELECT to_tsquery('english', '2 <-> ((a <-> 1) <-> s)'); 248SELECT to_tsquery('english', '2 <-> (s <-> (a <-> 1))'); 249SELECT to_tsquery('english', '2 <-> ((1 <-> a) <-> s)'); 250SELECT to_tsquery('english', '2 <-> (s <-> (1 <-> a))'); 251 252SELECT to_tsquery('english', 'foo <-> (a <-> (the <-> bar))'); 253SELECT to_tsquery('english', '((foo <-> a) <-> the) <-> bar'); 254SELECT to_tsquery('english', 'foo <-> a <-> the <-> bar'); 255SELECT phraseto_tsquery('english', 'PostgreSQL can be extended by the user in many ways'); 256 257 258SELECT ts_rank_cd(to_tsvector('english', ' 259Day after day, day after day, 260 We stuck, nor breath nor motion, 261As idle as a painted Ship 262 Upon a painted Ocean. 263Water, water, every where 264 And all the boards did shrink; 265Water, water, every where, 266 Nor any drop to drink. 267S. T. Coleridge (1772-1834) 268'), to_tsquery('english', 'paint&water')); 269 270SELECT ts_rank_cd(to_tsvector('english', ' 271Day after day, day after day, 272 We stuck, nor breath nor motion, 273As idle as a painted Ship 274 Upon a painted Ocean. 275Water, water, every where 276 And all the boards did shrink; 277Water, water, every where, 278 Nor any drop to drink. 279S. T. Coleridge (1772-1834) 280'), to_tsquery('english', 'breath&motion&water')); 281 282SELECT ts_rank_cd(to_tsvector('english', ' 283Day after day, day after day, 284 We stuck, nor breath nor motion, 285As idle as a painted Ship 286 Upon a painted Ocean. 287Water, water, every where 288 And all the boards did shrink; 289Water, water, every where, 290 Nor any drop to drink. 291S. T. Coleridge (1772-1834) 292'), to_tsquery('english', 'ocean')); 293 294SELECT ts_rank_cd(to_tsvector('english', ' 295Day after day, day after day, 296 We stuck, nor breath nor motion, 297As idle as a painted Ship 298 Upon a painted Ocean. 299Water, water, every where 300 And all the boards did shrink; 301Water, water, every where, 302 Nor any drop to drink. 303S. T. Coleridge (1772-1834) 304'), to_tsquery('english', 'painted <-> Ship')); 305 306SELECT ts_rank_cd(strip(to_tsvector('both stripped')), 307 to_tsquery('both & stripped')); 308 309SELECT ts_rank_cd(to_tsvector('unstripped') || strip(to_tsvector('stripped')), 310 to_tsquery('unstripped & stripped')); 311 312--headline tests 313SELECT ts_headline('english', ' 314Day after day, day after day, 315 We stuck, nor breath nor motion, 316As idle as a painted Ship 317 Upon a painted Ocean. 318Water, water, every where 319 And all the boards did shrink; 320Water, water, every where, 321 Nor any drop to drink. 322S. T. Coleridge (1772-1834) 323', to_tsquery('english', 'paint&water')); 324 325SELECT ts_headline('english', ' 326Day after day, day after day, 327 We stuck, nor breath nor motion, 328As idle as a painted Ship 329 Upon a painted Ocean. 330Water, water, every where 331 And all the boards did shrink; 332Water, water, every where, 333 Nor any drop to drink. 334S. T. Coleridge (1772-1834) 335', to_tsquery('english', 'breath&motion&water')); 336 337SELECT ts_headline('english', ' 338Day after day, day after day, 339 We stuck, nor breath nor motion, 340As idle as a painted Ship 341 Upon a painted Ocean. 342Water, water, every where 343 And all the boards did shrink; 344Water, water, every where, 345 Nor any drop to drink. 346S. T. Coleridge (1772-1834) 347', to_tsquery('english', 'ocean')); 348 349SELECT ts_headline('english', ' 350Day after day, day after day, 351 We stuck, nor breath nor motion, 352As idle as a painted Ship 353 Upon a painted Ocean. 354Water, water, every where 355 And all the boards did shrink; 356Water, water, every where, 357 Nor any drop to drink. 358S. T. Coleridge (1772-1834) 359', phraseto_tsquery('english', 'painted Ocean')); 360 361SELECT ts_headline('english', ' 362Day after day, day after day, 363 We stuck, nor breath nor motion, 364As idle as a painted Ship 365 Upon a painted Ocean. 366Water, water, every where 367 And all the boards did shrink; 368Water, water, every where, 369 Nor any drop to drink. 370S. T. Coleridge (1772-1834) 371', phraseto_tsquery('english', 'idle as a painted Ship')); 372 373SELECT ts_headline('english', 374'Lorem ipsum urna. Nullam nullam ullamcorper urna.', 375to_tsquery('english','Lorem') && phraseto_tsquery('english','ullamcorper urna'), 376'MaxWords=100, MinWords=1'); 377 378SELECT ts_headline('english', ' 379<html> 380<!-- some comment --> 381<body> 382Sea view wow <u>foo bar</u> <i>qq</i> 383<a href="http://www.google.com/foo.bar.html" target="_blank">YES </a> 384ff-bg 385<script> 386 document.write(15); 387</script> 388</body> 389</html>', 390to_tsquery('english', 'sea&foo'), 'HighlightAll=true'); 391 392SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 <-> 3', 'MaxWords=2, MinWords=1'); 393SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 & 3', 'MaxWords=4, MinWords=1'); 394SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 <-> 3', 'MaxWords=4, MinWords=1'); 395 396--Check if headline fragments work 397SELECT ts_headline('english', ' 398Day after day, day after day, 399 We stuck, nor breath nor motion, 400As idle as a painted Ship 401 Upon a painted Ocean. 402Water, water, every where 403 And all the boards did shrink; 404Water, water, every where, 405 Nor any drop to drink. 406S. T. Coleridge (1772-1834) 407', to_tsquery('english', 'ocean'), 'MaxFragments=1'); 408 409--Check if more than one fragments are displayed 410SELECT ts_headline('english', ' 411Day after day, day after day, 412 We stuck, nor breath nor motion, 413As idle as a painted Ship 414 Upon a painted Ocean. 415Water, water, every where 416 And all the boards did shrink; 417Water, water, every where, 418 Nor any drop to drink. 419S. T. Coleridge (1772-1834) 420', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2'); 421 422--Fragments when there all query words are not in the document 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', 'ocean & seahorse'), 'MaxFragments=1'); 434 435--FragmentDelimiter option 436SELECT ts_headline('english', ' 437Day after day, day after day, 438 We stuck, nor breath nor motion, 439As idle as a painted Ship 440 Upon a painted Ocean. 441Water, water, every where 442 And all the boards did shrink; 443Water, water, every where, 444 Nor any drop to drink. 445S. T. Coleridge (1772-1834) 446', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2,FragmentDelimiter=***'); 447 448--Fragments with phrase search 449SELECT ts_headline('english', 450'Lorem ipsum urna. Nullam nullam ullamcorper urna.', 451to_tsquery('english','Lorem') && phraseto_tsquery('english','ullamcorper urna'), 452'MaxFragments=100, MaxWords=100, MinWords=1'); 453 454--Rewrite sub system 455 456CREATE TABLE test_tsquery (txtkeyword TEXT, txtsample TEXT); 457\set ECHO none 458\copy test_tsquery from stdin 459'New York' new & york | big & apple | nyc 460Moscow moskva | moscow 461'Sanct Peter' Peterburg | peter | 'Sanct Peterburg' 462'foo bar qq' foo & (bar | qq) & city 4631 & (2 <-> 3) 2 <-> 4 4645 <-> 6 5 <-> 7 465\. 466\set ECHO all 467 468ALTER TABLE test_tsquery ADD COLUMN keyword tsquery; 469UPDATE test_tsquery SET keyword = to_tsquery('english', txtkeyword); 470ALTER TABLE test_tsquery ADD COLUMN sample tsquery; 471UPDATE test_tsquery SET sample = to_tsquery('english', txtsample::text); 472 473 474SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york'; 475SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york'; 476SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york'; 477SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york'; 478SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york'; 479 480CREATE UNIQUE INDEX bt_tsq ON test_tsquery (keyword); 481 482SET enable_seqscan=OFF; 483 484SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york'; 485SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york'; 486SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york'; 487SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york'; 488SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york'; 489 490RESET enable_seqscan; 491 492SELECT ts_rewrite('foo & bar & qq & new & york', 'new & york'::tsquery, 'big & apple | nyc | new & york & city'); 493SELECT ts_rewrite(ts_rewrite('new & !york ', 'york', '!jersey'), 494 'jersey', 'mexico'); 495 496SELECT ts_rewrite('moscow', 'SELECT keyword, sample FROM test_tsquery'::text ); 497SELECT ts_rewrite('moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'::text ); 498SELECT ts_rewrite('bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery'::text ); 499 500SELECT ts_rewrite( 'moscow', 'SELECT keyword, sample FROM test_tsquery'); 501SELECT ts_rewrite( 'moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'); 502SELECT ts_rewrite( 'bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery'); 503 504SELECT ts_rewrite('1 & (2 <-> 3)', 'SELECT keyword, sample FROM test_tsquery'::text ); 505SELECT ts_rewrite('1 & (2 <2> 3)', 'SELECT keyword, sample FROM test_tsquery'::text ); 506SELECT ts_rewrite('5 <-> (1 & (2 <-> 3))', 'SELECT keyword, sample FROM test_tsquery'::text ); 507SELECT ts_rewrite('5 <-> (6 | 8)', 'SELECT keyword, sample FROM test_tsquery'::text ); 508 509-- Check empty substitution 510SELECT ts_rewrite(to_tsquery('5 & (6 | 5)'), to_tsquery('5'), to_tsquery('')); 511SELECT ts_rewrite(to_tsquery('!5'), to_tsquery('5'), to_tsquery('')); 512 513SELECT keyword FROM test_tsquery WHERE keyword @> 'new'; 514SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow'; 515SELECT keyword FROM test_tsquery WHERE keyword <@ 'new'; 516SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow'; 517SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 518SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 519SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; 520SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 521SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 522SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; 523 524CREATE INDEX qq ON test_tsquery USING gist (keyword tsquery_ops); 525SET enable_seqscan=OFF; 526 527SELECT keyword FROM test_tsquery WHERE keyword @> 'new'; 528SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow'; 529SELECT keyword FROM test_tsquery WHERE keyword <@ 'new'; 530SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow'; 531SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 532SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 533SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; 534SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 535SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 536SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; 537 538SELECT ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz'); 539SELECT to_tsvector('foo bar') @@ 540 ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz'); 541SELECT to_tsvector('bar baz') @@ 542 ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz'); 543 544RESET enable_seqscan; 545 546--test GUC 547SET default_text_search_config=simple; 548 549SELECT to_tsvector('SKIES My booKs'); 550SELECT plainto_tsquery('SKIES My booKs'); 551SELECT to_tsquery('SKIES & My | booKs'); 552 553SET default_text_search_config=english; 554 555SELECT to_tsvector('SKIES My booKs'); 556SELECT plainto_tsquery('SKIES My booKs'); 557SELECT to_tsquery('SKIES & My | booKs'); 558 559--trigger 560CREATE TRIGGER tsvectorupdate 561BEFORE UPDATE OR INSERT ON test_tsvector 562FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(a, 'pg_catalog.english', t); 563 564SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 565INSERT INTO test_tsvector (t) VALUES ('345 qwerty'); 566SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 567UPDATE test_tsvector SET t = null WHERE t = '345 qwerty'; 568SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 569 570INSERT INTO test_tsvector (t) VALUES ('345 qwerty'); 571 572SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 573 574-- test finding items in GIN's pending list 575create temp table pendtest (ts tsvector); 576create index pendtest_idx on pendtest using gin(ts); 577insert into pendtest values (to_tsvector('Lore ipsam')); 578insert into pendtest values (to_tsvector('Lore ipsum')); 579select * from pendtest where 'ipsu:*'::tsquery @@ ts; 580select * from pendtest where 'ipsa:*'::tsquery @@ ts; 581select * from pendtest where 'ips:*'::tsquery @@ ts; 582select * from pendtest where 'ipt:*'::tsquery @@ ts; 583select * from pendtest where 'ipi:*'::tsquery @@ ts; 584 585--check OP_PHRASE on index 586create temp table phrase_index_test(fts tsvector); 587insert into phrase_index_test values ('A fat cat has just eaten a rat.'); 588insert into phrase_index_test values (to_tsvector('english', 'A fat cat has just eaten a rat.')); 589create index phrase_index_test_idx on phrase_index_test using gin(fts); 590set enable_seqscan = off; 591select * from phrase_index_test where fts @@ phraseto_tsquery('english', 'fat cat'); 592set enable_seqscan = on; 593 594-- test websearch_to_tsquery function 595select websearch_to_tsquery('simple', 'I have a fat:*ABCD cat'); 596select websearch_to_tsquery('simple', 'orange:**AABBCCDD'); 597select websearch_to_tsquery('simple', 'fat:A!cat:B|rat:C<'); 598select websearch_to_tsquery('simple', 'fat:A : cat:B'); 599 600select websearch_to_tsquery('simple', 'fat*rat'); 601select websearch_to_tsquery('simple', 'fat-rat'); 602select websearch_to_tsquery('simple', 'fat_rat'); 603 604-- weights are completely ignored 605select websearch_to_tsquery('simple', 'abc : def'); 606select websearch_to_tsquery('simple', 'abc:def'); 607select websearch_to_tsquery('simple', 'a:::b'); 608select websearch_to_tsquery('simple', 'abc:d'); 609select websearch_to_tsquery('simple', ':'); 610 611-- these operators are ignored 612select websearch_to_tsquery('simple', 'abc & def'); 613select websearch_to_tsquery('simple', 'abc | def'); 614select websearch_to_tsquery('simple', 'abc <-> def'); 615select websearch_to_tsquery('simple', 'abc (pg or class)'); 616 617-- NOT is ignored in quotes 618select websearch_to_tsquery('english', 'My brand new smartphone'); 619select websearch_to_tsquery('english', 'My brand "new smartphone"'); 620select websearch_to_tsquery('english', 'My brand "new -smartphone"'); 621 622-- test OR operator 623select websearch_to_tsquery('simple', 'cat or rat'); 624select websearch_to_tsquery('simple', 'cat OR rat'); 625select websearch_to_tsquery('simple', 'cat "OR" rat'); 626select websearch_to_tsquery('simple', 'cat OR'); 627select websearch_to_tsquery('simple', 'OR rat'); 628select websearch_to_tsquery('simple', '"fat cat OR rat"'); 629select websearch_to_tsquery('simple', 'fat (cat OR rat'); 630select websearch_to_tsquery('simple', 'or OR or'); 631 632-- OR is an operator here ... 633select websearch_to_tsquery('simple', '"fat cat"or"fat rat"'); 634select websearch_to_tsquery('simple', 'fat or(rat'); 635select websearch_to_tsquery('simple', 'fat or)rat'); 636select websearch_to_tsquery('simple', 'fat or&rat'); 637select websearch_to_tsquery('simple', 'fat or|rat'); 638select websearch_to_tsquery('simple', 'fat or!rat'); 639select websearch_to_tsquery('simple', 'fat or<rat'); 640select websearch_to_tsquery('simple', 'fat or>rat'); 641select websearch_to_tsquery('simple', 'fat or '); 642 643-- ... but not here 644select websearch_to_tsquery('simple', 'abc orange'); 645select websearch_to_tsquery('simple', 'abc OR1234'); 646select websearch_to_tsquery('simple', 'abc or-abc'); 647select websearch_to_tsquery('simple', 'abc OR_abc'); 648 649-- test quotes 650select websearch_to_tsquery('english', '"pg_class pg'); 651select websearch_to_tsquery('english', 'pg_class pg"'); 652select websearch_to_tsquery('english', '"pg_class pg"'); 653select websearch_to_tsquery('english', 'abc "pg_class pg"'); 654select websearch_to_tsquery('english', '"pg_class pg" def'); 655select websearch_to_tsquery('english', 'abc "pg pg_class pg" def'); 656select websearch_to_tsquery('english', ' or "pg pg_class pg" or '); 657select websearch_to_tsquery('english', '""pg pg_class pg""'); 658select websearch_to_tsquery('english', 'abc """"" def'); 659select websearch_to_tsquery('english', 'cat -"fat rat"'); 660select websearch_to_tsquery('english', 'cat -"fat rat" cheese'); 661select websearch_to_tsquery('english', 'abc "def -"'); 662select websearch_to_tsquery('english', 'abc "def :"'); 663 664select websearch_to_tsquery('english', '"A fat cat" has just eaten a -rat.'); 665select websearch_to_tsquery('english', '"A fat cat" has just eaten OR !rat.'); 666select websearch_to_tsquery('english', '"A fat cat" has just (+eaten OR -rat)'); 667 668select websearch_to_tsquery('english', 'this is ----fine'); 669select websearch_to_tsquery('english', '(()) )))) this ||| is && -fine, "dear friend" OR good'); 670select websearch_to_tsquery('english', 'an old <-> cat " is fine &&& too'); 671 672select websearch_to_tsquery('english', '"A the" OR just on'); 673select websearch_to_tsquery('english', '"a fat cat" ate a rat'); 674 675select to_tsvector('english', 'A fat cat ate a rat') @@ 676 websearch_to_tsquery('english', '"a fat cat" ate a rat'); 677 678select to_tsvector('english', 'A fat grey cat ate a rat') @@ 679 websearch_to_tsquery('english', '"a fat cat" ate a rat'); 680 681-- cases handled by gettoken_tsvector() 682select websearch_to_tsquery(''''); 683select websearch_to_tsquery('''abc''''def'''); 684select websearch_to_tsquery('\abc'); 685select websearch_to_tsquery('\'); 686