CODEWITHSUNDEEP
postgresqlfull-text-searchpostgresql-12
Nimisha Shukla
Nimisha Shukla

Reputation: 75

Postgres full text search not working for some terms

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.

Upvotes: 2

Views: 635

Answers (2)

jjanes
jjanes

Reputation: 44137

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

Upvotes: 2

Frank Heikens
Frank Heikens

Reputation: 127056

'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

Upvotes: 3

Related Questions