Postgres full text search not working for some terms

Question

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.

Frank Heikens · Accepted Answer · 2021-02-19 13:36:43Z

'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

jjanes · Accepted Answer · 2021-02-19 15:41:11Z

'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.

Collectives™ on Stack Overflow

Postgres full text search not working for some terms

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related