Postgres Like to Full Text Search

Question

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

pifor · Accepted Answer · 2020-04-17 18:24:02Z

1

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

answered Apr 17, 2020 at 18:24

pifor

7,9722 gold badges11 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Like to Full Text Search

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related