Full text search on substring in PostgreSQL

Question

I am trying to retrieve the data from the a database using to_tsquery() on the basis of text search. I am able to get the results for complete word search but not for the substring.

Example:

SELECT * FROM snp_gene_context WHERE protein_name @@ to_tsquery('english','KIN') AND gene_dist='0';

I am able to get the rows containing the word 'KIN11' but I am unable to get rows containing 'Kinase'.

can u explain how to do case insensitive search to_tsquery()? i searched with 'Kin' and 'kin' now and got same results as above mentioned. — Dadu
– Dadu, Commented May 15, 2015 at 6:40
Do you really need full text search for that? Why not just search for '%kin%'? — user330315
– user330315, Commented May 15, 2015 at 6:55
If you only interested in prefix matching, you can use to_tsquery('kin:*') -- for a more custom containment testing, you can use like / ilike with pg_trgm index (what Tometzky already mentioned). — pozs
– pozs, Commented May 15, 2015 at 7:55

Tometzky · Accepted Answer · 2015-05-15 07:16:14Z

Full text search doesn't work on substrings — only words.

For indexed substring searches you'd need to use pg_trgm extension. There's a good article about faster like/ilike searches using pg_trgm on select * from depesz blog. There's a trade-of though — much slower writes.

Collectives™ on Stack Overflow

Full text search on substring in PostgreSQL

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related