Skip to content
Advertisement

Postgres Like to Full Text Search

I attempting to create a query to find the name other in my sql database. I have a basic like search as follow and would like to use a full text search instead.

Like Query

SELECT g.*, COUNT(*) OVER() AS total 
FROM group AS g 
WHERE UPPER(g.name) LIKE UPPER('oth%')

Full Text Query

SELECT g.*, COUNT(*) OVER() AS total 
FROM group AS g 
WHERE to_tsvector(g.name) @@ to_tsquery('oth:*') 

It appears that my full text returns 0 unlike my like search does. Why is this so when it appears that both queries appear to be doing similar searches

Advertisement

Answer

It looks like ‘other’ is in the default stop word list in english. I have tested with PostgreSQL 12 at Linux level:

$ grep other /usr/pgsql-12/share/tsearch_data/english.stop 
other

In the database:

postgres=# select to_tsvector('french','other');
 to_tsvector 
-------------
 'other':1
(1 row)

postgres=# select to_tsvector('english','other');
 to_tsvector 
-------------

(1 row)

postgres=# select to_tsvector('english','others');
 to_tsvector 
-------------
 'other':1
(1 row)

postgres=# select to_tsvector('english','another');
 to_tsvector 
-------------
 'anoth':1
(1 row)

Try ‘another’.

6 People found this is helpful
Advertisement