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.
8
-- Find unexpected zero link entries
11
WHERE prsnamespace = 0 OR prsstart = 0 OR prstoken = 0 OR prsend = 0 OR
12
-- prsheadline is optional
20
WHERE dictnamespace = 0 OR dictowner = 0 OR dicttemplate = 0;
27
WHERE tmplnamespace = 0 OR tmpllexize = 0; -- tmplinit is optional
34
WHERE cfgnamespace = 0 OR cfgowner = 0 OR cfgparser = 0;
39
SELECT mapcfg, maptokentype, mapseqno
41
WHERE mapcfg = 0 OR mapdict = 0;
42
mapcfg | maptokentype | mapseqno
43
--------+--------------+----------
46
-- Look for pg_ts_config_map entries that aren't one of parser's token types
48
( SELECT oid AS cfgid, (ts_token_type(cfgparser)).tokid AS tokid
49
FROM pg_ts_config ) AS tt
50
RIGHT JOIN pg_ts_config_map AS m
51
ON (tt.cfgid=m.mapcfg AND tt.tokid=m.maptokentype)
53
tt.cfgid IS NULL OR tt.tokid IS NULL;
54
cfgid | tokid | mapcfg | maptokentype | mapseqno | mapdict
55
-------+-------+--------+--------------+----------+---------
58
-- test basic text search behavior without indexes, then with
59
SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
65
SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
71
SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
77
SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
83
SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
89
SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
95
SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*';
101
create index wowidx on test_tsvector using gist (a);
102
SET enable_seqscan=OFF;
103
SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
109
SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
115
SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
121
SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
127
SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
133
SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
139
SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*';
145
RESET enable_seqscan;
147
CREATE INDEX wowidx ON test_tsvector USING gin (a);
148
SET enable_seqscan=OFF;
149
SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
155
SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
161
SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
167
SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
173
SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
179
SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
185
SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*';
191
RESET enable_seqscan;
192
INSERT INTO test_tsvector VALUES ('???', 'DFG:1A,2B,6C,10 FGH');
193
SELECT * FROM ts_stat('SELECT a FROM test_tsvector') ORDER BY ndoc DESC, nentry DESC, word LIMIT 10;
195
------+------+--------
208
SELECT * FROM ts_stat('SELECT a FROM test_tsvector', 'AB') ORDER BY ndoc DESC, nentry DESC, word;
210
------+------+--------
214
--dictionaries and to_tsvector
215
SELECT ts_lexize('english_stem', 'skies');
221
SELECT ts_lexize('english_stem', 'identity');
227
SELECT * FROM ts_token_type('default');
228
tokid | alias | description
229
-------+-----------------+------------------------------------------
230
1 | asciiword | Word, all ASCII
231
2 | word | Word, all letters
232
3 | numword | Word, letters and digits
233
4 | email | Email address
236
7 | sfloat | Scientific notation
237
8 | version | Version number
238
9 | hword_numpart | Hyphenated word part, letters and digits
239
10 | hword_part | Hyphenated word part, all letters
240
11 | hword_asciipart | Hyphenated word part, all ASCII
241
12 | blank | Space symbols
243
14 | protocol | Protocol head
244
15 | numhword | Hyphenated word, letters and digits
245
16 | asciihword | Hyphenated word, all ASCII
246
17 | hword | Hyphenated word, all letters
247
18 | url_path | URL path
248
19 | file | File or path name
249
20 | float | Decimal notation
250
21 | int | Signed integer
251
22 | uint | Unsigned integer
252
23 | entity | XML entity
255
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 qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
256
/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
257
<i <b> wow < jqw <> qwerty');
259
-------+--------------------------------------
270
5 | aew.werc.ewr/?ad=qwe&dw
274
5 | 1aew.werc.ewr/?ad=qwe&dw
281
5 | 3aew.werc.ewr/?ad=qwe&dw
289
5 | 5aew.werc.ewr:8100/?
290
6 | 5aew.werc.ewr:8100
299
5 | 6aew.werc.ewr:8100/?ad=qwe&dw
300
6 | 6aew.werc.ewr:8100
303
5 | 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32
304
6 | 7aew.werc.ewr:8100
305
18 | /?ad=qwe&dw=%20%32
347
13 | <a href="qwe<qwe>">
397
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>">
398
/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
399
<i <b> wow < jqw <> qwerty');
401
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
402
'+4.0e-10':28 '-4.2':60,62 '/?':18 '/?ad=qwe&dw':7,10,14,24 '/?ad=qwe&dw=%20%32':27 '/awdf/dwqe/4325':48 '/usr/local/fff':47 '/wqe-324/ewr':51 '1aew.werc.ewr':9 '1aew.werc.ewr/?ad=qwe&dw':8 '234':63 '234.435':32 '2aew.werc.ewr':11 '345':1 '3aew.werc.ewr':13 '3aew.werc.ewr/?ad=qwe&dw':12 '4.2':56,57,58 '455':33 '4aew.werc.ewr':15 '5.005':34 '5aew.werc.ewr:8100':17 '5aew.werc.ewr:8100/?':16 '6aew.werc.ewr:8100':23 '6aew.werc.ewr:8100/?ad=qwe&dw':22 '7aew.werc.ewr:8100':26 '7aew.werc.ewr:8100/?ad=qwe&dw=%20%32':25 'ad':19 'aew.werc.ewr':6 'aew.werc.ewr/?ad=qwe&dw':5 'asdf':39 'dw':21 'efd.r':3 'ewr1':45 'ewri2':46 'gist.c':54 'gist.h':52 'gist.h.c':53 'hjwer':44 'jf':41 'jqw':66 'qwe':2,20,29,30,37 'qwe-wer':36 'qwer':40 'qwerti':67 'qwqwe':31 'readlin':55,59,61 'rewt/ewr':49 'sdjk':42 'teodor@stack.net':35 'wefjn':50 'wer':38 'wow':65 'www.com':4
405
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>">
406
/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
407
<i <b> wow < jqw <> qwerty'));
414
SELECT * from ts_debug('english', '<myns:foo-bar_baz.blurfl>abc&nm1;def©ghiõjkl</myns:foo-bar_baz.blurfl>');
415
alias | description | token | dictionaries | dictionary | lexemes
416
-----------+-----------------+----------------------------+----------------+--------------+---------
417
tag | XML tag | <myns:foo-bar_baz.blurfl> | {} | |
418
asciiword | Word, all ASCII | abc | {english_stem} | english_stem | {abc}
419
entity | XML entity | &nm1; | {} | |
420
asciiword | Word, all ASCII | def | {english_stem} | english_stem | {def}
421
entity | XML entity | © | {} | |
422
asciiword | Word, all ASCII | ghi | {english_stem} | english_stem | {ghi}
423
entity | XML entity | õ | {} | |
424
asciiword | Word, all ASCII | jkl | {english_stem} | english_stem | {jkl}
425
tag | XML tag | </myns:foo-bar_baz.blurfl> | {} | |
428
-- check parsing of URLs
429
SELECT * from ts_debug('english', 'http://www.harewoodsolutions.co.uk/press.aspx</span>');
430
alias | description | token | dictionaries | dictionary | lexemes
431
----------+---------------+----------------------------------------+--------------+------------+------------------------------------------
432
protocol | Protocol head | http:// | {} | |
433
url | URL | www.harewoodsolutions.co.uk/press.aspx | {simple} | simple | {www.harewoodsolutions.co.uk/press.aspx}
434
host | Host | www.harewoodsolutions.co.uk | {simple} | simple | {www.harewoodsolutions.co.uk}
435
url_path | URL path | /press.aspx | {simple} | simple | {/press.aspx}
436
tag | XML tag | </span> | {} | |
439
SELECT * from ts_debug('english', 'http://aew.wer0c.ewr/id?ad=qwe&dw<span>');
440
alias | description | token | dictionaries | dictionary | lexemes
441
----------+---------------+----------------------------+--------------+------------+------------------------------
442
protocol | Protocol head | http:// | {} | |
443
url | URL | aew.wer0c.ewr/id?ad=qwe&dw | {simple} | simple | {aew.wer0c.ewr/id?ad=qwe&dw}
444
host | Host | aew.wer0c.ewr | {simple} | simple | {aew.wer0c.ewr}
445
url_path | URL path | /id?ad=qwe&dw | {simple} | simple | {/id?ad=qwe&dw}
446
tag | XML tag | <span> | {} | |
449
SELECT * from ts_debug('english', 'http://5aew.werc.ewr:8100/?');
450
alias | description | token | dictionaries | dictionary | lexemes
451
----------+---------------+----------------------+--------------+------------+------------------------
452
protocol | Protocol head | http:// | {} | |
453
url | URL | 5aew.werc.ewr:8100/? | {simple} | simple | {5aew.werc.ewr:8100/?}
454
host | Host | 5aew.werc.ewr:8100 | {simple} | simple | {5aew.werc.ewr:8100}
455
url_path | URL path | /? | {simple} | simple | {/?}
458
SELECT * from ts_debug('english', '5aew.werc.ewr:8100/?xx');
459
alias | description | token | dictionaries | dictionary | lexemes
460
----------+-------------+------------------------+--------------+------------+--------------------------
461
url | URL | 5aew.werc.ewr:8100/?xx | {simple} | simple | {5aew.werc.ewr:8100/?xx}
462
host | Host | 5aew.werc.ewr:8100 | {simple} | simple | {5aew.werc.ewr:8100}
463
url_path | URL path | /?xx | {simple} | simple | {/?xx}
467
SELECT to_tsquery('english', 'qwe & sKies ');
473
SELECT to_tsquery('simple', 'qwe & sKies ');
479
SELECT to_tsquery('english', '''the wether'':dc & '' sKies '':BC ');
481
------------------------
482
'wether':CD & 'sky':BC
485
SELECT to_tsquery('english', 'asd&(and|fghj)');
491
SELECT to_tsquery('english', '(asd&and)|fghj');
497
SELECT to_tsquery('english', '(asd&!and)|fghj');
503
SELECT to_tsquery('english', '(the|and&(i&1))&fghj');
509
SELECT plainto_tsquery('english', 'the and z 1))& fghj');
515
SELECT plainto_tsquery('english', 'foo bar') && plainto_tsquery('english', 'asd');
517
-----------------------
518
'foo' & 'bar' & 'asd'
521
SELECT plainto_tsquery('english', 'foo bar') || plainto_tsquery('english', 'asd fg');
523
------------------------------
524
'foo' & 'bar' | 'asd' & 'fg'
527
SELECT plainto_tsquery('english', 'foo bar') || !!plainto_tsquery('english', 'asd fg');
529
-----------------------------------
530
'foo' & 'bar' | !( 'asd' & 'fg' )
533
SELECT plainto_tsquery('english', 'foo bar') && 'asd | fg';
535
----------------------------------
536
'foo' & 'bar' & ( 'asd' | 'fg' )
539
SELECT ts_rank_cd(to_tsvector('english', '
540
Day after day, day after day,
541
We stuck, nor breath nor motion,
542
As idle as a painted Ship
543
Upon a painted Ocean.
544
Water, water, every where
545
And all the boards did shrink;
546
Water, water, every where,
547
Nor any drop to drink.
548
S. T. Coleridge (1772-1834)
549
'), to_tsquery('english', 'paint&water'));
555
SELECT ts_rank_cd(to_tsvector('english', '
556
Day after day, day after day,
557
We stuck, nor breath nor motion,
558
As idle as a painted Ship
559
Upon a painted Ocean.
560
Water, water, every where
561
And all the boards did shrink;
562
Water, water, every where,
563
Nor any drop to drink.
564
S. T. Coleridge (1772-1834)
565
'), to_tsquery('english', 'breath&motion&water'));
571
SELECT ts_rank_cd(to_tsvector('english', '
572
Day after day, day after day,
573
We stuck, nor breath nor motion,
574
As idle as a painted Ship
575
Upon a painted Ocean.
576
Water, water, every where
577
And all the boards did shrink;
578
Water, water, every where,
579
Nor any drop to drink.
580
S. T. Coleridge (1772-1834)
581
'), to_tsquery('english', 'ocean'));
588
SELECT ts_headline('english', '
589
Day after day, day after day,
590
We stuck, nor breath nor motion,
591
As idle as a painted Ship
592
Upon a painted Ocean.
593
Water, water, every where
594
And all the boards did shrink;
595
Water, water, every where,
596
Nor any drop to drink.
597
S. T. Coleridge (1772-1834)
598
', to_tsquery('english', 'paint&water'));
600
-----------------------------------------
601
<b>painted</b> Ocean. +
602
<b>Water</b>, <b>water</b>, every where+
603
And all the boards did shrink; +
604
<b>Water</b>, <b>water</b>, every
607
SELECT ts_headline('english', '
608
Day after day, day after day,
609
We stuck, nor breath nor motion,
610
As idle as a painted Ship
611
Upon a painted Ocean.
612
Water, water, every where
613
And all the boards did shrink;
614
Water, water, every where,
615
Nor any drop to drink.
616
S. T. Coleridge (1772-1834)
617
', to_tsquery('english', 'breath&motion&water'));
619
----------------------------------
620
<b>breath</b> nor <b>motion</b>,+
621
As idle as a painted Ship +
622
Upon a painted Ocean. +
623
<b>Water</b>, <b>water</b>
626
SELECT ts_headline('english', '
627
Day after day, day after day,
628
We stuck, nor breath nor motion,
629
As idle as a painted Ship
630
Upon a painted Ocean.
631
Water, water, every where
632
And all the boards did shrink;
633
Water, water, every where,
634
Nor any drop to drink.
635
S. T. Coleridge (1772-1834)
636
', to_tsquery('english', 'ocean'));
638
----------------------------------
640
Water, water, every where +
641
And all the boards did shrink;+
642
Water, water, every where
645
SELECT ts_headline('english', '
647
<!-- some comment -->
649
Sea view wow <u>foo bar</u> <i>qq</i>
650
<a href="http://www.google.com/foo.bar.html" target="_blank">YES </a>
657
to_tsquery('english', 'sea&foo'), 'HighlightAll=true');
659
-----------------------------------------------------------------------------
662
<!-- some comment --> +
664
<b>Sea</b> view wow <u><b>foo</b> bar</u> <i>qq</i> +
665
<a href="http://www.google.com/foo.bar.html" target="_blank">YES </a>+
668
document.write(15); +
674
--Check if headline fragments work
675
SELECT ts_headline('english', '
676
Day after day, day after day,
677
We stuck, nor breath nor motion,
678
As idle as a painted Ship
679
Upon a painted Ocean.
680
Water, water, every where
681
And all the boards did shrink;
682
Water, water, every where,
683
Nor any drop to drink.
684
S. T. Coleridge (1772-1834)
685
', to_tsquery('english', 'ocean'), 'MaxFragments=1');
687
------------------------------------
689
We stuck, nor breath nor motion,+
690
As idle as a painted Ship +
691
Upon a painted <b>Ocean</b>. +
692
Water, water, every where +
693
And all the boards did shrink; +
694
Water, water, every where, +
698
--Check if more than one fragments are displayed
699
SELECT ts_headline('english', '
700
Day after day, day after day,
701
We stuck, nor breath nor motion,
702
As idle as a painted Ship
703
Upon a painted Ocean.
704
Water, water, every where
705
And all the boards did shrink;
706
Water, water, every where,
707
Nor any drop to drink.
708
S. T. Coleridge (1772-1834)
709
', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2');
711
----------------------------------------------
712
after day, day after day, +
713
We <b>stuck</b>, nor breath nor motion, +
714
As idle as a painted Ship +
715
Upon a painted Ocean. +
716
Water, water, every where +
717
And all the boards did shrink; +
718
Water, water, every where ... drop to drink.+
719
S. T. <b>Coleridge</b>
722
--Fragments when there all query words are not in the document
723
SELECT ts_headline('english', '
724
Day after day, day after day,
725
We stuck, nor breath nor motion,
726
As idle as a painted Ship
727
Upon a painted Ocean.
728
Water, water, every where
729
And all the boards did shrink;
730
Water, water, every where,
731
Nor any drop to drink.
732
S. T. Coleridge (1772-1834)
733
', to_tsquery('english', 'ocean & seahorse'), 'MaxFragments=1');
735
------------------------------------
737
Day after day, day after day, +
738
We stuck, nor breath nor motion,+
742
--FragmentDelimiter option
743
SELECT ts_headline('english', '
744
Day after day, day after day,
745
We stuck, nor breath nor motion,
746
As idle as a painted Ship
747
Upon a painted Ocean.
748
Water, water, every where
749
And all the boards did shrink;
750
Water, water, every where,
751
Nor any drop to drink.
752
S. T. Coleridge (1772-1834)
753
', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2,FragmentDelimiter=***');
755
--------------------------------------------
756
after day, day after day, +
757
We <b>stuck</b>, nor breath nor motion, +
758
As idle as a painted Ship +
759
Upon a painted Ocean. +
760
Water, water, every where +
761
And all the boards did shrink; +
762
Water, water, every where***drop to drink.+
763
S. T. <b>Coleridge</b>
767
CREATE TABLE test_tsquery (txtkeyword TEXT, txtsample TEXT);
769
ALTER TABLE test_tsquery ADD COLUMN keyword tsquery;
770
UPDATE test_tsquery SET keyword = to_tsquery('english', txtkeyword);
771
ALTER TABLE test_tsquery ADD COLUMN sample tsquery;
772
UPDATE test_tsquery SET sample = to_tsquery('english', txtsample::text);
773
SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york';
779
SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york';
785
SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york';
791
SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york';
797
SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york';
803
CREATE UNIQUE INDEX bt_tsq ON test_tsquery (keyword);
804
SET enable_seqscan=OFF;
805
SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york';
811
SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york';
817
SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york';
823
SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york';
829
SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york';
835
RESET enable_seqscan;
836
SELECT ts_rewrite('foo & bar & qq & new & york', 'new & york'::tsquery, 'big & apple | nyc | new & york & city');
838
----------------------------------------------------------------------------------
839
'foo' & 'bar' & 'qq' & ( 'city' & 'new' & 'york' | ( 'nyc' | 'big' & 'apple' ) )
842
SELECT ts_rewrite('moscow', 'SELECT keyword, sample FROM test_tsquery'::text );
844
---------------------
848
SELECT ts_rewrite('moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'::text );
850
-----------------------------------
851
'hotel' & ( 'moskva' | 'moscow' )
854
SELECT ts_rewrite('bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery'::text );
856
-------------------------------------------------------------------------------------
857
'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )
860
SELECT ts_rewrite( 'moscow', 'SELECT keyword, sample FROM test_tsquery');
862
---------------------
866
SELECT ts_rewrite( 'moscow & hotel', 'SELECT keyword, sample FROM test_tsquery');
868
-----------------------------------
869
'hotel' & ( 'moskva' | 'moscow' )
872
SELECT ts_rewrite( 'bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery');
874
-------------------------------------------------------------------------------------
875
'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )
878
SELECT keyword FROM test_tsquery WHERE keyword @> 'new';
884
SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow';
890
SELECT keyword FROM test_tsquery WHERE keyword <@ 'new';
895
SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow';
901
SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
903
---------------------
907
SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
909
-----------------------------------
910
'hotel' & ( 'moskva' | 'moscow' )
913
SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
915
-------------------------------------------------------------------------------------
916
'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )
919
SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
921
---------------------
925
SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
927
-----------------------------------
928
'hotel' & ( 'moskva' | 'moscow' )
931
SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
933
-------------------------------------------------------------------------------------
934
'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )
937
CREATE INDEX qq ON test_tsquery USING gist (keyword tsquery_ops);
938
SET enable_seqscan=OFF;
939
SELECT keyword FROM test_tsquery WHERE keyword @> 'new';
945
SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow';
951
SELECT keyword FROM test_tsquery WHERE keyword <@ 'new';
956
SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow';
962
SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
964
---------------------
968
SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
970
-----------------------------------
971
'hotel' & ( 'moskva' | 'moscow' )
974
SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
976
-------------------------------------------------------------------------------------
977
'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )
980
SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;
982
---------------------
986
SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;
988
-----------------------------------
989
'hotel' & ( 'moskva' | 'moscow' )
992
SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;
994
-------------------------------------------------------------------------------------
995
'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )
998
RESET enable_seqscan;
1000
SET default_text_search_config=simple;
1001
SELECT to_tsvector('SKIES My booKs');
1003
----------------------------
1004
'books':3 'my':2 'skies':1
1007
SELECT plainto_tsquery('SKIES My booKs');
1009
--------------------------
1010
'skies' & 'my' & 'books'
1013
SELECT to_tsquery('SKIES & My | booKs');
1015
--------------------------
1016
'skies' & 'my' | 'books'
1019
SET default_text_search_config=english;
1020
SELECT to_tsvector('SKIES My booKs');
1026
SELECT plainto_tsquery('SKIES My booKs');
1032
SELECT to_tsquery('SKIES & My | booKs');
1039
CREATE TRIGGER tsvectorupdate
1040
BEFORE UPDATE OR INSERT ON test_tsvector
1041
FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(a, 'pg_catalog.english', t);
1042
SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
1048
INSERT INTO test_tsvector (t) VALUES ('345 qwerty');
1049
SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
1055
UPDATE test_tsvector SET t = null WHERE t = '345 qwerty';
1056
SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
1062
INSERT INTO test_tsvector (t) VALUES ('345 qwerty');
1063
SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
1069
-- test finding items in GIN's pending list
1070
create temp table pendtest (ts tsvector);
1071
create index pendtest_idx on pendtest using gin(ts);
1072
insert into pendtest values (to_tsvector('Lore ipsam'));
1073
insert into pendtest values (to_tsvector('Lore ipsum'));
1074
select * from pendtest where 'ipsu:*'::tsquery @@ ts;
1076
--------------------
1080
select * from pendtest where 'ipsa:*'::tsquery @@ ts;
1082
--------------------
1086
select * from pendtest where 'ips:*'::tsquery @@ ts;
1088
--------------------
1093
select * from pendtest where 'ipt:*'::tsquery @@ ts;
1098
select * from pendtest where 'ipi:*'::tsquery @@ ts;
added
removed
src/test/regress/expected/tsearch.out
