Postgres full text search not working for some terms

user_14313451 0
Viewed 309

I am using Postgres FTS to implement search. This is my sample tsvector:

{'analyst':2A 'busi':1A}

The query I am using is

SELECT * FROM table_name WHERE tsv @@ to_tsquery('english', 'b:*')

The result is showing correctly, but if I use 'a:*' instead of 'b:*',

SELECT * FROM table_name WHERE tsv @@ to_tsquery('english', 'a:*')

The result is an empty set.

Is this some bug in PostgreSQL serach? I am using PostgreSQL version 12.6.

postgresql-12 full-text-search postgresql
2 Answers

'a:' isn't a valid lexeme in English:

SELECT to_tsquery('english', 'a:')

NOTICE: text-search query contains only stop words or doesn't contain lexemes, ignored

user_2719590

'a' is a stopword, and to_tsquery removes it even if it already decorated with ':*'. I don't what the rationale for that is, it is not very intuitive to me.

So you will have to avoid the use of to_tsquery. You could instead do 'a:*'::tsquery.

user_17212390
Related