2 -- Sanity checks for text search catalogs
4 -- NB: we assume the oidjoins test will have caught any dangling links,
5 -- that is OID or REGPROC fields that are not zero and do not match some
6 -- row in the linked-to table. However, if we want to enforce that a link
7 -- field can't be 0, we have to check it here.
9 -- Find unexpected zero link entries
13 WHERE prsnamespace = 0 OR prsstart = 0 OR prstoken = 0 OR prsend = 0 OR
14 -- prsheadline is optional
19 WHERE dictnamespace = 0 OR dictowner = 0 OR dicttemplate = 0;
23 WHERE tmplnamespace = 0 OR tmpllexize = 0; -- tmplinit is optional
27 WHERE cfgnamespace = 0 OR cfgowner = 0 OR cfgparser = 0;
29 SELECT mapcfg, maptokentype, mapseqno
31 WHERE mapcfg = 0 OR mapdict = 0;
33 -- Look for pg_ts_config_map entries that aren't one of parser's token types
35 ( SELECT oid AS cfgid, (ts_token_type(cfgparser)).tokid AS tokid
36 FROM pg_ts_config ) AS tt
37 RIGHT JOIN pg_ts_config_map AS m
38 ON (tt.cfgid=m.mapcfg AND tt.tokid=m.maptokentype)
40 tt.cfgid IS NULL OR tt.tokid IS NULL;
42 -- test basic text search behavior without indexes, then with
44 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
45 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
46 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
47 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
48 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
49 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
50 SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*';
51 SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}');
52 SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme';
53 SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme';
55 create index wowidx on test_tsvector using gist (a);
57 SET enable_seqscan=OFF;
58 SET enable_indexscan=ON;
59 SET enable_bitmapscan=OFF;
61 explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
63 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
64 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
65 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
66 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
67 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
68 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
69 SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*';
70 SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}');
71 SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme';
72 SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme';
74 SET enable_indexscan=OFF;
75 SET enable_bitmapscan=ON;
77 explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
79 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
80 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
81 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
82 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
83 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
84 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
85 SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*';
86 SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}');
87 SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme';
88 SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme';
91 RESET enable_indexscan;
92 RESET enable_bitmapscan;
96 CREATE INDEX wowidx ON test_tsvector USING gin (a);
98 SET enable_seqscan=OFF;
99 -- GIN only supports bitmapscan, so no need to test plain indexscan
101 explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
103 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
104 SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
105 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
106 SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
107 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
108 SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
109 SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*';
110 SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}');
111 SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme';
112 SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme';
114 RESET enable_seqscan;
116 INSERT INTO test_tsvector VALUES ('???', 'DFG:1A,2B,6C,10 FGH');
117 SELECT * FROM ts_stat('SELECT a FROM test_tsvector') ORDER BY ndoc DESC, nentry DESC, word LIMIT 10;
118 SELECT * FROM ts_stat('SELECT a FROM test_tsvector', 'AB') ORDER BY ndoc DESC, nentry DESC, word;
120 --dictionaries and to_tsvector
122 SELECT ts_lexize('english_stem', 'skies');
123 SELECT ts_lexize('english_stem', 'identity');
125 SELECT * FROM ts_token_type('default');
127 SELECT * FROM ts_parse('default', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net 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>">
128 /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
129 <i <b> wow < jqw <> qwerty');
131 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 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>">
132 /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
133 <i <b> wow < jqw <> qwerty');
135 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 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>">
136 /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
137 <i <b> wow < jqw <> qwerty'));
141 SELECT * from ts_debug('english', '<myns:foo-bar_baz.blurfl>abc&nm1;def©ghiõjkl</myns:foo-bar_baz.blurfl>');
143 -- check parsing of URLs
144 SELECT * from ts_debug('english', 'http://www.harewoodsolutions.co.uk/press.aspx</span>');
145 SELECT * from ts_debug('english', 'http://aew.wer0c.ewr/id?ad=qwe&dw<span>');
146 SELECT * from ts_debug('english', 'http://5aew.werc.ewr:8100/?');
147 SELECT * from ts_debug('english', '5aew.werc.ewr:8100/?xx');
149 dictionaries, dictionaries is null as dnull, array_dims(dictionaries) as ddims,
150 lexemes, lexemes is null as lnull, array_dims(lexemes) as ldims
151 from ts_debug('english', 'a title');
155 SELECT to_tsquery('english', 'qwe & sKies ');
156 SELECT to_tsquery('simple', 'qwe & sKies ');
157 SELECT to_tsquery('english', '''the wether'':dc & '' sKies '':BC ');
158 SELECT to_tsquery('english', 'asd&(and|fghj)');
159 SELECT to_tsquery('english', '(asd&and)|fghj');
160 SELECT to_tsquery('english', '(asd&!and)|fghj');
161 SELECT to_tsquery('english', '(the|and&(i&1))&fghj');
163 SELECT plainto_tsquery('english', 'the and z 1))& fghj');
164 SELECT plainto_tsquery('english', 'foo bar') && plainto_tsquery('english', 'asd');
165 SELECT plainto_tsquery('english', 'foo bar') || plainto_tsquery('english', 'asd fg');
166 SELECT plainto_tsquery('english', 'foo bar') || !!plainto_tsquery('english', 'asd fg');
167 SELECT plainto_tsquery('english', 'foo bar') && 'asd | fg';
169 -- Check stop word deletion, a and s are stop-words
170 SELECT to_tsquery('english', '!(a & !b) & c');
171 SELECT to_tsquery('english', '!(a & !b)');
173 SELECT to_tsquery('english', '(1 <-> 2) <-> a');
174 SELECT to_tsquery('english', '(1 <-> a) <-> 2');
175 SELECT to_tsquery('english', '(a <-> 1) <-> 2');
176 SELECT to_tsquery('english', 'a <-> (1 <-> 2)');
177 SELECT to_tsquery('english', '1 <-> (a <-> 2)');
178 SELECT to_tsquery('english', '1 <-> (2 <-> a)');
180 SELECT to_tsquery('english', '(1 <-> 2) <3> a');
181 SELECT to_tsquery('english', '(1 <-> a) <3> 2');
182 SELECT to_tsquery('english', '(a <-> 1) <3> 2');
183 SELECT to_tsquery('english', 'a <3> (1 <-> 2)');
184 SELECT to_tsquery('english', '1 <3> (a <-> 2)');
185 SELECT to_tsquery('english', '1 <3> (2 <-> a)');
187 SELECT to_tsquery('english', '(1 <3> 2) <-> a');
188 SELECT to_tsquery('english', '(1 <3> a) <-> 2');
189 SELECT to_tsquery('english', '(a <3> 1) <-> 2');
190 SELECT to_tsquery('english', 'a <-> (1 <3> 2)');
191 SELECT to_tsquery('english', '1 <-> (a <3> 2)');
192 SELECT to_tsquery('english', '1 <-> (2 <3> a)');
194 SELECT to_tsquery('english', '((a <-> 1) <-> 2) <-> s');
195 SELECT to_tsquery('english', '(2 <-> (a <-> 1)) <-> s');
196 SELECT to_tsquery('english', '((1 <-> a) <-> 2) <-> s');
197 SELECT to_tsquery('english', '(2 <-> (1 <-> a)) <-> s');
198 SELECT to_tsquery('english', 's <-> ((a <-> 1) <-> 2)');
199 SELECT to_tsquery('english', 's <-> (2 <-> (a <-> 1))');
200 SELECT to_tsquery('english', 's <-> ((1 <-> a) <-> 2)');
201 SELECT to_tsquery('english', 's <-> (2 <-> (1 <-> a))');
203 SELECT to_tsquery('english', '((a <-> 1) <-> s) <-> 2');
204 SELECT to_tsquery('english', '(s <-> (a <-> 1)) <-> 2');
205 SELECT to_tsquery('english', '((1 <-> a) <-> s) <-> 2');
206 SELECT to_tsquery('english', '(s <-> (1 <-> a)) <-> 2');
207 SELECT to_tsquery('english', '2 <-> ((a <-> 1) <-> s)');
208 SELECT to_tsquery('english', '2 <-> (s <-> (a <-> 1))');
209 SELECT to_tsquery('english', '2 <-> ((1 <-> a) <-> s)');
210 SELECT to_tsquery('english', '2 <-> (s <-> (1 <-> a))');
212 SELECT to_tsquery('english', 'foo <-> (a <-> (the <-> bar))');
213 SELECT to_tsquery('english', '((foo <-> a) <-> the) <-> bar');
214 SELECT to_tsquery('english', 'foo <-> a <-> the <-> bar');
215 SELECT phraseto_tsquery('english', 'PostgreSQL can be extended by the user in many ways');
218 SELECT ts_rank_cd(to_tsvector('english', '
219 Day after day, day after day,
220 We stuck, nor breath nor motion,
221 As idle as a painted Ship
222 Upon a painted Ocean.
223 Water, water, every where
224 And all the boards did shrink;
225 Water, water, every where,
226 Nor any drop to drink.
227 S. T. Coleridge (1772-1834)
228 '), to_tsquery('english', 'paint&water'));
230 SELECT ts_rank_cd(to_tsvector('english', '
231 Day after day, day after day,
232 We stuck, nor breath nor motion,
233 As idle as a painted Ship
234 Upon a painted Ocean.
235 Water, water, every where
236 And all the boards did shrink;
237 Water, water, every where,
238 Nor any drop to drink.
239 S. T. Coleridge (1772-1834)
240 '), to_tsquery('english', 'breath&motion&water'));
242 SELECT ts_rank_cd(to_tsvector('english', '
243 Day after day, day after day,
244 We stuck, nor breath nor motion,
245 As idle as a painted Ship
246 Upon a painted Ocean.
247 Water, water, every where
248 And all the boards did shrink;
249 Water, water, every where,
250 Nor any drop to drink.
251 S. T. Coleridge (1772-1834)
252 '), to_tsquery('english', 'ocean'));
254 SELECT ts_rank_cd(to_tsvector('english', '
255 Day after day, day after day,
256 We stuck, nor breath nor motion,
257 As idle as a painted Ship
258 Upon a painted Ocean.
259 Water, water, every where
260 And all the boards did shrink;
261 Water, water, every where,
262 Nor any drop to drink.
263 S. T. Coleridge (1772-1834)
264 '), to_tsquery('english', 'painted <-> Ship'));
266 SELECT ts_rank_cd(strip(to_tsvector('both stripped')),
267 to_tsquery('both & stripped'));
269 SELECT ts_rank_cd(to_tsvector('unstripped') || strip(to_tsvector('stripped')),
270 to_tsquery('unstripped & stripped'));
273 SELECT ts_headline('english', '
274 Day after day, day after day,
275 We stuck, nor breath nor motion,
276 As idle as a painted Ship
277 Upon a painted Ocean.
278 Water, water, every where
279 And all the boards did shrink;
280 Water, water, every where,
281 Nor any drop to drink.
282 S. T. Coleridge (1772-1834)
283 ', to_tsquery('english', 'paint&water'));
285 SELECT ts_headline('english', '
286 Day after day, day after day,
287 We stuck, nor breath nor motion,
288 As idle as a painted Ship
289 Upon a painted Ocean.
290 Water, water, every where
291 And all the boards did shrink;
292 Water, water, every where,
293 Nor any drop to drink.
294 S. T. Coleridge (1772-1834)
295 ', to_tsquery('english', 'breath&motion&water'));
297 SELECT ts_headline('english', '
298 Day after day, day after day,
299 We stuck, nor breath nor motion,
300 As idle as a painted Ship
301 Upon a painted Ocean.
302 Water, water, every where
303 And all the boards did shrink;
304 Water, water, every where,
305 Nor any drop to drink.
306 S. T. Coleridge (1772-1834)
307 ', to_tsquery('english', 'ocean'));
309 SELECT ts_headline('english', '
310 Day after day, day after day,
311 We stuck, nor breath nor motion,
312 As idle as a painted Ship
313 Upon a painted Ocean.
314 Water, water, every where
315 And all the boards did shrink;
316 Water, water, every where,
317 Nor any drop to drink.
318 S. T. Coleridge (1772-1834)
319 ', phraseto_tsquery('english', 'painted Ocean'));
321 SELECT ts_headline('english', '
322 Day after day, day after day,
323 We stuck, nor breath nor motion,
324 As idle as a painted Ship
325 Upon a painted Ocean.
326 Water, water, every where
327 And all the boards did shrink;
328 Water, water, every where,
329 Nor any drop to drink.
330 S. T. Coleridge (1772-1834)
331 ', phraseto_tsquery('english', 'idle as a painted Ship'));
333 SELECT ts_headline('english', '
335 <!-- some comment -->
337 Sea view wow <u>foo bar</u> <i>qq</i>
338 <a href="http://www.google.com/foo.bar.html" target="_blank">YES </a>
345 to_tsquery('english', 'sea&foo'), 'HighlightAll=true');
347 SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 <-> 3', 'MaxWords=2, MinWords=1');
348 SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 & 3', 'MaxWords=4, MinWords=1');
349 SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 <-> 3', 'MaxWords=4, MinWords=1');
351 --Check if headline fragments work
352 SELECT ts_headline('english', '
353 Day after day, day after day,
354 We stuck, nor breath nor motion,
355 As idle as a painted Ship
356 Upon a painted Ocean.
357 Water, water, every where
358 And all the boards did shrink;
359 Water, water, every where,
360 Nor any drop to drink.
361 S. T. Coleridge (1772-1834)
362 ', to_tsquery('english', 'ocean'), 'MaxFragments=1');
364 --Check if more than one fragments are displayed
365 SELECT ts_headline('english', '
366 Day after day, day after day,
367 We stuck, nor breath nor motion,
368 As idle as a painted Ship
369 Upon a painted Ocean.
370 Water, water, every where
371 And all the boards did shrink;
372 Water, water, every where,
373 Nor any drop to drink.
374 S. T. Coleridge (1772-1834)
375 ', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2');
377 --Fragments when there all query words are not in the document
378 SELECT ts_headline('english', '
379 Day after day, day after day,
380 We stuck, nor breath nor motion,
381 As idle as a painted Ship
382 Upon a painted Ocean.
383 Water, water, every where
384 And all the boards did shrink;
385 Water, water, every where,
386 Nor any drop to drink.
387 S. T. Coleridge (1772-1834)
388 ', to_tsquery('english', 'ocean & seahorse'), 'MaxFragments=1');
390 --FragmentDelimiter option
391 SELECT ts_headline('english', '
392 Day after day, day after day,
393 We stuck, nor breath nor motion,
394 As idle as a painted Ship
395 Upon a painted Ocean.
396 Water, water, every where
397 And all the boards did shrink;
398 Water, water, every where,
399 Nor any drop to drink.
400 S. T. Coleridge (1772-1834)
401 ', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2,FragmentDelimiter=***');
405 CREATE TABLE test_tsquery (txtkeyword TEXT, txtsample TEXT);
407 \copy test_tsquery from stdin
408 'New York' new & york | big & apple | nyc
409 Moscow moskva | moscow
410 'Sanct Peter' Peterburg | peter | 'Sanct Peterburg'
411 'foo bar qq' foo & (bar | qq) & city
412 1 & (2 <-> 3) 2 <-> 4
417 ALTER TABLE test_tsquery ADD COLUMN keyword tsquery;
418 UPDATE test_tsquery SET keyword = to_tsquery('english', txtkeyword);
419 ALTER TABLE test_tsquery ADD COLUMN sample tsquery;
420 UPDATE test_tsquery SET sample = to_tsquery('english', txtsample::text);
423 SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york';
424 SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york';
425 SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york';
426 SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york';
427 SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york';
429 CREATE UNIQUE INDEX bt_tsq ON test_tsquery (keyword);
431 SET enable_seqscan=OFF;
433 SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york';
434 SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york';
435 SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york';
436 SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york';
437 SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york';
439 RESET enable_seqscan;
441 SELECT ts_rewrite('foo & bar & qq & new & york', 'new & york'::tsquery, 'big & apple | nyc | new & york & city');
442 SELECT ts_rewrite(ts_rewrite('new & !york ', 'york', '!jersey'),
445 SELECT ts_rewrite('moscow', 'SELECT keyword, sample FROM test_tsquery'::text );
446 SELECT ts_rewrite('moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'::text );
447 SELECT ts_rewrite('bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery'::text );
449 SELECT ts_rewrite( 'moscow', 'SELECT keyword, sample FROM test_tsquery');
450 SELECT ts_rewrite( 'moscow & hotel', 'SELECT keyword, sample FROM test_tsquery');
451 SELECT ts_rewrite( 'bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery');
453 SELECT ts_rewrite('1 & (2 <-> 3)', 'SELECT keyword, sample FROM test_tsquery'::text );
454 SELECT ts_rewrite('1 & (2 <2> 3)', 'SELECT keyword, sample FROM test_tsquery'::text );
455 SELECT ts_rewrite('5 <-> (1 & (2 <-> 3))', 'SELECT keyword, sample FROM test_tsquery'::text );
456 SELECT ts_rewrite('5 <-> (6 | 8)', 'SELECT keyword, sample FROM test_tsquery'::text );
458 -- Check empty substitution
459 SELECT ts_rewrite(to_tsquery('5 & (6 | 5)'), to_tsquery('5'), to_tsquery(''));
460 SELECT ts_rewrite(to_tsquery('!5'), to_tsquery('5'), to_tsquery(''));
462 SELECT keyword FROM test_tsquery WHERE keyword @> 'new';
463 SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow';
464 SELECT keyword FROM test_tsquery WHERE keyword <@ 'new';
465 SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow';
466 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
467 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
468 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
469 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
470 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
471 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
473 CREATE INDEX qq ON test_tsquery USING gist (keyword tsquery_ops);
474 SET enable_seqscan=OFF;
476 SELECT keyword FROM test_tsquery WHERE keyword @> 'new';
477 SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow';
478 SELECT keyword FROM test_tsquery WHERE keyword <@ 'new';
479 SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow';
480 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
481 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
482 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
483 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
484 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
485 SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
487 SELECT ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz');
488 SELECT to_tsvector('foo bar') @@
489 ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz');
490 SELECT to_tsvector('bar baz') @@
491 ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz');
493 RESET enable_seqscan;
496 SET default_text_search_config=simple;
498 SELECT to_tsvector('SKIES My booKs');
499 SELECT plainto_tsquery('SKIES My booKs');
500 SELECT to_tsquery('SKIES & My | booKs');
502 SET default_text_search_config=english;
504 SELECT to_tsvector('SKIES My booKs');
505 SELECT plainto_tsquery('SKIES My booKs');
506 SELECT to_tsquery('SKIES & My | booKs');
509 CREATE TRIGGER tsvectorupdate
510 BEFORE UPDATE OR INSERT ON test_tsvector
511 FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(a, 'pg_catalog.english', t);
513 SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
514 INSERT INTO test_tsvector (t) VALUES ('345 qwerty');
515 SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
516 UPDATE test_tsvector SET t = null WHERE t = '345 qwerty';
517 SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
519 INSERT INTO test_tsvector (t) VALUES ('345 qwerty');
521 SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
523 -- Test inlining of immutable constant functions
525 -- to_tsquery(text) is not immutable, so it won't be inlined
527 select * from test_tsquery, to_tsquery('new') q where txtsample @@ q;
529 -- to_tsquery(regconfig, text) is an immutable function.
530 -- That allows us to get rid of using function scan and join at all.
532 select * from test_tsquery, to_tsquery('english', 'new') q where txtsample @@ q;
534 -- test finding items in GIN's pending list
535 create temp table pendtest (ts tsvector);
536 create index pendtest_idx on pendtest using gin(ts);
537 insert into pendtest values (to_tsvector('Lore ipsam'));
538 insert into pendtest values (to_tsvector('Lore ipsum'));
539 select * from pendtest where 'ipsu:*'::tsquery @@ ts;
540 select * from pendtest where 'ipsa:*'::tsquery @@ ts;
541 select * from pendtest where 'ips:*'::tsquery @@ ts;
542 select * from pendtest where 'ipt:*'::tsquery @@ ts;
543 select * from pendtest where 'ipi:*'::tsquery @@ ts;
545 --check OP_PHRASE on index
546 create temp table phrase_index_test(fts tsvector);
547 insert into phrase_index_test values ('A fat cat has just eaten a rat.');
548 insert into phrase_index_test values (to_tsvector('english', 'A fat cat has just eaten a rat.'));
549 create index phrase_index_test_idx on phrase_index_test using gin(fts);
550 set enable_seqscan = off;
551 select * from phrase_index_test where fts @@ phraseto_tsquery('english', 'fat cat');
552 set enable_seqscan = on;
554 -- test websearch_to_tsquery function
555 select websearch_to_tsquery('simple', 'I have a fat:*ABCD cat');
556 select websearch_to_tsquery('simple', 'orange:**AABBCCDD');
557 select websearch_to_tsquery('simple', 'fat:A!cat:B|rat:C<');
558 select websearch_to_tsquery('simple', 'fat:A : cat:B');
560 select websearch_to_tsquery('simple', 'fat*rat');
561 select websearch_to_tsquery('simple', 'fat-rat');
562 select websearch_to_tsquery('simple', 'fat_rat');
564 -- weights are completely ignored
565 select websearch_to_tsquery('simple', 'abc : def');
566 select websearch_to_tsquery('simple', 'abc:def');
567 select websearch_to_tsquery('simple', 'a:::b');
568 select websearch_to_tsquery('simple', 'abc:d');
569 select websearch_to_tsquery('simple', ':');
571 -- these operators are ignored
572 select websearch_to_tsquery('simple', 'abc & def');
573 select websearch_to_tsquery('simple', 'abc | def');
574 select websearch_to_tsquery('simple', 'abc <-> def');
575 select websearch_to_tsquery('simple', 'abc (pg or class)');
577 -- NOT is ignored in quotes
578 select websearch_to_tsquery('english', 'My brand new smartphone');
579 select websearch_to_tsquery('english', 'My brand "new smartphone"');
580 select websearch_to_tsquery('english', 'My brand "new -smartphone"');
583 select websearch_to_tsquery('simple', 'cat or rat');
584 select websearch_to_tsquery('simple', 'cat OR rat');
585 select websearch_to_tsquery('simple', 'cat "OR" rat');
586 select websearch_to_tsquery('simple', 'cat OR');
587 select websearch_to_tsquery('simple', 'OR rat');
588 select websearch_to_tsquery('simple', '"fat cat OR rat"');
589 select websearch_to_tsquery('simple', 'fat (cat OR rat');
590 select websearch_to_tsquery('simple', 'or OR or');
592 -- OR is an operator here ...
593 select websearch_to_tsquery('simple', '"fat cat"or"fat rat"');
594 select websearch_to_tsquery('simple', 'fat or(rat');
595 select websearch_to_tsquery('simple', 'fat or)rat');
596 select websearch_to_tsquery('simple', 'fat or&rat');
597 select websearch_to_tsquery('simple', 'fat or|rat');
598 select websearch_to_tsquery('simple', 'fat or!rat');
599 select websearch_to_tsquery('simple', 'fat or<rat');
600 select websearch_to_tsquery('simple', 'fat or>rat');
601 select websearch_to_tsquery('simple', 'fat or ');
604 select websearch_to_tsquery('simple', 'abc orange');
605 select websearch_to_tsquery('simple', 'abc OR1234');
606 select websearch_to_tsquery('simple', 'abc or-abc');
607 select websearch_to_tsquery('simple', 'abc OR_abc');
610 select websearch_to_tsquery('english', '"pg_class pg');
611 select websearch_to_tsquery('english', 'pg_class pg"');
612 select websearch_to_tsquery('english', '"pg_class pg"');
613 select websearch_to_tsquery('english', 'abc "pg_class pg"');
614 select websearch_to_tsquery('english', '"pg_class pg" def');
615 select websearch_to_tsquery('english', 'abc "pg pg_class pg" def');
616 select websearch_to_tsquery('english', ' or "pg pg_class pg" or ');
617 select websearch_to_tsquery('english', '""pg pg_class pg""');
618 select websearch_to_tsquery('english', 'abc """"" def');
619 select websearch_to_tsquery('english', 'cat -"fat rat"');
620 select websearch_to_tsquery('english', 'cat -"fat rat" cheese');
621 select websearch_to_tsquery('english', 'abc "def -"');
622 select websearch_to_tsquery('english', 'abc "def :"');
624 select websearch_to_tsquery('english', '"A fat cat" has just eaten a -rat.');
625 select websearch_to_tsquery('english', '"A fat cat" has just eaten OR !rat.');
626 select websearch_to_tsquery('english', '"A fat cat" has just (+eaten OR -rat)');
628 select websearch_to_tsquery('english', 'this is ----fine');
629 select websearch_to_tsquery('english', '(()) )))) this ||| is && -fine, "dear friend" OR good');
630 select websearch_to_tsquery('english', 'an old <-> cat " is fine &&& too');
632 select websearch_to_tsquery('english', '"A the" OR just on');
633 select websearch_to_tsquery('english', '"a fat cat" ate a rat');
635 select to_tsvector('english', 'A fat cat ate a rat') @@
636 websearch_to_tsquery('english', '"a fat cat" ate a rat');
638 select to_tsvector('english', 'A fat grey cat ate a rat') @@
639 websearch_to_tsquery('english', '"a fat cat" ate a rat');
641 -- cases handled by gettoken_tsvector()
642 select websearch_to_tsquery('''');
643 select websearch_to_tsquery('''abc''''def''');
644 select websearch_to_tsquery('\abc');
645 select websearch_to_tsquery('\');