1-- deal with numeric instability of ts_rank 2SET extra_float_digits = 0; 3 4--Base tsvector test 5 6SELECT '1'::tsvector; 7SELECT '1 '::tsvector; 8SELECT ' 1'::tsvector; 9SELECT ' 1 '::tsvector; 10SELECT '1 2'::tsvector; 11SELECT '''1 2'''::tsvector; 12SELECT E'''1 \\''2'''::tsvector; 13SELECT E'''1 \\''2''3'::tsvector; 14SELECT E'''1 \\''2'' 3'::tsvector; 15SELECT E'''1 \\''2'' '' 3'' 4 '::tsvector; 16SELECT $$'\\as' ab\c ab\\c AB\\\c ab\\\\c$$::tsvector; 17SELECT tsvectorin(tsvectorout($$'\\as' ab\c ab\\c AB\\\c ab\\\\c$$::tsvector)); 18SELECT '''w'':4A,3B,2C,1D,5 a:8'; 19SELECT 'a:3A b:2a'::tsvector || 'ba:1234 a:1B'; 20 21--Base tsquery test 22SELECT '1'::tsquery; 23SELECT '1 '::tsquery; 24SELECT ' 1'::tsquery; 25SELECT ' 1 '::tsquery; 26SELECT '''1 2'''::tsquery; 27SELECT E'''1 \\''2'''::tsquery; 28SELECT '!1'::tsquery; 29SELECT '1|2'::tsquery; 30SELECT '1|!2'::tsquery; 31SELECT '!1|2'::tsquery; 32SELECT '!1|!2'::tsquery; 33SELECT '!(!1|!2)'::tsquery; 34SELECT '!(!1|2)'::tsquery; 35SELECT '!(1|!2)'::tsquery; 36SELECT '!(1|2)'::tsquery; 37SELECT '1&2'::tsquery; 38SELECT '!1&2'::tsquery; 39SELECT '1&!2'::tsquery; 40SELECT '!1&!2'::tsquery; 41SELECT '(1&2)'::tsquery; 42SELECT '1&(2)'::tsquery; 43SELECT '!(1)&2'::tsquery; 44SELECT '!(1&2)'::tsquery; 45SELECT '1|2&3'::tsquery; 46SELECT '1|(2&3)'::tsquery; 47SELECT '(1|2)&3'::tsquery; 48SELECT '1|2&!3'::tsquery; 49SELECT '1|!2&3'::tsquery; 50SELECT '!1|2&3'::tsquery; 51SELECT '!1|(2&3)'::tsquery; 52SELECT '!(1|2)&3'::tsquery; 53SELECT '(!1|2)&3'::tsquery; 54SELECT '1|(2|(4|(5|6)))'::tsquery; 55SELECT '1|2|4|5|6'::tsquery; 56SELECT '1&(2&(4&(5&6)))'::tsquery; 57SELECT '1&2&4&5&6'::tsquery; 58SELECT '1&(2&(4&(5|6)))'::tsquery; 59SELECT '1&(2&(4&(5|!6)))'::tsquery; 60SELECT E'1&(''2''&('' 4''&(\\|5 | ''6 \\'' !|&'')))'::tsquery; 61SELECT $$'\\as'$$::tsquery; 62SELECT 'a:* & nbb:*ac | doo:a* | goo'::tsquery; 63SELECT '!!b'::tsquery; 64SELECT '!!!b'::tsquery; 65SELECT '!(!b)'::tsquery; 66SELECT 'a & !!b'::tsquery; 67SELECT '!!a & b'::tsquery; 68SELECT '!!a & !!b'::tsquery; 69 70--comparisons 71SELECT 'a' < 'b & c'::tsquery as "true"; 72SELECT 'a' > 'b & c'::tsquery as "false"; 73SELECT 'a | f' < 'b & c'::tsquery as "false"; 74SELECT 'a | ff' < 'b & c'::tsquery as "false"; 75SELECT 'a | f | g' < 'b & c'::tsquery as "false"; 76 77--concatenation 78SELECT numnode( 'new'::tsquery ); 79SELECT numnode( 'new & york'::tsquery ); 80SELECT numnode( 'new & york | qwery'::tsquery ); 81 82SELECT 'foo & bar'::tsquery && 'asd'; 83SELECT 'foo & bar'::tsquery || 'asd & fg'; 84SELECT 'foo & bar'::tsquery || !!'asd & fg'::tsquery; 85SELECT 'foo & bar'::tsquery && 'asd | fg'; 86SELECT 'a' <-> 'b & d'::tsquery; 87SELECT 'a & g' <-> 'b & d'::tsquery; 88SELECT 'a & g' <-> 'b | d'::tsquery; 89SELECT 'a & g' <-> 'b <-> d'::tsquery; 90SELECT tsquery_phrase('a <3> g', 'b & d', 10); 91 92-- tsvector-tsquery operations 93 94SELECT 'a b:89 ca:23A,64b d:34c'::tsvector @@ 'd:AC & ca' as "true"; 95SELECT 'a b:89 ca:23A,64b d:34c'::tsvector @@ 'd:AC & ca:B' as "true"; 96SELECT 'a b:89 ca:23A,64b d:34c'::tsvector @@ 'd:AC & ca:A' as "true"; 97SELECT 'a b:89 ca:23A,64b d:34c'::tsvector @@ 'd:AC & ca:C' as "false"; 98SELECT 'a b:89 ca:23A,64b d:34c'::tsvector @@ 'd:AC & ca:CB' as "true"; 99SELECT 'a b:89 ca:23A,64b d:34c'::tsvector @@ 'd:AC & c:*C' as "false"; 100SELECT 'a b:89 ca:23A,64b d:34c'::tsvector @@ 'd:AC & c:*CB' as "true"; 101SELECT 'a b:89 ca:23A,64b cb:80c d:34c'::tsvector @@ 'd:AC & c:*C' as "true"; 102SELECT 'a b:89 ca:23A,64c cb:80b d:34c'::tsvector @@ 'd:AC & c:*C' as "true"; 103SELECT 'a b:89 ca:23A,64c cb:80b d:34c'::tsvector @@ 'd:AC & c:*B' as "true"; 104SELECT 'wa:1D wb:2A'::tsvector @@ 'w:*D & w:*A'::tsquery as "true"; 105SELECT 'wa:1D wb:2A'::tsvector @@ 'w:*D <-> w:*A'::tsquery as "true"; 106SELECT 'wa:1A wb:2D'::tsvector @@ 'w:*D <-> w:*A'::tsquery as "false"; 107SELECT 'wa:1A'::tsvector @@ 'w:*A'::tsquery as "true"; 108SELECT 'wa:1A'::tsvector @@ 'w:*D'::tsquery as "false"; 109SELECT 'wa:1A'::tsvector @@ '!w:*A'::tsquery as "false"; 110SELECT 'wa:1A'::tsvector @@ '!w:*D'::tsquery as "true"; 111-- historically, a stripped tsvector matches queries ignoring weights: 112SELECT strip('wa:1A'::tsvector) @@ 'w:*A'::tsquery as "true"; 113SELECT strip('wa:1A'::tsvector) @@ 'w:*D'::tsquery as "true"; 114SELECT strip('wa:1A'::tsvector) @@ '!w:*A'::tsquery as "false"; 115SELECT strip('wa:1A'::tsvector) @@ '!w:*D'::tsquery as "false"; 116 117SELECT 'supernova'::tsvector @@ 'super'::tsquery AS "false"; 118SELECT 'supeanova supernova'::tsvector @@ 'super'::tsquery AS "false"; 119SELECT 'supeznova supernova'::tsvector @@ 'super'::tsquery AS "false"; 120SELECT 'supernova'::tsvector @@ 'super:*'::tsquery AS "true"; 121SELECT 'supeanova supernova'::tsvector @@ 'super:*'::tsquery AS "true"; 122SELECT 'supeznova supernova'::tsvector @@ 'super:*'::tsquery AS "true"; 123 124--phrase search 125SELECT to_tsvector('simple', '1 2 3 1') @@ '1 <-> 2' AS "true"; 126SELECT to_tsvector('simple', '1 2 3 1') @@ '1 <2> 2' AS "false"; 127SELECT to_tsvector('simple', '1 2 3 1') @@ '1 <-> 3' AS "false"; 128SELECT to_tsvector('simple', '1 2 3 1') @@ '1 <2> 3' AS "true"; 129SELECT to_tsvector('simple', '1 2 1 2') @@ '1 <3> 2' AS "true"; 130 131SELECT to_tsvector('simple', '1 2 11 3') @@ '1 <-> 3' AS "false"; 132SELECT to_tsvector('simple', '1 2 11 3') @@ '1:* <-> 3' AS "true"; 133 134SELECT to_tsvector('simple', '1 2 3 4') @@ '1 <-> 2 <-> 3' AS "true"; 135SELECT to_tsvector('simple', '1 2 3 4') @@ '(1 <-> 2) <-> 3' AS "true"; 136SELECT to_tsvector('simple', '1 2 3 4') @@ '1 <-> (2 <-> 3)' AS "true"; 137SELECT to_tsvector('simple', '1 2 3 4') @@ '1 <2> (2 <-> 3)' AS "false"; 138SELECT to_tsvector('simple', '1 2 1 2 3 4') @@ '(1 <-> 2) <-> 3' AS "true"; 139SELECT to_tsvector('simple', '1 2 1 2 3 4') @@ '1 <-> 2 <-> 3' AS "true"; 140-- without position data, phrase search does not match 141SELECT strip(to_tsvector('simple', '1 2 3 4')) @@ '1 <-> 2 <-> 3' AS "false"; 142 143select to_tsvector('simple', 'q x q y') @@ 'q <-> (x & y)' AS "false"; 144select to_tsvector('simple', 'q x') @@ 'q <-> (x | y <-> z)' AS "true"; 145select to_tsvector('simple', 'q y') @@ 'q <-> (x | y <-> z)' AS "false"; 146select to_tsvector('simple', 'q y z') @@ 'q <-> (x | y <-> z)' AS "true"; 147select to_tsvector('simple', 'q y x') @@ 'q <-> (x | y <-> z)' AS "false"; 148select to_tsvector('simple', 'q x y') @@ 'q <-> (x | y <-> z)' AS "true"; 149select to_tsvector('simple', 'q x') @@ '(x | y <-> z) <-> q' AS "false"; 150select to_tsvector('simple', 'x q') @@ '(x | y <-> z) <-> q' AS "true"; 151select to_tsvector('simple', 'x y q') @@ '(x | y <-> z) <-> q' AS "false"; 152select to_tsvector('simple', 'x y z') @@ '(x | y <-> z) <-> q' AS "false"; 153select to_tsvector('simple', 'x y z q') @@ '(x | y <-> z) <-> q' AS "true"; 154select to_tsvector('simple', 'y z q') @@ '(x | y <-> z) <-> q' AS "true"; 155select to_tsvector('simple', 'y y q') @@ '(x | y <-> z) <-> q' AS "false"; 156select to_tsvector('simple', 'y y q') @@ '(!x | y <-> z) <-> q' AS "true"; 157select to_tsvector('simple', 'x y q') @@ '(!x | y <-> z) <-> q' AS "true"; 158select to_tsvector('simple', 'y y q') @@ '(x | y <-> !z) <-> q' AS "true"; 159select to_tsvector('simple', 'x q') @@ '(x | y <-> !z) <-> q' AS "true"; 160select to_tsvector('simple', 'x q') @@ '(!x | y <-> z) <-> q' AS "false"; 161select to_tsvector('simple', 'z q') @@ '(!x | y <-> z) <-> q' AS "true"; 162select to_tsvector('simple', 'x y q') @@ '(!x | y) <-> y <-> q' AS "false"; 163select to_tsvector('simple', 'x y q') @@ '(!x | !y) <-> y <-> q' AS "true"; 164select to_tsvector('simple', 'x y q') @@ '(x | !y) <-> y <-> q' AS "true"; 165select to_tsvector('simple', 'x y q') @@ '(x | !!z) <-> y <-> q' AS "true"; 166select to_tsvector('simple', 'x y q y') @@ '!x <-> y' AS "true"; 167select to_tsvector('simple', 'x y q y') @@ '!x <-> !y' AS "true"; 168select to_tsvector('simple', 'x y q y') @@ '!x <-> !!y' AS "true"; 169select to_tsvector('simple', 'x y q y') @@ '!(x <-> y)' AS "false"; 170select to_tsvector('simple', 'x y q y') @@ '!(x <2> y)' AS "true"; 171select strip(to_tsvector('simple', 'x y q y')) @@ '!x <-> y' AS "false"; 172select strip(to_tsvector('simple', 'x y q y')) @@ '!x <-> !y' AS "false"; 173select strip(to_tsvector('simple', 'x y q y')) @@ '!x <-> !!y' AS "false"; 174select strip(to_tsvector('simple', 'x y q y')) @@ '!(x <-> y)' AS "true"; 175select strip(to_tsvector('simple', 'x y q y')) @@ '!(x <2> y)' AS "true"; 176select to_tsvector('simple', 'x y q y') @@ '!foo' AS "true"; 177select to_tsvector('simple', '') @@ '!foo' AS "true"; 178 179--ranking 180SELECT ts_rank(' a:1 s:2C d g'::tsvector, 'a | s'); 181SELECT ts_rank(' a:1 sa:2C d g'::tsvector, 'a | s'); 182SELECT ts_rank(' a:1 sa:2C d g'::tsvector, 'a | s:*'); 183SELECT ts_rank(' a:1 sa:2C d g'::tsvector, 'a | sa:*'); 184SELECT ts_rank(' a:1 s:2B d g'::tsvector, 'a | s'); 185SELECT ts_rank(' a:1 s:2 d g'::tsvector, 'a | s'); 186SELECT ts_rank(' a:1 s:2C d g'::tsvector, 'a & s'); 187SELECT ts_rank(' a:1 s:2B d g'::tsvector, 'a & s'); 188SELECT ts_rank(' a:1 s:2 d g'::tsvector, 'a & s'); 189 190SELECT ts_rank_cd(' a:1 s:2C d g'::tsvector, 'a | s'); 191SELECT ts_rank_cd(' a:1 sa:2C d g'::tsvector, 'a | s'); 192SELECT ts_rank_cd(' a:1 sa:2C d g'::tsvector, 'a | s:*'); 193SELECT ts_rank_cd(' a:1 sa:2C d g'::tsvector, 'a | sa:*'); 194SELECT ts_rank_cd(' a:1 sa:3C sab:2c d g'::tsvector, 'a | sa:*'); 195SELECT ts_rank_cd(' a:1 s:2B d g'::tsvector, 'a | s'); 196SELECT ts_rank_cd(' a:1 s:2 d g'::tsvector, 'a | s'); 197SELECT ts_rank_cd(' a:1 s:2C d g'::tsvector, 'a & s'); 198SELECT ts_rank_cd(' a:1 s:2B d g'::tsvector, 'a & s'); 199SELECT ts_rank_cd(' a:1 s:2 d g'::tsvector, 'a & s'); 200 201SELECT ts_rank_cd(' a:1 s:2A d g'::tsvector, 'a <-> s'); 202SELECT ts_rank_cd(' a:1 s:2C d g'::tsvector, 'a <-> s'); 203SELECT ts_rank_cd(' a:1 s:2 d g'::tsvector, 'a <-> s'); 204SELECT ts_rank_cd(' a:1 s:2 d:2A g'::tsvector, 'a <-> s'); 205SELECT ts_rank_cd(' a:1 s:2,3A d:2A g'::tsvector, 'a <2> s:A'); 206SELECT ts_rank_cd(' a:1 b:2 s:3A d:2A g'::tsvector, 'a <2> s:A'); 207SELECT ts_rank_cd(' a:1 sa:2D sb:2A g'::tsvector, 'a <-> s:*'); 208SELECT ts_rank_cd(' a:1 sa:2A sb:2D g'::tsvector, 'a <-> s:*'); 209SELECT ts_rank_cd(' a:1 sa:2A sb:2D g'::tsvector, 'a <-> s:* <-> sa:A'); 210SELECT ts_rank_cd(' a:1 sa:2A sb:2D g'::tsvector, 'a <-> s:* <-> sa:B'); 211 212SELECT 'a:1 b:2'::tsvector @@ 'a <-> b'::tsquery AS "true"; 213SELECT 'a:1 b:2'::tsvector @@ 'a <0> b'::tsquery AS "false"; 214SELECT 'a:1 b:2'::tsvector @@ 'a <1> b'::tsquery AS "true"; 215SELECT 'a:1 b:2'::tsvector @@ 'a <2> b'::tsquery AS "false"; 216SELECT 'a:1 b:3'::tsvector @@ 'a <-> b'::tsquery AS "false"; 217SELECT 'a:1 b:3'::tsvector @@ 'a <0> b'::tsquery AS "false"; 218SELECT 'a:1 b:3'::tsvector @@ 'a <1> b'::tsquery AS "false"; 219SELECT 'a:1 b:3'::tsvector @@ 'a <2> b'::tsquery AS "true"; 220SELECT 'a:1 b:3'::tsvector @@ 'a <3> b'::tsquery AS "false"; 221SELECT 'a:1 b:3'::tsvector @@ 'a <0> a:*'::tsquery AS "true"; 222 223-- tsvector editing operations 224 225SELECT strip('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd'::tsvector); 226SELECT strip('base:7 hidden:6 rebel:1 spaceship:2,33A,34B,35C,36D strike:3'::tsvector); 227SELECT strip('base hidden rebel spaceship strike'::tsvector); 228 229SELECT ts_delete(to_tsvector('english', 'Rebel spaceships, striking from a hidden base'), 'spaceship'); 230SELECT ts_delete('base:7 hidden:6 rebel:1 spaceship:2,33A,34B,35C,36D strike:3'::tsvector, 'base'); 231SELECT ts_delete('base:7 hidden:6 rebel:1 spaceship:2,33A,34B,35C,36D strike:3'::tsvector, 'bas'); 232SELECT ts_delete('base:7 hidden:6 rebel:1 spaceship:2,33A,34B,35C,36D strike:3'::tsvector, 'bases'); 233SELECT ts_delete('base:7 hidden:6 rebel:1 spaceship:2,33A,34B,35C,36D strike:3'::tsvector, 'spaceship'); 234SELECT ts_delete('base hidden rebel spaceship strike'::tsvector, 'spaceship'); 235 236SELECT ts_delete('base:7 hidden:6 rebel:1 spaceship:2,33A,34B,35C,36D strike:3'::tsvector, ARRAY['spaceship','rebel']); 237SELECT ts_delete('base:7 hidden:6 rebel:1 spaceship:2,33A,34B,35C,36D strike:3'::tsvector, ARRAY['spaceships','rebel']); 238SELECT ts_delete('base:7 hidden:6 rebel:1 spaceship:2,33A,34B,35C,36D strike:3'::tsvector, ARRAY['spaceshi','rebel']); 239SELECT ts_delete('base:7 hidden:6 rebel:1 spaceship:2,33A,34B,35C,36D strike:3'::tsvector, ARRAY['spaceship','leya','rebel']); 240SELECT ts_delete('base hidden rebel spaceship strike'::tsvector, ARRAY['spaceship','leya','rebel']); 241SELECT ts_delete('base hidden rebel spaceship strike'::tsvector, ARRAY['spaceship','leya','rebel','rebel']); 242SELECT ts_delete('base hidden rebel spaceship strike'::tsvector, ARRAY['spaceship','leya','rebel', NULL]); 243 244SELECT unnest('base:7 hidden:6 rebel:1 spaceship:2,33A,34B,35C,36D strike:3'::tsvector); 245SELECT unnest('base hidden rebel spaceship strike'::tsvector); 246SELECT * FROM unnest('base:7 hidden:6 rebel:1 spaceship:2,33A,34B,35C,36D strike:3'::tsvector); 247SELECT * FROM unnest('base hidden rebel spaceship strike'::tsvector); 248SELECT lexeme, positions[1] from unnest('base:7 hidden:6 rebel:1 spaceship:2,33A,34B,35C,36D strike:3'::tsvector); 249 250SELECT tsvector_to_array('base:7 hidden:6 rebel:1 spaceship:2,33A,34B,35C,36D strike:3'::tsvector); 251SELECT tsvector_to_array('base hidden rebel spaceship strike'::tsvector); 252 253SELECT array_to_tsvector(ARRAY['base','hidden','rebel','spaceship','strike']); 254SELECT array_to_tsvector(ARRAY['base','hidden','rebel','spaceship', NULL]); 255-- array_to_tsvector must sort and de-dup 256SELECT array_to_tsvector(ARRAY['foo','bar','baz','bar']); 257 258SELECT setweight('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd zxc:81,567,222A'::tsvector, 'c'); 259SELECT setweight('a:1,3A asd:1C w:5,6,12B,13A zxc:81,222A,567'::tsvector, 'c'); 260SELECT setweight('a:1,3A asd:1C w:5,6,12B,13A zxc:81,222A,567'::tsvector, 'c', '{a}'); 261SELECT setweight('a:1,3A asd:1C w:5,6,12B,13A zxc:81,222A,567'::tsvector, 'c', '{a}'); 262SELECT setweight('a:1,3A asd:1C w:5,6,12B,13A zxc:81,222A,567'::tsvector, 'c', '{a,zxc}'); 263SELECT setweight('a asd w:5,6,12B,13A zxc'::tsvector, 'c', '{a,zxc}'); 264SELECT setweight('a asd w:5,6,12B,13A zxc'::tsvector, 'c', ARRAY['a', 'zxc', NULL]); 265 266SELECT ts_filter('base:7A empir:17 evil:15 first:11 galact:16 hidden:6A rebel:1A spaceship:2A strike:3A victori:12 won:9'::tsvector, '{a}'); 267SELECT ts_filter('base hidden rebel spaceship strike'::tsvector, '{a}'); 268SELECT ts_filter('base hidden rebel spaceship strike'::tsvector, '{a,b,NULL}'); 269