2 -- first, define the datatype. Turn off echoing so that expected file
3 -- does not depend on contents of tsearch2.sql.
5 SET client_min_messages = warning;
9 RESET client_min_messages;
13 SELECT '1 '::tsvector;
14 SELECT ' 1'::tsvector;
15 SELECT ' 1 '::tsvector;
16 SELECT '1 2'::tsvector;
17 SELECT '''1 2'''::tsvector;
18 SELECT E'''1 \\''2'''::tsvector;
19 SELECT E'''1 \\''2''3'::tsvector;
20 SELECT E'''1 \\''2'' 3'::tsvector;
21 SELECT E'''1 \\''2'' '' 3'' 4 '::tsvector;
22 select '''w'':4A,3B,2C,1D,5 a:8';
23 select 'a:3A b:2a'::tsvector || 'ba:1234 a:1B';
24 select setweight('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd zxc:81,567,222A'::tsvector, 'c');
25 select strip('w:12B w:13* w:12,5,6 a:1,3* a:3 w asd:1dc asd'::tsvector);
32 SELECT ' 1 '::tsquery;
33 SELECT '''1 2'''::tsquery;
34 SELECT E'''1 \\''2'''::tsquery;
36 SELECT '1|2'::tsquery;
37 SELECT '1|!2'::tsquery;
38 SELECT '!1|2'::tsquery;
39 SELECT '!1|!2'::tsquery;
40 SELECT '!(!1|!2)'::tsquery;
41 SELECT '!(!1|2)'::tsquery;
42 SELECT '!(1|!2)'::tsquery;
43 SELECT '!(1|2)'::tsquery;
44 SELECT '1&2'::tsquery;
45 SELECT '!1&2'::tsquery;
46 SELECT '1&!2'::tsquery;
47 SELECT '!1&!2'::tsquery;
48 SELECT '(1&2)'::tsquery;
49 SELECT '1&(2)'::tsquery;
50 SELECT '!(1)&2'::tsquery;
51 SELECT '!(1&2)'::tsquery;
52 SELECT '1|2&3'::tsquery;
53 SELECT '1|(2&3)'::tsquery;
54 SELECT '(1|2)&3'::tsquery;
55 SELECT '1|2&!3'::tsquery;
56 SELECT '1|!2&3'::tsquery;
57 SELECT '!1|2&3'::tsquery;
58 SELECT '!1|(2&3)'::tsquery;
59 SELECT '!(1|2)&3'::tsquery;
60 SELECT '(!1|2)&3'::tsquery;
61 SELECT '1|(2|(4|(5|6)))'::tsquery;
62 SELECT '1|2|4|5|6'::tsquery;
63 SELECT '1&(2&(4&(5&6)))'::tsquery;
64 SELECT '1&2&4&5&6'::tsquery;
65 SELECT '1&(2&(4&(5|6)))'::tsquery;
66 SELECT '1&(2&(4&(5|!6)))'::tsquery;
67 SELECT E'1&(''2''&('' 4''&(\\|5 | ''6 \\'' !|&'')))'::tsquery;
68 SELECT '''the wether'':dc & '' sKies '':BC & a:d b:a';
70 select 'a' < 'b & c'::tsquery;
71 select 'a' > 'b & c'::tsquery;
72 select 'a | f' < 'b & c'::tsquery;
73 select 'a | ff' < 'b & c'::tsquery;
74 select 'a | f | g' < 'b & c'::tsquery;
76 select numnode( 'new'::tsquery );
77 select numnode( 'new & york'::tsquery );
78 select numnode( 'new & york | qwery'::tsquery );
80 create table test_tsquery (txtkeyword text, txtsample text);
82 \copy test_tsquery from stdin
83 'New York' new & york | big & apple | nyc
84 Moscow moskva | moscow
85 'Sanct Peter' Peterburg | peter | 'Sanct Peterburg'
86 'foo bar qq' foo & (bar | qq) & city
90 alter table test_tsquery add column keyword tsquery;
91 update test_tsquery set keyword = to_tsquery('english', txtkeyword);
92 alter table test_tsquery add column sample tsquery;
93 update test_tsquery set sample = to_tsquery('english', txtsample::text);
95 create unique index bt_tsq on test_tsquery (keyword);
97 select count(*) from test_tsquery where keyword < 'new & york';
98 select count(*) from test_tsquery where keyword <= 'new & york';
99 select count(*) from test_tsquery where keyword = 'new & york';
100 select count(*) from test_tsquery where keyword >= 'new & york';
101 select count(*) from test_tsquery where keyword > 'new & york';
103 set enable_seqscan=off;
105 select count(*) from test_tsquery where keyword < 'new & york';
106 select count(*) from test_tsquery where keyword <= 'new & york';
107 select count(*) from test_tsquery where keyword = 'new & york';
108 select count(*) from test_tsquery where keyword >= 'new & york';
109 select count(*) from test_tsquery where keyword > 'new & york';
111 set enable_seqscan=on;
113 select rewrite('foo & bar & qq & new & york', 'new & york'::tsquery, 'big & apple | nyc | new & york & city');
115 select rewrite('moscow', 'select keyword, sample from test_tsquery'::text );
116 select rewrite('moscow & hotel', 'select keyword, sample from test_tsquery'::text );
117 select rewrite('bar & new & qq & foo & york', 'select keyword, sample from test_tsquery'::text );
119 select rewrite( ARRAY['moscow', keyword, sample] ) from test_tsquery;
120 select rewrite( ARRAY['moscow & hotel', keyword, sample] ) from test_tsquery;
121 select rewrite( ARRAY['bar & new & qq & foo & york', keyword, sample] ) from test_tsquery;
124 select keyword from test_tsquery where keyword @> 'new';
125 select keyword from test_tsquery where keyword @> 'moscow';
126 select keyword from test_tsquery where keyword <@ 'new';
127 select keyword from test_tsquery where keyword <@ 'moscow';
128 select rewrite( ARRAY[query, keyword, sample] ) from test_tsquery, to_tsquery('english', 'moscow') as query where keyword <@ query;
129 select rewrite( ARRAY[query, keyword, sample] ) from test_tsquery, to_tsquery('english', 'moscow & hotel') as query where keyword <@ query;
130 select rewrite( ARRAY[query, keyword, sample] ) from test_tsquery, to_tsquery('english', 'bar & new & qq & foo & york') as query where keyword <@ query;
131 select rewrite( ARRAY[query, keyword, sample] ) from test_tsquery, to_tsquery('english', 'moscow') as query where query @> keyword;
132 select rewrite( ARRAY[query, keyword, sample] ) from test_tsquery, to_tsquery('english', 'moscow & hotel') as query where query @> keyword;
133 select rewrite( ARRAY[query, keyword, sample] ) from test_tsquery, to_tsquery('english', 'bar & new & qq & foo & york') as query where query @> keyword;
135 create index qq on test_tsquery using gist (keyword gist_tp_tsquery_ops);
136 set enable_seqscan='off';
138 select keyword from test_tsquery where keyword @> 'new';
139 select keyword from test_tsquery where keyword @> 'moscow';
140 select keyword from test_tsquery where keyword <@ 'new';
141 select keyword from test_tsquery where keyword <@ 'moscow';
142 select rewrite( ARRAY[query, keyword, sample] ) from test_tsquery, to_tsquery('english', 'moscow') as query where keyword <@ query;
143 select rewrite( ARRAY[query, keyword, sample] ) from test_tsquery, to_tsquery('english', 'moscow & hotel') as query where keyword <@ query;
144 select rewrite( ARRAY[query, keyword, sample] ) from test_tsquery, to_tsquery('english', 'bar & new & qq & foo & york') as query where keyword <@ query;
145 select rewrite( ARRAY[query, keyword, sample] ) from test_tsquery, to_tsquery('english', 'moscow') as query where query @> keyword;
146 select rewrite( ARRAY[query, keyword, sample] ) from test_tsquery, to_tsquery('english', 'moscow & hotel') as query where query @> keyword;
147 select rewrite( ARRAY[query, keyword, sample] ) from test_tsquery, to_tsquery('english', 'bar & new & qq & foo & york') as query where query @> keyword;
148 set enable_seqscan='on';
152 select lexize('simple', 'ASD56 hsdkf');
153 select lexize('english_stem', 'SKIES Problems identity');
155 select * from token_type('default');
156 select * from 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>">
157 /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
158 <i <b> wow < jqw <> qwerty');
160 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>">
161 /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
162 <i <b> wow < jqw <> qwerty');
164 SELECT length(to_tsvector('english', '345 qw'));
166 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>">
167 /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
168 <i <b> wow < jqw <> qwerty'));
171 select to_tsquery('english', 'qwe & sKies ');
172 select to_tsquery('simple', 'qwe & sKies ');
173 select to_tsquery('english', '''the wether'':dc & '' sKies '':BC ');
174 select to_tsquery('english', 'asd&(and|fghj)');
175 select to_tsquery('english', '(asd&and)|fghj');
176 select to_tsquery('english', '(asd&!and)|fghj');
177 select to_tsquery('english', '(the|and&(i&1))&fghj');
179 select plainto_tsquery('english', 'the and z 1))& fghj');
180 select plainto_tsquery('english', 'foo bar') && plainto_tsquery('english', 'asd');
181 select plainto_tsquery('english', 'foo bar') || plainto_tsquery('english', 'asd fg');
182 select plainto_tsquery('english', 'foo bar') || !!plainto_tsquery('english', 'asd fg');
183 select plainto_tsquery('english', 'foo bar') && 'asd | fg';
185 select 'a b:89 ca:23A,64b d:34c'::tsvector @@ 'd:AC & ca';
186 select 'a b:89 ca:23A,64b d:34c'::tsvector @@ 'd:AC & ca:B';
187 select 'a b:89 ca:23A,64b d:34c'::tsvector @@ 'd:AC & ca:A';
188 select 'a b:89 ca:23A,64b d:34c'::tsvector @@ 'd:AC & ca:C';
189 select 'a b:89 ca:23A,64b d:34c'::tsvector @@ 'd:AC & ca:CB';
191 CREATE TABLE test_tsvector( t text, a tsvector );
193 \copy test_tsvector from 'data/test_tsearch.data'
195 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
196 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
197 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
198 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
199 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
200 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
202 create index wowidx on test_tsvector using gist (a);
203 set enable_seqscan=off;
205 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
206 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
207 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
208 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
209 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
210 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
212 select set_curcfg('english');
214 CREATE TRIGGER tsvectorupdate
215 BEFORE UPDATE OR INSERT ON test_tsvector
216 FOR EACH ROW EXECUTE PROCEDURE tsearch2(a, t);
218 SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
220 INSERT INTO test_tsvector (t) VALUES ('345 qwerty');
222 SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
224 UPDATE test_tsvector SET t = null WHERE t = '345 qwerty';
226 SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
228 insert into test_tsvector (t) values ('345 qwerty copyright');
229 select count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
230 select count(*) FROM test_tsvector WHERE a @@ to_tsquery('copyright');
232 select rank(' a:1 s:2C d g'::tsvector, 'a | s');
233 select rank(' a:1 s:2B d g'::tsvector, 'a | s');
234 select rank(' a:1 s:2 d g'::tsvector, 'a | s');
235 select rank(' a:1 s:2C d g'::tsvector, 'a & s');
236 select rank(' a:1 s:2B d g'::tsvector, 'a & s');
237 select rank(' a:1 s:2 d g'::tsvector, 'a & s');
239 insert into test_tsvector (t) values ('foo bar foo the over foo qq bar');
240 drop trigger tsvectorupdate on test_tsvector;
241 select * from stat('select a from test_tsvector') order by ndoc desc, nentry desc, word;
242 insert into test_tsvector values ('1', 'a:1a,2,3b b:5a,6a,7c,8');
243 insert into test_tsvector values ('1', 'a:1a,2,3c b:5a,6b,7c,8b');
244 select * from stat('select a from test_tsvector','a') order by ndoc desc, nentry desc, word;
245 select * from stat('select a from test_tsvector','b') order by ndoc desc, nentry desc, word;
246 select * from stat('select a from test_tsvector','c') order by ndoc desc, nentry desc, word;
247 select * from stat('select a from test_tsvector','d') order by ndoc desc, nentry desc, word;
248 select * from stat('select a from test_tsvector','ad') order by ndoc desc, nentry desc, word;
250 select to_tsquery('english', 'skies & books');
252 select rank_cd(to_tsvector('Erosion It took the sea a thousand years,
253 A thousand years to trace
254 The granite features of this cliff
255 In crag and scarp and base.
256 It took the sea an hour one night
257 An hour of storm to place
258 The sculpture of these granite seams,
259 Upon a woman s face. E. J. Pratt (1882 1964)
260 '), to_tsquery('sea&thousand&years'));
262 select rank_cd(to_tsvector('Erosion It took the sea a thousand years,
263 A thousand years to trace
264 The granite features of this cliff
265 In crag and scarp and base.
266 It took the sea an hour one night
267 An hour of storm to place
268 The sculpture of these granite seams,
269 Upon a woman s face. E. J. Pratt (1882 1964)
270 '), to_tsquery('granite&sea'));
272 select rank_cd(to_tsvector('Erosion It took the sea a thousand years,
273 A thousand years to trace
274 The granite features of this cliff
275 In crag and scarp and base.
276 It took the sea an hour one night
277 An hour of storm to place
278 The sculpture of these granite seams,
279 Upon a woman s face. E. J. Pratt (1882 1964)
280 '), to_tsquery('sea'));
282 select headline('Erosion It took the sea a thousand years,
283 A thousand years to trace
284 The granite features of this cliff
285 In crag and scarp and base.
286 It took the sea an hour one night
287 An hour of storm to place
288 The sculpture of these granite seams,
289 Upon a woman s face. E. J. Pratt (1882 1964)
290 ', to_tsquery('sea&thousand&years'));
292 select headline('Erosion It took the sea a thousand years,
293 A thousand years to trace
294 The granite features of this cliff
295 In crag and scarp and base.
296 It took the sea an hour one night
297 An hour of storm to place
298 The sculpture of these granite seams,
299 Upon a woman s face. E. J. Pratt (1882 1964)
300 ', to_tsquery('granite&sea'));
302 select headline('Erosion It took the sea a thousand years,
303 A thousand years to trace
304 The granite features of this cliff
305 In crag and scarp and base.
306 It took the sea an hour one night
307 An hour of storm to place
308 The sculpture of these granite seams,
309 Upon a woman s face. E. J. Pratt (1882 1964)
310 ', to_tsquery('sea'));
315 <!-- some comment -->
317 Sea view wow <u>foo bar</u> <i>qq</i>
318 <a href="http://www.google.com/foo.bar.html" target="_blank">YES </a>
325 to_tsquery('sea&foo'), 'HighlightAll=true');
327 select * from public.ts_debug('Tsearch module for PostgreSQL 7.3.3');
330 insert into test_tsvector values (null, null);
331 select a is null, a from test_tsvector order by a;
334 create index wowidx on test_tsvector using gin (a);
335 set enable_seqscan=off;
337 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
338 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
339 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
340 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
341 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
342 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';