Postgresql – How to replace to_tsquery by phraseto_tsquery in PostgreSQL

full-text-searchpostgresql

For word suggestion when do search.

I can query like

select title from test where ts_filter(tsv, '{A}') @@ to_tsquery('english_nostop', 'god<->o' || ':*')

# tsv is something like to_tsvector('english_nostop', test.title)

The <-> was something like pattern = pattern.replace(' ', '<->'). That may have some problem when pattern contain <. Thought we can remove such char.

Combine tsquery don't work here. I can't find away to do to_tsquery(':*')

But how to use phraseto_tsquery to replace to_tsquery ? And still can have :* work?

After @Laurenz Albe answer. phraseto_tsquery can't do this.

May be I need give more detail. Why I want to use phraseto_tsquery.

I use the

CREATE TEXT SEARCH DICTIONARY english_stem_nostop (
    Template = snowball
    , Language = english
);

CREATE TEXT SEARCH CONFIGURATION public.english_nostop ( COPY = pg_catalog.english );
ALTER TEXT SEARCH CONFIGURATION public.english_nostop
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, hword, hword_part, word WITH english_stem_nostop;

To keep stop words can be searched.

But I need to change pattern like this in python:

pattern = info["pattern"]
replace_punctuation = str.maketrans(string.punctuation, ' ' * len(string.punctuation))
pattern = pattern.translate(replace_punctuation)
replace_space = pattern.replace(" ", '<->')

You can see I replace punctuation by ' '. Then replace ' ' with <->.

I came up this question. Because I wonder can I don't transform pattern in python and just use psql to transform pattern.

Why need both ':*' and <->. here is a example:

select title from book where ts_filter(tsv_nostop, '{A}') @@ to_tsquery('english_nostop', 'how<->t'||':*')
except
select title from book where ts_filter(tsv_nostop, '{A}') @@ to_tsquery('english_nostop', 'how<->t');

This will show title contain how to. When user input how t. We want pop up some title.

Best Answer

You cannot do that with phraseto_tsquery.

The lower-level functions provide more features that you cannot get with phraseto_tsquery or websearch_to_tsquery.

Related Question