Hello All,
I have the following table:
postgres=# \d so_rum;
Table "public.so_rum"
Column | Type | Collation | Nullable | Default
-----------+-------------------------+-----------+----------+---------
id | integer | | |
title | character varying(1000) | | |
posts | text | | |
body | tsvector | | |
parent_id | integer | | |
Indexes:
"so_rum_body_idx" rum (body)
I wanted to do phrase search query, so I came up with the below query, for
example:
select id from so_rum
where body @@ phraseto_tsquery('english','Is it possible to toggle
the visibility');
This gives me the results, which only match's the entire text. However,
there are documents, where the distance between lexmes are more and the
above query doesn't gives me back those data. For example: `'it is
something possible to do toggle between the. . . visibility'` doesn't get
returned. I know I can get it returned with `<2>` (for example) distance
operator by giving in the `to_tsquery`, manually.
But I wanted to understand, how to do this in my sql statement itself, so
that I get the results first with distance of `1` and then `2` and so on
(may be till `6-7`). Finally append results with the actual count of the
search words like the following query:
select count(id) from so_rum
where body @@ to_tsquery('english','string & string . . . ')
Is it possible to do in a single query with good performance?
