| set optimizer_print_missing_stats = off; |
| -- |
| -- 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) |
| |
| -- 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 |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Scan using wowidx on test_tsvector |
| Index Cond: (a @@ '''wr'' | ''qh'''::tsquery) |
| Filter: (a @@ '''wr'' | ''qh'''::tsquery) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (7 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 |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Scan using wowidx on test_tsvector |
| Index Cond: (a @@ '''wr'' | ''qh'''::tsquery) |
| Filter: (a @@ '''wr'' | ''qh'''::tsquery) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (7 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 "8168". |
| CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(siglen=8192)); |
| ERROR: value 8192 out of bounds for option "siglen" |
| DETAIL: Valid values are between "1" and "8168". |
| 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')) |
| Distributed by: (t) |
| |
| DROP INDEX wowidx; |
| EXPLAIN (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; |
| QUERY PLAN |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Scan using wowidx2 on test_tsvector |
| Index Cond: (a @@ '''wr'' | ''qh'''::tsquery) |
| Filter: (a @@ '''wr'' | ''qh'''::tsquery) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (7 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')) |
| Distributed by: (t) |
| |
| EXPLAIN (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; |
| QUERY PLAN |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Scan using wowidx on test_tsvector |
| Index Cond: (a @@ '''wr'' | ''qh'''::tsquery) |
| Filter: (a @@ '''wr'' | ''qh'''::tsquery) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (7 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; |
| 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 |
| --------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Seq Scan on test_tsvector |
| Filter: (a @@ '''wr'' | ''qh'''::tsquery) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (6 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 |
| ------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Seq Scan on test_tsvector |
| Filter: (a @@ '!''qh'''::tsquery) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (6 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 |
| -------------------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Seq Scan on test_tsvector |
| Filter: ((a @@ '''wr'''::tsquery) AND (a @@ '!''qh'''::tsquery)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (6 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) |
| ', phraseto_tsquery('english', 'painted Ocean')); |
| ts_headline |
| --------------------------------------- |
| <b>painted</b> Ship + |
| Upon a <b>painted</b> <b>Ocean</b>.+ |
| 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', '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', ' |
| <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>3</b> <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) |
| |
| --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 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 |
| -- GPDB doesn't allow updating the distribution key, so create a synthetic |
| -- distribution key column. |
| alter table test_tsvector add column distkey int4; |
| alter table test_tsvector set distributed by (distkey); |
| 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 |
| ---------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Nested Loop |
| Join Filter: (txtsample @@ ('''new'''::tsquery)) |
| -> Seq Scan on test_tsquery |
| -> Materialize |
| -> Result |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (7 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 |
| ---------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Nested Loop |
| Join Filter: (txtsample @@ ('''new'''::tsquery)) |
| -> Seq Scan on test_tsquery |
| -> Materialize |
| -> Result |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (7 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) |
| |
| select websearch_to_tsquery('simple', 'abc (pg or class)'); |
| websearch_to_tsquery |
| ------------------------ |
| 'abc' & 'pg' | 'class' |
| (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) |
| |
| COPY test_tsvector TO '/tmp/test_tsvector.txt'; |
| COPY test_tsvector FROM '/tmp/test_tsvector.txt'; |
