/
githubmirror
/
postgres
Обзор
Документация
Войти
/
githubmirror
/
postgres
Код
Запросы
0
Пакеты
0
Релизы
0
Аналитика
Безопасность
master
src/test/regress/expected/tsearch.out
3 024 строки
78 KB
Michael Paquier
Fix overflows with ts_headline()
11 май 2026, 15:13
11 май 2026, 15:13
d388e1d
Код
Авторство
О чём код?
-- directory paths are passed to us in environment variables \getenv abs_srcdir PG_ABS_SRCDIR -- -- Sanity checks for text search catalogs -- -- NB: we assume the oidjoins test will have caught any dangling links, -- that is OID or REGPROC fields that are not zero and do not match some -- row in the linked-to table. However, if we want to enforce that a link -- field can't be 0, we have to check it here. -- Find unexpected zero link entries SELECT oid, prsname FROM pg_ts_parser WHERE prsnamespace = 0 OR prsstart = 0 OR prstoken = 0 OR prsend = 0 OR -- prsheadline is optional prslextype = 0; oid | prsname -----+--------- (0 rows) SELECT oid, dictname FROM pg_ts_dict WHERE dictnamespace = 0 OR dictowner = 0 OR dicttemplate = 0; oid | dictname -----+---------- (0 rows) SELECT oid, tmplname FROM pg_ts_template WHERE tmplnamespace = 0 OR tmpllexize = 0; -- tmplinit is optional oid | tmplname -----+---------- (0 rows) SELECT oid, cfgname FROM pg_ts_config WHERE cfgnamespace = 0 OR cfgowner = 0 OR cfgparser = 0; oid | cfgname -----+--------- (0 rows) SELECT mapcfg, maptokentype, mapseqno FROM pg_ts_config_map WHERE mapcfg = 0 OR mapdict = 0; mapcfg | maptokentype | mapseqno --------+--------------+---------- (0 rows) -- Look for pg_ts_config_map entries that aren't one of parser's token types SELECT * FROM ( SELECT oid AS cfgid, (ts_token_type(cfgparser)).tokid AS tokid FROM pg_ts_config ) AS tt RIGHT JOIN pg_ts_config_map AS m ON (tt.cfgid=m.mapcfg AND tt.tokid=m.maptokentype) WHERE tt.cfgid IS NULL OR tt.tokid IS NULL; cfgid | tokid | mapcfg | maptokentype | mapseqno | mapdict -------+-------+--------+--------------+----------+--------- (0 rows) -- Load some test data CREATE TABLE test_tsvector( t text, a tsvector ); \set filename :abs_srcdir '/data/tsearch.data' COPY test_tsvector FROM :'filename'; ANALYZE test_tsvector; -- test basic text search behavior without indexes, then with SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; count ------- 158 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; count ------- 17 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; count ------- 98 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; count ------- 23 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; count ------- 39 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; count ------- 494 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); count ------- 158 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; count ------- 508 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; count ------- 3 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; count ------- 432 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; count ------- 507 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; count ------- 508 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; count ------- 507 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:A'; count ------- 56 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:D'; count ------- 58 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:A'; count ------- 452 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:D'; count ------- 450 (1 row) create index wowidx on test_tsvector using gist (a); SET enable_seqscan=OFF; SET enable_indexscan=ON; SET enable_bitmapscan=OFF; explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; QUERY PLAN ------------------------------------------------------- Aggregate -> Index Scan using wowidx on test_tsvector Index Cond: (a @@ '''wr'' | ''qh'''::tsquery) (3 rows) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; count ------- 158 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; count ------- 17 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; count ------- 98 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; count ------- 23 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; count ------- 39 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; count ------- 494 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); count ------- 158 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; count ------- 508 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; count ------- 3 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; count ------- 432 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; count ------- 507 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; count ------- 508 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; count ------- 507 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:A'; count ------- 56 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:D'; count ------- 58 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:A'; count ------- 452 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:D'; count ------- 450 (1 row) SET enable_indexscan=OFF; SET enable_bitmapscan=ON; explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; QUERY PLAN ------------------------------------------------------------- Aggregate -> Bitmap Heap Scan on test_tsvector Recheck Cond: (a @@ '''wr'' | ''qh'''::tsquery) -> Bitmap Index Scan on wowidx Index Cond: (a @@ '''wr'' | ''qh'''::tsquery) (5 rows) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; count ------- 158 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; count ------- 17 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; count ------- 98 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; count ------- 23 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; count ------- 39 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; count ------- 494 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); count ------- 158 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; count ------- 508 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; count ------- 3 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; count ------- 432 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; count ------- 507 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; count ------- 508 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; count ------- 507 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:A'; count ------- 56 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:D'; count ------- 58 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:A'; count ------- 452 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:D'; count ------- 450 (1 row) -- Test siglen parameter of GiST tsvector_ops CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(foo=1)); ERROR: unrecognized parameter "foo" CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(siglen=0)); ERROR: value 0 out of bounds for option "siglen" DETAIL: Valid values are between "1" and "2024". CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(siglen=2048)); ERROR: value 2048 out of bounds for option "siglen" DETAIL: Valid values are between "1" and "2024". CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(siglen=100,foo='bar')); ERROR: unrecognized parameter "foo" CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(siglen=100, siglen = 200)); ERROR: parameter "siglen" specified more than once CREATE INDEX wowidx2 ON test_tsvector USING gist (a tsvector_ops(siglen=1)); \d test_tsvector Table "public.test_tsvector" Column | Type | Collation | Nullable | Default --------+----------+-----------+----------+--------- t | text | | | a | tsvector | | | Indexes: "wowidx" gist (a) "wowidx2" gist (a tsvector_ops (siglen='1')) DROP INDEX wowidx; EXPLAIN (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; QUERY PLAN ------------------------------------------------------------- Aggregate -> Bitmap Heap Scan on test_tsvector Recheck Cond: (a @@ '''wr'' | ''qh'''::tsquery) -> Bitmap Index Scan on wowidx2 Index Cond: (a @@ '''wr'' | ''qh'''::tsquery) (5 rows) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; count ------- 158 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; count ------- 17 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; count ------- 98 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; count ------- 23 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; count ------- 39 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; count ------- 494 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); count ------- 158 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; count ------- 508 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; count ------- 3 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; count ------- 432 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; count ------- 507 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; count ------- 508 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; count ------- 507 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:A'; count ------- 56 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:D'; count ------- 58 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:A'; count ------- 452 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:D'; count ------- 450 (1 row) DROP INDEX wowidx2; CREATE INDEX wowidx ON test_tsvector USING gist (a tsvector_ops(siglen=484)); \d test_tsvector Table "public.test_tsvector" Column | Type | Collation | Nullable | Default --------+----------+-----------+----------+--------- t | text | | | a | tsvector | | | Indexes: "wowidx" gist (a tsvector_ops (siglen='484')) EXPLAIN (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; QUERY PLAN ------------------------------------------------------------- Aggregate -> Bitmap Heap Scan on test_tsvector Recheck Cond: (a @@ '''wr'' | ''qh'''::tsquery) -> Bitmap Index Scan on wowidx Index Cond: (a @@ '''wr'' | ''qh'''::tsquery) (5 rows) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; count ------- 158 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; count ------- 17 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; count ------- 98 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; count ------- 23 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; count ------- 39 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; count ------- 494 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); count ------- 158 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; count ------- 508 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; count ------- 3 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; count ------- 432 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; count ------- 507 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; count ------- 508 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; count ------- 507 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:A'; count ------- 56 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:D'; count ------- 58 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:A'; count ------- 452 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:D'; count ------- 450 (1 row) RESET enable_seqscan; RESET enable_indexscan; RESET enable_bitmapscan; DROP INDEX wowidx; ALTER TABLE test_tsvector SET (parallel_workers = 2); CREATE INDEX wowidx ON test_tsvector USING gin (a); SET enable_seqscan=OFF; -- GIN only supports bitmapscan, so no need to test plain indexscan explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; QUERY PLAN ------------------------------------------------------------- Aggregate -> Bitmap Heap Scan on test_tsvector Recheck Cond: (a @@ '''wr'' | ''qh'''::tsquery) -> Bitmap Index Scan on wowidx Index Cond: (a @@ '''wr'' | ''qh'''::tsquery) (5 rows) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; count ------- 158 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; count ------- 17 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; count ------- 98 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; count ------- 23 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; count ------- 39 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; count ------- 494 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); count ------- 158 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; count ------- 508 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; count ------- 3 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; count ------- 432 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; count ------- 507 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; count ------- 508 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; count ------- 507 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:A'; count ------- 56 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ 'wd:D'; count ------- 58 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:A'; count ------- 452 (1 row) SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:D'; count ------- 450 (1 row) -- Test optimization of non-empty GIN_SEARCH_MODE_ALL queries EXPLAIN (COSTS OFF) SELECT count(*) FROM test_tsvector WHERE a @@ '!qh'; QUERY PLAN ----------------------------------------------------- Aggregate -> Bitmap Heap Scan on test_tsvector Recheck Cond: (a @@ '!''qh'''::tsquery) -> Bitmap Index Scan on wowidx Index Cond: (a @@ '!''qh'''::tsquery) (5 rows) SELECT count(*) FROM test_tsvector WHERE a @@ '!qh'; count ------- 410 (1 row) EXPLAIN (COSTS OFF) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr' AND a @@ '!qh'; QUERY PLAN ------------------------------------------------------------------------------------ Aggregate -> Bitmap Heap Scan on test_tsvector Recheck Cond: ((a @@ '''wr'''::tsquery) AND (a @@ '!''qh'''::tsquery)) -> Bitmap Index Scan on wowidx Index Cond: ((a @@ '''wr'''::tsquery) AND (a @@ '!''qh'''::tsquery)) (5 rows) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr' AND a @@ '!qh'; count ------- 60 (1 row) RESET enable_seqscan; INSERT INTO test_tsvector VALUES ('???', 'DFG:1A,2B,6C,10 FGH'); SELECT * FROM ts_stat('SELECT a FROM test_tsvector') ORDER BY ndoc DESC, nentry DESC, word LIMIT 10; word | ndoc | nentry ------+------+-------- qq | 108 | 108 qt | 102 | 102 qe | 100 | 101 qh | 98 | 99 qw | 98 | 98 qa | 97 | 97 ql | 94 | 94 qs | 94 | 94 qr | 92 | 93 qi | 92 | 92 (10 rows) SELECT * FROM ts_stat('SELECT a FROM test_tsvector', 'AB') ORDER BY ndoc DESC, nentry DESC, word; word | ndoc | nentry ------+------+-------- DFG | 1 | 2 (1 row) --dictionaries and to_tsvector SELECT ts_lexize('english_stem', 'skies'); ts_lexize ----------- {sky} (1 row) SELECT ts_lexize('english_stem', 'identity'); ts_lexize ----------- {ident} (1 row) SELECT * FROM ts_token_type('default'); tokid | alias | description -------+-----------------+------------------------------------------ 1 | asciiword | Word, all ASCII 2 | word | Word, all letters 3 | numword | Word, letters and digits 4 | email | Email address 5 | url | URL 6 | host | Host 7 | sfloat | Scientific notation 8 | version | Version number 9 | hword_numpart | Hyphenated word part, letters and digits 10 | hword_part | Hyphenated word part, all letters 11 | hword_asciipart | Hyphenated word part, all ASCII 12 | blank | Space symbols 13 | tag | XML tag 14 | protocol | Protocol head 15 | numhword | Hyphenated word, letters and digits 16 | asciihword | Hyphenated word, all ASCII 17 | hword | Hyphenated word, all letters 18 | url_path | URL path 19 | file | File or path name 20 | float | Decimal notation 21 | int | Signed integer 22 | uint | Unsigned integer 23 | entity | XML entity (23 rows) 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>"> /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 <i <b> wow < jqw <> qwerty'); tokid | token -------+-------------------------------------- 22 | 345 12 | 1 | qwe 12 | @ 19 | efd.r 12 | ' 14 | http:// 6 | www.com 12 | / 14 | http:// 5 | aew.werc.ewr/?ad=qwe&dw 6 | aew.werc.ewr 18 | /?ad=qwe&dw 12 | 5 | 1aew.werc.ewr/?ad=qwe&dw 6 | 1aew.werc.ewr 18 | /?ad=qwe&dw 12 | 6 | 2aew.werc.ewr 12 | 14 | http:// 5 | 3aew.werc.ewr/?ad=qwe&dw 6 | 3aew.werc.ewr 18 | /?ad=qwe&dw 12 | 14 | http:// 6 | 4aew.werc.ewr 12 | 14 | http:// 5 | 5aew.werc.ewr:8100/? 6 | 5aew.werc.ewr:8100 18 | /? 12 | 1 | ad 12 | = 1 | qwe 12 | & 1 | dw 12 | 5 | 6aew.werc.ewr:8100/?ad=qwe&dw 6 | 6aew.werc.ewr:8100 18 | /?ad=qwe&dw 12 | 5 | 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 6 | 7aew.werc.ewr:8100 18 | /?ad=qwe&dw=%20%32 12 | 7 | +4.0e-10 12 | 1 | qwe 12 | 1 | qwe 12 | 1 | qwqwe 12 | 20 | 234.435 12 | 22 | 455 12 | 20 | 5.005 12 | 4 | teodor@stack.net 12 | 4 | teodor@123-stack.net 12 | 4 | 123_teodor@stack.net 12 | 4 | 123-teodor@stack.net 12 | 16 | qwe-wer 11 | qwe 12 | - 11 | wer 12 | 1 | asdf 12 | 13 | <fr> 1 | qwer 12 | 1 | jf 12 | 1 | sdjk 12 | < 1 | we 12 | 1 | hjwer 12 | 13 | <werrwe> 12 | 3 | ewr1 12 | > 3 | ewri2 12 | 13 | <a href="qwe<qwe>"> 12 | + | 19 | /usr/local/fff 12 | 19 | /awdf/dwqe/4325 12 | 19 | rewt/ewr 12 | 1 | wefjn 12 | 19 | /wqe-324/ewr 12 | 19 | gist.h 12 | 19 | gist.h.c 12 | 19 | gist.c 12 | . 1 | readline 12 | 20 | 4.2 12 | 20 | 4.2 12 | . 20 | 4.2 12 | , 1 | readline 20 | -4.2 12 | 1 | readline 20 | -4.2 12 | . 22 | 234 12 | + | 12 | < 1 | i 12 | 13 | <b> 12 | 1 | wow 12 | 12 | < 1 | jqw 12 | 12 | <> 1 | qwerty (139 rows) 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>"> /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 <i <b> wow < jqw <> qwerty'); to_tsvector ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ '+4.0e-10':28 '-4.2':63,65 '/?':18 '/?ad=qwe&dw':7,10,14,24 '/?ad=qwe&dw=%20%32':27 '/awdf/dwqe/4325':51 '/usr/local/fff':50 '/wqe-324/ewr':54 '123-teodor@stack.net':38 '123_teodor@stack.net':37 '1aew.werc.ewr':9 '1aew.werc.ewr/?ad=qwe&dw':8 '234':66 '234.435':32 '2aew.werc.ewr':11 '345':1 '3aew.werc.ewr':13 '3aew.werc.ewr/?ad=qwe&dw':12 '4.2':59,60,61 '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':42 'dw':21 'efd.r':3 'ewr1':48 'ewri2':49 'gist.c':57 'gist.h':55 'gist.h.c':56 'hjwer':47 'jf':44 'jqw':69 'qwe':2,20,29,30,40 'qwe-wer':39 'qwer':43 'qwerti':70 'qwqwe':31 'readlin':58,62,64 'rewt/ewr':52 'sdjk':45 'teodor@123-stack.net':36 'teodor@stack.net':35 'wefjn':53 'wer':41 'wow':68 'www.com':4 (1 row) 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>"> /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 <i <b> wow < jqw <> qwerty')); length -------- 56 (1 row) -- ts_debug SELECT * from ts_debug('english', '<myns:foo-bar_baz.blurfl>abc&nm1;def©ghiõjkl</myns:foo-bar_baz.blurfl>'); alias | description | token | dictionaries | dictionary | lexemes -----------+-----------------+----------------------------+----------------+--------------+--------- tag | XML tag | <myns:foo-bar_baz.blurfl> | {} | | asciiword | Word, all ASCII | abc | {english_stem} | english_stem | {abc} entity | XML entity | &nm1; | {} | | asciiword | Word, all ASCII | def | {english_stem} | english_stem | {def} entity | XML entity | © | {} | | asciiword | Word, all ASCII | ghi | {english_stem} | english_stem | {ghi} entity | XML entity | õ | {} | | asciiword | Word, all ASCII | jkl | {english_stem} | english_stem | {jkl} tag | XML tag | </myns:foo-bar_baz.blurfl> | {} | | (9 rows) -- check parsing of URLs SELECT * from ts_debug('english', 'http://www.harewoodsolutions.co.uk/press.aspx</span>'); alias | description | token | dictionaries | dictionary | lexemes ----------+---------------+----------------------------------------+--------------+------------+------------------------------------------ protocol | Protocol head | http:// | {} | | url | URL | www.harewoodsolutions.co.uk/press.aspx | {simple} | simple | {www.harewoodsolutions.co.uk/press.aspx} host | Host | www.harewoodsolutions.co.uk | {simple} | simple | {www.harewoodsolutions.co.uk} url_path | URL path | /press.aspx | {simple} | simple | {/press.aspx} tag | XML tag | </span> | {} | | (5 rows) SELECT * from ts_debug('english', 'http://aew.wer0c.ewr/id?ad=qwe&dw<span>'); alias | description | token | dictionaries | dictionary | lexemes ----------+---------------+----------------------------+--------------+------------+------------------------------ protocol | Protocol head | http:// | {} | | url | URL | aew.wer0c.ewr/id?ad=qwe&dw | {simple} | simple | {aew.wer0c.ewr/id?ad=qwe&dw} host | Host | aew.wer0c.ewr | {simple} | simple | {aew.wer0c.ewr} url_path | URL path | /id?ad=qwe&dw | {simple} | simple | {/id?ad=qwe&dw} tag | XML tag | <span> | {} | | (5 rows) SELECT * from ts_debug('english', 'http://5aew.werc.ewr:8100/?'); alias | description | token | dictionaries | dictionary | lexemes ----------+---------------+----------------------+--------------+------------+------------------------ protocol | Protocol head | http:// | {} | | url | URL | 5aew.werc.ewr:8100/? | {simple} | simple | {5aew.werc.ewr:8100/?} host | Host | 5aew.werc.ewr:8100 | {simple} | simple | {5aew.werc.ewr:8100} url_path | URL path | /? | {simple} | simple | {/?} (4 rows) SELECT * from ts_debug('english', '5aew.werc.ewr:8100/?xx'); alias | description | token | dictionaries | dictionary | lexemes ----------+-------------+------------------------+--------------+------------+-------------------------- url | URL | 5aew.werc.ewr:8100/?xx | {simple} | simple | {5aew.werc.ewr:8100/?xx} host | Host | 5aew.werc.ewr:8100 | {simple} | simple | {5aew.werc.ewr:8100} url_path | URL path | /?xx | {simple} | simple | {/?xx} (3 rows) SELECT token, alias, dictionaries, dictionaries is null as dnull, array_dims(dictionaries) as ddims, lexemes, lexemes is null as lnull, array_dims(lexemes) as ldims from ts_debug('english', 'a title'); token | alias | dictionaries | dnull | ddims | lexemes | lnull | ldims -------+-----------+----------------+-------+-------+---------+-------+------- a | asciiword | {english_stem} | f | [1:1] | {} | f | | blank | {} | f | | | t | title | asciiword | {english_stem} | f | [1:1] | {titl} | f | [1:1] (3 rows) -- to_tsquery SELECT to_tsquery('english', 'qwe & sKies '); to_tsquery --------------- 'qwe' & 'sky' (1 row) SELECT to_tsquery('simple', 'qwe & sKies '); to_tsquery ----------------- 'qwe' & 'skies' (1 row) SELECT to_tsquery('english', '''the wether'':dc & '' sKies '':BC '); to_tsquery ------------------------ 'wether':CD & 'sky':BC (1 row) SELECT to_tsquery('english', 'asd&(and|fghj)'); to_tsquery ---------------- 'asd' & 'fghj' (1 row) SELECT to_tsquery('english', '(asd&and)|fghj'); to_tsquery ---------------- 'asd' | 'fghj' (1 row) SELECT to_tsquery('english', '(asd&!and)|fghj'); to_tsquery ---------------- 'asd' | 'fghj' (1 row) SELECT to_tsquery('english', '(the|and&(i&1))&fghj'); to_tsquery -------------- '1' & 'fghj' (1 row) SELECT plainto_tsquery('english', 'the and z 1))& fghj'); plainto_tsquery -------------------- 'z' & '1' & 'fghj' (1 row) SELECT plainto_tsquery('english', 'foo bar') && plainto_tsquery('english', 'asd'); ?column? ----------------------- 'foo' & 'bar' & 'asd' (1 row) SELECT plainto_tsquery('english', 'foo bar') || plainto_tsquery('english', 'asd fg'); ?column? ------------------------------ 'foo' & 'bar' | 'asd' & 'fg' (1 row) SELECT plainto_tsquery('english', 'foo bar') || !!plainto_tsquery('english', 'asd fg'); ?column? ----------------------------------- 'foo' & 'bar' | !( 'asd' & 'fg' ) (1 row) SELECT plainto_tsquery('english', 'foo bar') && 'asd | fg'; ?column? ---------------------------------- 'foo' & 'bar' & ( 'asd' | 'fg' ) (1 row) -- Check stop word deletion, a and s are stop-words SELECT to_tsquery('english', '!(a & !b) & c'); to_tsquery ------------- !!'b' & 'c' (1 row) SELECT to_tsquery('english', '!(a & !b)'); to_tsquery ------------ !!'b' (1 row) SELECT to_tsquery('english', '(1 <-> 2) <-> a'); to_tsquery ------------- '1' <-> '2' (1 row) SELECT to_tsquery('english', '(1 <-> a) <-> 2'); to_tsquery ------------- '1' <2> '2' (1 row) SELECT to_tsquery('english', '(a <-> 1) <-> 2'); to_tsquery ------------- '1' <-> '2' (1 row) SELECT to_tsquery('english', 'a <-> (1 <-> 2)'); to_tsquery ------------- '1' <-> '2' (1 row) SELECT to_tsquery('english', '1 <-> (a <-> 2)'); to_tsquery ------------- '1' <2> '2' (1 row) SELECT to_tsquery('english', '1 <-> (2 <-> a)'); to_tsquery ------------- '1' <-> '2' (1 row) SELECT to_tsquery('english', '(1 <-> 2) <3> a'); to_tsquery ------------- '1' <-> '2' (1 row) SELECT to_tsquery('english', '(1 <-> a) <3> 2'); to_tsquery ------------- '1' <4> '2' (1 row) SELECT to_tsquery('english', '(a <-> 1) <3> 2'); to_tsquery ------------- '1' <3> '2' (1 row) SELECT to_tsquery('english', 'a <3> (1 <-> 2)'); to_tsquery ------------- '1' <-> '2' (1 row) SELECT to_tsquery('english', '1 <3> (a <-> 2)'); to_tsquery ------------- '1' <4> '2' (1 row) SELECT to_tsquery('english', '1 <3> (2 <-> a)'); to_tsquery ------------- '1' <3> '2' (1 row) SELECT to_tsquery('english', '(1 <3> 2) <-> a'); to_tsquery ------------- '1' <3> '2' (1 row) SELECT to_tsquery('english', '(1 <3> a) <-> 2'); to_tsquery ------------- '1' <4> '2' (1 row) SELECT to_tsquery('english', '(a <3> 1) <-> 2'); to_tsquery ------------- '1' <-> '2' (1 row) SELECT to_tsquery('english', 'a <-> (1 <3> 2)'); to_tsquery ------------- '1' <3> '2' (1 row) SELECT to_tsquery('english', '1 <-> (a <3> 2)'); to_tsquery ------------- '1' <4> '2' (1 row) SELECT to_tsquery('english', '1 <-> (2 <3> a)'); to_tsquery ------------- '1' <-> '2' (1 row) SELECT to_tsquery('english', '((a <-> 1) <-> 2) <-> s'); to_tsquery ------------- '1' <-> '2' (1 row) SELECT to_tsquery('english', '(2 <-> (a <-> 1)) <-> s'); to_tsquery ------------- '2' <2> '1' (1 row) SELECT to_tsquery('english', '((1 <-> a) <-> 2) <-> s'); to_tsquery ------------- '1' <2> '2' (1 row) SELECT to_tsquery('english', '(2 <-> (1 <-> a)) <-> s'); to_tsquery ------------- '2' <-> '1' (1 row) SELECT to_tsquery('english', 's <-> ((a <-> 1) <-> 2)'); to_tsquery ------------- '1' <-> '2' (1 row) SELECT to_tsquery('english', 's <-> (2 <-> (a <-> 1))'); to_tsquery ------------- '2' <2> '1' (1 row) SELECT to_tsquery('english', 's <-> ((1 <-> a) <-> 2)'); to_tsquery ------------- '1' <2> '2' (1 row) SELECT to_tsquery('english', 's <-> (2 <-> (1 <-> a))'); to_tsquery ------------- '2' <-> '1' (1 row) SELECT to_tsquery('english', '((a <-> 1) <-> s) <-> 2'); to_tsquery ------------- '1' <2> '2' (1 row) SELECT to_tsquery('english', '(s <-> (a <-> 1)) <-> 2'); to_tsquery ------------- '1' <-> '2' (1 row) SELECT to_tsquery('english', '((1 <-> a) <-> s) <-> 2'); to_tsquery ------------- '1' <3> '2' (1 row) SELECT to_tsquery('english', '(s <-> (1 <-> a)) <-> 2'); to_tsquery ------------- '1' <2> '2' (1 row) SELECT to_tsquery('english', '2 <-> ((a <-> 1) <-> s)'); to_tsquery ------------- '2' <2> '1' (1 row) SELECT to_tsquery('english', '2 <-> (s <-> (a <-> 1))'); to_tsquery ------------- '2' <3> '1' (1 row) SELECT to_tsquery('english', '2 <-> ((1 <-> a) <-> s)'); to_tsquery ------------- '2' <-> '1' (1 row) SELECT to_tsquery('english', '2 <-> (s <-> (1 <-> a))'); to_tsquery ------------- '2' <2> '1' (1 row) SELECT to_tsquery('english', 'foo <-> (a <-> (the <-> bar))'); to_tsquery ----------------- 'foo' <3> 'bar' (1 row) SELECT to_tsquery('english', '((foo <-> a) <-> the) <-> bar'); to_tsquery ----------------- 'foo' <3> 'bar' (1 row) SELECT to_tsquery('english', 'foo <-> a <-> the <-> bar'); to_tsquery ----------------- 'foo' <3> 'bar' (1 row) SELECT phraseto_tsquery('english', 'PostgreSQL can be extended by the user in many ways'); phraseto_tsquery ----------------------------------------------------------- 'postgresql' <3> 'extend' <3> 'user' <2> 'mani' <-> 'way' (1 row) SELECT ts_rank_cd(to_tsvector('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) '), to_tsquery('english', 'paint&water')); ts_rank_cd ------------ 0.05 (1 row) SELECT ts_rank_cd(to_tsvector('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) '), to_tsquery('english', 'breath&motion&water')); ts_rank_cd ------------- 0.008333334 (1 row) SELECT ts_rank_cd(to_tsvector('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) '), to_tsquery('english', 'ocean')); ts_rank_cd ------------ 0.1 (1 row) SELECT ts_rank_cd(to_tsvector('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) '), to_tsquery('english', 'painted <-> Ship')); ts_rank_cd ------------ 0.1 (1 row) SELECT ts_rank_cd(strip(to_tsvector('both stripped')), to_tsquery('both & stripped')); ts_rank_cd ------------ 0 (1 row) SELECT ts_rank_cd(to_tsvector('unstripped') || strip(to_tsvector('stripped')), to_tsquery('unstripped & stripped')); ts_rank_cd ------------ 0 (1 row) --headline tests SELECT ts_headline('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) ', to_tsquery('english', 'paint&water')); ts_headline ----------------------------------------- <b>painted</b> Ocean. + <b>Water</b>, <b>water</b>, every where+ And all the boards did shrink; + <b>Water</b>, <b>water</b>, every (1 row) SELECT ts_headline('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) ', to_tsquery('english', 'breath&motion&water')); ts_headline ---------------------------------- <b>breath</b> nor <b>motion</b>,+ As idle as a painted Ship + Upon a painted Ocean. + <b>Water</b>, <b>water</b> (1 row) SELECT ts_headline('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) ', to_tsquery('english', 'ocean')); ts_headline ---------------------------------- <b>Ocean</b>. + Water, water, every where + And all the boards did shrink;+ Water, water, every where (1 row) SELECT ts_headline('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) ', to_tsquery('english', 'day & drink')); ts_headline ------------------------------------ <b>day</b>, + We stuck, nor breath nor motion,+ As idle as a painted Ship + Upon a painted Ocean. + Water, water, every where + And all the boards did shrink; + Water, water, every where, + Nor any drop (1 row) SELECT ts_headline('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) ', to_tsquery('english', 'day | drink')); ts_headline ----------------------------------------------------------- <b>Day</b> after <b>day</b>, <b>day</b> after <b>day</b>,+ We stuck, nor breath nor motion, + As idle as a painted (1 row) SELECT ts_headline('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) ', to_tsquery('english', 'day | !drink')); ts_headline ----------------------------------------------------------- <b>Day</b> after <b>day</b>, <b>day</b> after <b>day</b>,+ We stuck, nor breath nor motion, + As idle as a painted (1 row) SELECT ts_headline('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) ', to_tsquery('english', 'painted <-> Ship & drink')); ts_headline ---------------------------------- <b>painted</b> <b>Ship</b> + Upon a <b>painted</b> Ocean. + Water, water, every where + And all the boards did shrink;+ Water, water, every where, + Nor any drop to <b>drink</b> (1 row) SELECT ts_headline('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) ', to_tsquery('english', 'painted <-> Ship | drink')); ts_headline --------------------------------- <b>painted</b> <b>Ship</b> + Upon a <b>painted</b> Ocean. + Water, water, every where + And all the boards did shrink (1 row) SELECT ts_headline('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) ', to_tsquery('english', 'painted <-> Ship | !drink')); ts_headline --------------------------------- <b>painted</b> <b>Ship</b> + Upon a <b>painted</b> Ocean. + Water, water, every where + And all the boards did shrink (1 row) SELECT ts_headline('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) ', phraseto_tsquery('english', 'painted Ocean')); ts_headline ---------------------------------- <b>painted</b> <b>Ocean</b>. + Water, water, every where + And all the boards did shrink;+ Water, water, every (1 row) SELECT ts_headline('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) ', phraseto_tsquery('english', 'idle as a painted Ship')); ts_headline --------------------------------------------- <b>idle</b> as a <b>painted</b> <b>Ship</b>+ Upon a <b>painted</b> Ocean. + Water, water, every where + And all the boards (1 row) SELECT ts_headline('english', 'Lorem ipsum urna. Nullam nullam ullamcorper urna.', to_tsquery('english','Lorem') && phraseto_tsquery('english','ullamcorper urna'), 'MaxWords=100, MinWords=1'); ts_headline ------------------------------------------------------------------------------- <b>Lorem</b> ipsum <b>urna</b>. Nullam nullam <b>ullamcorper</b> <b>urna</b> (1 row) SELECT ts_headline('english', 'Lorem ipsum urna. Nullam nullam ullamcorper urna.', phraseto_tsquery('english','ullamcorper urna'), 'MaxWords=100, MinWords=5'); ts_headline ------------------------------------------------------------- <b>urna</b>. Nullam nullam <b>ullamcorper</b> <b>urna</b>. (1 row) SELECT ts_headline('english', ' <html> <!-- some comment --> <body> Sea view wow <u>foo bar</u> <i>qq</i> <a href="http://www.google.com/foo.bar.html" target="_blank">YES </a> ff-bg <script> document.write(15); </script> </body> </html>', to_tsquery('english', 'sea&foo'), 'HighlightAll=true'); ts_headline ----------------------------------------------------------------------------- + <html> + <!-- some comment --> + <body> + <b>Sea</b> view wow <u><b>foo</b> bar</u> <i>qq</i> + <a href="http://www.google.com/foo.bar.html" target="_blank">YES </a>+ ff-bg + <script> + document.write(15); + </script> + </body> + </html> (1 row) SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 <-> 3', 'MaxWords=2, MinWords=1'); ts_headline ------------------- <b>1</b> <b>3</b> (1 row) SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 & 3', 'MaxWords=4, MinWords=1'); ts_headline --------------------- <b>1</b> 2 <b>3</b> (1 row) SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 <-> 3', 'MaxWords=4, MinWords=1'); ts_headline ------------------- <b>1</b> <b>3</b> (1 row) --Check if headline fragments work SELECT ts_headline('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) ', to_tsquery('english', 'ocean'), 'MaxFragments=1'); ts_headline ------------------------------------ after day, + We stuck, nor breath nor motion,+ As idle as a painted Ship + Upon a painted <b>Ocean</b>. + Water, water, every where + And all the boards did shrink; + Water, water, every where, + Nor any drop (1 row) --Check if more than one fragments are displayed SELECT ts_headline('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) ', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2'); ts_headline ---------------------------------------------- after day, day after day, + We <b>stuck</b>, nor breath nor motion, + As idle as a painted Ship + Upon a painted Ocean. + Water, water, every where + And all the boards did shrink; + Water, water, every where ... drop to drink.+ S. T. <b>Coleridge</b> (1 row) --Fragments when there all query words are not in the document SELECT ts_headline('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) ', to_tsquery('english', 'ocean & seahorse'), 'MaxFragments=1'); ts_headline ------------------------------------ + Day after day, day after day, + We stuck, nor breath nor motion,+ As idle as (1 row) --FragmentDelimiter option SELECT ts_headline('english', ' Day after day, day after day, We stuck, nor breath nor motion, As idle as a painted Ship Upon a painted Ocean. Water, water, every where And all the boards did shrink; Water, water, every where, Nor any drop to drink. S. T. Coleridge (1772-1834) ', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2,FragmentDelimiter=***'); ts_headline -------------------------------------------- after day, day after day, + We <b>stuck</b>, nor breath nor motion, + As idle as a painted Ship + Upon a painted Ocean. + Water, water, every where + And all the boards did shrink; + Water, water, every where***drop to drink.+ S. T. <b>Coleridge</b> (1 row) --Fragments with phrase search SELECT ts_headline('english', 'Lorem ipsum urna. Nullam nullam ullamcorper urna.', to_tsquery('english','Lorem') && phraseto_tsquery('english','ullamcorper urna'), 'MaxFragments=100, MaxWords=100, MinWords=1'); ts_headline ------------------------------------------------------------------------------- <b>Lorem</b> ipsum <b>urna</b>. Nullam nullam <b>ullamcorper</b> <b>urna</b> (1 row) -- Edge cases with empty query SELECT ts_headline('english', '', to_tsquery('english', '')); NOTICE: text-search query doesn't contain lexemes: "" ts_headline ------------- (1 row) SELECT ts_headline('english', 'foo bar', to_tsquery('english', '')); NOTICE: text-search query doesn't contain lexemes: "" ts_headline ------------- foo bar (1 row) -- Test for large values of StartSel, StopSel and FragmentDelimiter SELECT ts_headline('english', 'foo barbar', to_tsquery('english', 'foo'), 'StartSel=' || repeat('x', 32768)); ERROR: value for "StartSel" is too long SELECT ts_headline('english', 'foo barbar', to_tsquery('english', 'foo'), 'StopSel=' || repeat('x', 32768)); ERROR: value for "StopSel" is too long SELECT ts_headline('english', 'foo barbar', to_tsquery('english', 'foo'), 'FragmentDelimiter=' || repeat('x', 32768)); ERROR: value for "FragmentDelimiter" is too long --Rewrite sub system CREATE TABLE test_tsquery (txtkeyword TEXT, txtsample TEXT); \set ECHO none ALTER TABLE test_tsquery ADD COLUMN keyword tsquery; UPDATE test_tsquery SET keyword = to_tsquery('english', txtkeyword); ALTER TABLE test_tsquery ADD COLUMN sample tsquery; UPDATE test_tsquery SET sample = to_tsquery('english', txtsample::text); SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new <-> york'; count ------- 2 (1 row) SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new <-> york'; count ------- 3 (1 row) SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new <-> york'; count ------- 1 (1 row) SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new <-> york'; count ------- 4 (1 row) SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new <-> york'; count ------- 3 (1 row) CREATE UNIQUE INDEX bt_tsq ON test_tsquery (keyword); SET enable_seqscan=OFF; SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new <-> york'; count ------- 2 (1 row) SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new <-> york'; count ------- 3 (1 row) SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new <-> york'; count ------- 1 (1 row) SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new <-> york'; count ------- 4 (1 row) SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new <-> york'; count ------- 3 (1 row) RESET enable_seqscan; SELECT ts_rewrite('foo & bar & qq & new & york', 'new & york'::tsquery, 'big & apple | nyc | new & york & city'); ts_rewrite ------------------------------------------------------------------------------ 'foo' & 'bar' & 'qq' & ( 'city' & 'new' & 'york' | 'nyc' | 'big' & 'apple' ) (1 row) SELECT ts_rewrite(ts_rewrite('new & !york ', 'york', '!jersey'), 'jersey', 'mexico'); ts_rewrite -------------------- 'new' & !!'mexico' (1 row) SELECT ts_rewrite('moscow', 'SELECT keyword, sample FROM test_tsquery'::text ); ts_rewrite --------------------- 'moskva' | 'moscow' (1 row) SELECT ts_rewrite('moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'::text ); ts_rewrite ----------------------------------- 'hotel' & ( 'moskva' | 'moscow' ) (1 row) SELECT ts_rewrite('bar & qq & foo & (new <-> york)', 'SELECT keyword, sample FROM test_tsquery'::text ); ts_rewrite ------------------------------------------------------------------------------------- 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' <-> 'appl' | 'new' <-> 'york' ) (1 row) SELECT ts_rewrite( 'moscow', 'SELECT keyword, sample FROM test_tsquery'); ts_rewrite --------------------- 'moskva' | 'moscow' (1 row) SELECT ts_rewrite( 'moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'); ts_rewrite ----------------------------------- 'hotel' & ( 'moskva' | 'moscow' ) (1 row) SELECT ts_rewrite( 'bar & qq & foo & (new <-> york)', 'SELECT keyword, sample FROM test_tsquery'); ts_rewrite ------------------------------------------------------------------------------------- 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' <-> 'appl' | 'new' <-> 'york' ) (1 row) SELECT ts_rewrite('1 & (2 <-> 3)', 'SELECT keyword, sample FROM test_tsquery'::text ); ts_rewrite ------------- '2' <-> '4' (1 row) SELECT ts_rewrite('1 & (2 <2> 3)', 'SELECT keyword, sample FROM test_tsquery'::text ); ts_rewrite ------------------- '1' & '2' <2> '3' (1 row) SELECT ts_rewrite('5 <-> (1 & (2 <-> 3))', 'SELECT keyword, sample FROM test_tsquery'::text ); ts_rewrite ------------------------- '5' <-> ( '2' <-> '4' ) (1 row) SELECT ts_rewrite('5 <-> (6 | 8)', 'SELECT keyword, sample FROM test_tsquery'::text ); ts_rewrite ----------------------- '5' <-> ( '6' | '8' ) (1 row) -- Check empty substitution SELECT ts_rewrite(to_tsquery('5 & (6 | 5)'), to_tsquery('5'), to_tsquery('')); NOTICE: text-search query doesn't contain lexemes: "" ts_rewrite ------------ '6' (1 row) SELECT ts_rewrite(to_tsquery('!5'), to_tsquery('5'), to_tsquery('')); NOTICE: text-search query doesn't contain lexemes: "" ts_rewrite ------------ (1 row) SELECT keyword FROM test_tsquery WHERE keyword @> 'new'; keyword ------------------ 'new' <-> 'york' (1 row) SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow'; keyword ---------- 'moscow' (1 row) SELECT keyword FROM test_tsquery WHERE keyword <@ 'new'; keyword --------- (0 rows) SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow'; keyword ---------- 'moscow' (1 row) SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; ts_rewrite --------------------- 'moskva' | 'moscow' (1 row) SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; ts_rewrite ----------------------------------- 'hotel' & ( 'moskva' | 'moscow' ) (1 row) SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & qq & foo & (new <-> york)') AS query; ts_rewrite ------------------------------------------------------------------------------------- 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' <-> 'appl' | 'new' <-> 'york' ) (1 row) SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; ts_rewrite --------------------- 'moskva' | 'moscow' (1 row) SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; ts_rewrite ----------------------------------- 'hotel' & ( 'moskva' | 'moscow' ) (1 row) SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & qq & foo & (new <-> york)') AS query; ts_rewrite ------------------------------------------------------------------------------------- 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' <-> 'appl' | 'new' <-> 'york' ) (1 row) CREATE INDEX qq ON test_tsquery USING gist (keyword tsquery_ops); SET enable_seqscan=OFF; SELECT keyword FROM test_tsquery WHERE keyword @> 'new'; keyword ------------------ 'new' <-> 'york' (1 row) SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow'; keyword ---------- 'moscow' (1 row) SELECT keyword FROM test_tsquery WHERE keyword <@ 'new'; keyword --------- (0 rows) SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow'; keyword ---------- 'moscow' (1 row) SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; ts_rewrite --------------------- 'moskva' | 'moscow' (1 row) SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; ts_rewrite ----------------------------------- 'hotel' & ( 'moskva' | 'moscow' ) (1 row) SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & qq & foo & (new <-> york)') AS query; ts_rewrite ------------------------------------------------------------------------------------- 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' <-> 'appl' | 'new' <-> 'york' ) (1 row) SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; ts_rewrite --------------------- 'moskva' | 'moscow' (1 row) SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; ts_rewrite ----------------------------------- 'hotel' & ( 'moskva' | 'moscow' ) (1 row) SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & qq & foo & (new <-> york)') AS query; ts_rewrite ------------------------------------------------------------------------------------- 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | 'big' <-> 'appl' | 'new' <-> 'york' ) (1 row) SELECT ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz'); ts_rewrite ----------------------------------------- ( 'bar' | 'baz' ) <-> ( 'bar' | 'baz' ) (1 row) SELECT to_tsvector('foo bar') @@ ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz'); ?column? ---------- f (1 row) SELECT to_tsvector('bar baz') @@ ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz'); ?column? ---------- t (1 row) RESET enable_seqscan; --test GUC SET default_text_search_config=simple; SELECT to_tsvector('SKIES My booKs'); to_tsvector ---------------------------- 'books':3 'my':2 'skies':1 (1 row) SELECT plainto_tsquery('SKIES My booKs'); plainto_tsquery -------------------------- 'skies' & 'my' & 'books' (1 row) SELECT to_tsquery('SKIES & My | booKs'); to_tsquery -------------------------- 'skies' & 'my' | 'books' (1 row) SET default_text_search_config=english; SELECT to_tsvector('SKIES My booKs'); to_tsvector ------------------ 'book':3 'sky':1 (1 row) SELECT plainto_tsquery('SKIES My booKs'); plainto_tsquery ----------------- 'sky' & 'book' (1 row) SELECT to_tsquery('SKIES & My | booKs'); to_tsquery ---------------- 'sky' | 'book' (1 row) --trigger CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON test_tsvector FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(a, 'pg_catalog.english', t); SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); count ------- 0 (1 row) INSERT INTO test_tsvector (t) VALUES ('345 qwerty'); SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); count ------- 1 (1 row) UPDATE test_tsvector SET t = null WHERE t = '345 qwerty'; SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); count ------- 0 (1 row) INSERT INTO test_tsvector (t) VALUES ('345 qwerty'); SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); count ------- 1 (1 row) -- Test inlining of immutable constant functions -- to_tsquery(text) is not immutable, so it won't be inlined explain (costs off) select * from test_tsquery, to_tsquery('new') q where txtsample @@ q; QUERY PLAN ------------------------------------------------ Nested Loop Join Filter: (test_tsquery.txtsample @@ q.q) -> Function Scan on to_tsquery q -> Seq Scan on test_tsquery (4 rows) -- to_tsquery(regconfig, text) is an immutable function. -- That allows us to get rid of using function scan and join at all. explain (costs off) select * from test_tsquery, to_tsquery('english', 'new') q where txtsample @@ q; QUERY PLAN --------------------------------------------- Seq Scan on test_tsquery Filter: (txtsample @@ '''new'''::tsquery) (2 rows) -- test finding items in GIN's pending list create temp table pendtest (ts tsvector); create index pendtest_idx on pendtest using gin(ts); insert into pendtest values (to_tsvector('Lore ipsam')); insert into pendtest values (to_tsvector('Lore ipsum')); select * from pendtest where 'ipsu:*'::tsquery @@ ts; ts -------------------- 'ipsum':2 'lore':1 (1 row) select * from pendtest where 'ipsa:*'::tsquery @@ ts; ts -------------------- 'ipsam':2 'lore':1 (1 row) select * from pendtest where 'ips:*'::tsquery @@ ts; ts -------------------- 'ipsam':2 'lore':1 'ipsum':2 'lore':1 (2 rows) select * from pendtest where 'ipt:*'::tsquery @@ ts; ts ---- (0 rows) select * from pendtest where 'ipi:*'::tsquery @@ ts; ts ---- (0 rows) --check OP_PHRASE on index create temp table phrase_index_test(fts tsvector); insert into phrase_index_test values ('A fat cat has just eaten a rat.'); insert into phrase_index_test values (to_tsvector('english', 'A fat cat has just eaten a rat.')); create index phrase_index_test_idx on phrase_index_test using gin(fts); set enable_seqscan = off; select * from phrase_index_test where fts @@ phraseto_tsquery('english', 'fat cat'); fts ----------------------------------- 'cat':3 'eaten':6 'fat':2 'rat':8 (1 row) set enable_seqscan = on; -- test websearch_to_tsquery function select websearch_to_tsquery('simple', 'I have a fat:*ABCD cat'); websearch_to_tsquery --------------------------------------------- 'i' & 'have' & 'a' & 'fat' & 'abcd' & 'cat' (1 row) select websearch_to_tsquery('simple', 'orange:**AABBCCDD'); websearch_to_tsquery ----------------------- 'orange' & 'aabbccdd' (1 row) select websearch_to_tsquery('simple', 'fat:A!cat:B|rat:C<'); websearch_to_tsquery ----------------------------------------- 'fat' & 'a' & 'cat' & 'b' & 'rat' & 'c' (1 row) select websearch_to_tsquery('simple', 'fat:A : cat:B'); websearch_to_tsquery --------------------------- 'fat' & 'a' & 'cat' & 'b' (1 row) select websearch_to_tsquery('simple', 'fat*rat'); websearch_to_tsquery ---------------------- 'fat' <-> 'rat' (1 row) select websearch_to_tsquery('simple', 'fat-rat'); websearch_to_tsquery ------------------------------- 'fat-rat' <-> 'fat' <-> 'rat' (1 row) select websearch_to_tsquery('simple', 'fat_rat'); websearch_to_tsquery ---------------------- 'fat' <-> 'rat' (1 row) -- weights are completely ignored select websearch_to_tsquery('simple', 'abc : def'); websearch_to_tsquery ---------------------- 'abc' & 'def' (1 row) select websearch_to_tsquery('simple', 'abc:def'); websearch_to_tsquery ---------------------- 'abc' & 'def' (1 row) select websearch_to_tsquery('simple', 'a:::b'); websearch_to_tsquery ---------------------- 'a' & 'b' (1 row) select websearch_to_tsquery('simple', 'abc:d'); websearch_to_tsquery ---------------------- 'abc' & 'd' (1 row) select websearch_to_tsquery('simple', ':'); NOTICE: text-search query contains only stop words or doesn't contain lexemes, ignored websearch_to_tsquery ---------------------- (1 row) -- these operators are ignored select websearch_to_tsquery('simple', 'abc & def'); websearch_to_tsquery ---------------------- 'abc' & 'def' (1 row) select websearch_to_tsquery('simple', 'abc | def'); websearch_to_tsquery ---------------------- 'abc' & 'def' (1 row) select websearch_to_tsquery('simple', 'abc <-> def'); websearch_to_tsquery ---------------------- 'abc' & 'def' (1 row) -- parens are ignored, too select websearch_to_tsquery('simple', 'abc (pg or class)'); websearch_to_tsquery ------------------------ 'abc' & 'pg' | 'class' (1 row) select websearch_to_tsquery('simple', '(foo bar) or (ding dong)'); websearch_to_tsquery --------------------------------- 'foo' & 'bar' | 'ding' & 'dong' (1 row) -- NOT is ignored in quotes select websearch_to_tsquery('english', 'My brand new smartphone'); websearch_to_tsquery ------------------------------- 'brand' & 'new' & 'smartphon' (1 row) select websearch_to_tsquery('english', 'My brand "new smartphone"'); websearch_to_tsquery --------------------------------- 'brand' & 'new' <-> 'smartphon' (1 row) select websearch_to_tsquery('english', 'My brand "new -smartphone"'); websearch_to_tsquery --------------------------------- 'brand' & 'new' <-> 'smartphon' (1 row) -- test OR operator select websearch_to_tsquery('simple', 'cat or rat'); websearch_to_tsquery ---------------------- 'cat' | 'rat' (1 row) select websearch_to_tsquery('simple', 'cat OR rat'); websearch_to_tsquery ---------------------- 'cat' | 'rat' (1 row) select websearch_to_tsquery('simple', 'cat "OR" rat'); websearch_to_tsquery ---------------------- 'cat' & 'or' & 'rat' (1 row) select websearch_to_tsquery('simple', 'cat OR'); websearch_to_tsquery ---------------------- 'cat' & 'or' (1 row) select websearch_to_tsquery('simple', 'OR rat'); websearch_to_tsquery ---------------------- 'or' & 'rat' (1 row) select websearch_to_tsquery('simple', '"fat cat OR rat"'); websearch_to_tsquery ------------------------------------ 'fat' <-> 'cat' <-> 'or' <-> 'rat' (1 row) select websearch_to_tsquery('simple', 'fat (cat OR rat'); websearch_to_tsquery ----------------------- 'fat' & 'cat' | 'rat' (1 row) select websearch_to_tsquery('simple', 'or OR or'); websearch_to_tsquery ---------------------- 'or' | 'or' (1 row) -- OR is an operator here ... select websearch_to_tsquery('simple', '"fat cat"or"fat rat"'); websearch_to_tsquery ----------------------------------- 'fat' <-> 'cat' | 'fat' <-> 'rat' (1 row) select websearch_to_tsquery('simple', 'fat or(rat'); websearch_to_tsquery ---------------------- 'fat' | 'rat' (1 row) select websearch_to_tsquery('simple', 'fat or)rat'); websearch_to_tsquery ---------------------- 'fat' | 'rat' (1 row) select websearch_to_tsquery('simple', 'fat or&rat'); websearch_to_tsquery ---------------------- 'fat' | 'rat' (1 row) select websearch_to_tsquery('simple', 'fat or|rat'); websearch_to_tsquery ---------------------- 'fat' | 'rat' (1 row) select websearch_to_tsquery('simple', 'fat or!rat'); websearch_to_tsquery ---------------------- 'fat' | 'rat' (1 row) select websearch_to_tsquery('simple', 'fat or<rat'); websearch_to_tsquery ---------------------- 'fat' | 'rat' (1 row) select websearch_to_tsquery('simple', 'fat or>rat'); websearch_to_tsquery ---------------------- 'fat' | 'rat' (1 row) select websearch_to_tsquery('simple', 'fat or '); websearch_to_tsquery ---------------------- 'fat' & 'or' (1 row) -- ... but not here select websearch_to_tsquery('simple', 'abc orange'); websearch_to_tsquery ---------------------- 'abc' & 'orange' (1 row) select websearch_to_tsquery('simple', 'abc OR1234'); websearch_to_tsquery ---------------------- 'abc' & 'or1234' (1 row) select websearch_to_tsquery('simple', 'abc or-abc'); websearch_to_tsquery ------------------------------------- 'abc' & 'or-abc' <-> 'or' <-> 'abc' (1 row) select websearch_to_tsquery('simple', 'abc OR_abc'); websearch_to_tsquery ------------------------ 'abc' & 'or' <-> 'abc' (1 row) -- test quotes select websearch_to_tsquery('english', '"pg_class pg'); websearch_to_tsquery --------------------------- 'pg' <-> 'class' <-> 'pg' (1 row) select websearch_to_tsquery('english', 'pg_class pg"'); websearch_to_tsquery ------------------------- 'pg' <-> 'class' & 'pg' (1 row) select websearch_to_tsquery('english', '"pg_class pg"'); websearch_to_tsquery --------------------------- 'pg' <-> 'class' <-> 'pg' (1 row) select websearch_to_tsquery('english', '"pg_class : pg"'); websearch_to_tsquery --------------------------- 'pg' <-> 'class' <-> 'pg' (1 row) select websearch_to_tsquery('english', 'abc "pg_class pg"'); websearch_to_tsquery ----------------------------------- 'abc' & 'pg' <-> 'class' <-> 'pg' (1 row) select websearch_to_tsquery('english', '"pg_class pg" def'); websearch_to_tsquery ----------------------------------- 'pg' <-> 'class' <-> 'pg' & 'def' (1 row) select websearch_to_tsquery('english', 'abc "pg pg_class pg" def'); websearch_to_tsquery ---------------------------------------------------- 'abc' & 'pg' <-> 'pg' <-> 'class' <-> 'pg' & 'def' (1 row) select websearch_to_tsquery('english', ' or "pg pg_class pg" or '); websearch_to_tsquery ------------------------------------ 'pg' <-> 'pg' <-> 'class' <-> 'pg' (1 row) select websearch_to_tsquery('english', '""pg pg_class pg""'); websearch_to_tsquery -------------------------------- 'pg' & 'pg' <-> 'class' & 'pg' (1 row) select websearch_to_tsquery('english', 'abc """"" def'); websearch_to_tsquery ---------------------- 'abc' & 'def' (1 row) select websearch_to_tsquery('english', 'cat -"fat rat"'); websearch_to_tsquery ------------------------------ 'cat' & !( 'fat' <-> 'rat' ) (1 row) select websearch_to_tsquery('english', 'cat -"fat rat" cheese'); websearch_to_tsquery ---------------------------------------- 'cat' & !( 'fat' <-> 'rat' ) & 'chees' (1 row) select websearch_to_tsquery('english', 'abc "def -"'); websearch_to_tsquery ---------------------- 'abc' & 'def' (1 row) select websearch_to_tsquery('english', 'abc "def :"'); websearch_to_tsquery ---------------------- 'abc' & 'def' (1 row) select websearch_to_tsquery('english', '"A fat cat" has just eaten a -rat.'); websearch_to_tsquery ------------------------------------ 'fat' <-> 'cat' & 'eaten' & !'rat' (1 row) select websearch_to_tsquery('english', '"A fat cat" has just eaten OR !rat.'); websearch_to_tsquery ----------------------------------- 'fat' <-> 'cat' & 'eaten' | 'rat' (1 row) select websearch_to_tsquery('english', '"A fat cat" has just (+eaten OR -rat)'); websearch_to_tsquery ------------------------------------ 'fat' <-> 'cat' & 'eaten' | !'rat' (1 row) select websearch_to_tsquery('english', 'this is ----fine'); websearch_to_tsquery ---------------------- !!!!'fine' (1 row) select websearch_to_tsquery('english', '(()) )))) this ||| is && -fine, "dear friend" OR good'); websearch_to_tsquery ---------------------------------------- !'fine' & 'dear' <-> 'friend' | 'good' (1 row) select websearch_to_tsquery('english', 'an old <-> cat " is fine &&& too'); websearch_to_tsquery ------------------------ 'old' & 'cat' & 'fine' (1 row) select websearch_to_tsquery('english', '"A the" OR just on'); NOTICE: text-search query contains only stop words or doesn't contain lexemes, ignored websearch_to_tsquery ---------------------- (1 row) select websearch_to_tsquery('english', '"a fat cat" ate a rat'); websearch_to_tsquery --------------------------------- 'fat' <-> 'cat' & 'ate' & 'rat' (1 row) select to_tsvector('english', 'A fat cat ate a rat') @@ websearch_to_tsquery('english', '"a fat cat" ate a rat'); ?column? ---------- t (1 row) select to_tsvector('english', 'A fat grey cat ate a rat') @@ websearch_to_tsquery('english', '"a fat cat" ate a rat'); ?column? ---------- f (1 row) -- cases handled by gettoken_tsvector() select websearch_to_tsquery(''''); NOTICE: text-search query contains only stop words or doesn't contain lexemes, ignored websearch_to_tsquery ---------------------- (1 row) select websearch_to_tsquery('''abc''''def'''); websearch_to_tsquery ---------------------- 'abc' <-> 'def' (1 row) select websearch_to_tsquery('\abc'); websearch_to_tsquery ---------------------- 'abc' (1 row) select websearch_to_tsquery('\'); NOTICE: text-search query contains only stop words or doesn't contain lexemes, ignored websearch_to_tsquery ---------------------- (1 row)