PostgreSQL Full Text Search: Cant get a partial match of tsvector

Question

Here's the problem:

I have a table in PostgreSQL with adresses in plain text and tsvectors. And i'm trying to find an adress record in a query like this.

SELECT * FROM address_catalog
WHERE address_catalog.search_vector @@ to_tsquery('123456:* & Klingon:* & Empire:* & Kronos:* & city:* & Matrok:* & street:* & 789:*')

But the problem is that I don't know anything about the adress in a query. I can't define where a country, a city or a street is in the incoming string. I don't know what order of words the adress has, or does it contain extra words.

I can only search for countries and cities, but if the incoming string contains street, index or anything else, the search returns nothing because of the conjunction of all vector tokens. At the same time, I simply can't delete some string parts or use disjunction, because I never know where in the string the extra words are.

So, is there any way to construct a tsquery to return some best matches for the incoming string? Or maybe partial matches? When i tried to force it to use OR instead of AND everywhere in tsquery, it returned me nearly the whole database. I need vectors intersection... in postgresql.

Neil McGuigan · Accepted Answer · 2016-04-29 18:47:16Z

1

I'd recommend using the smlar (PDF) extension for this. It was written by the same guys that wrote text search. It lets you use the TF-IDF similarity measure, which allows for "extraneous" query terms

Here's how to compile it (I haven't figured out how to compile it on Windows):

http://blog.databasepatterns.com/2014/07/postgresql-install-smlar-extension.html

And here's how to use it:

http://blog.databasepatterns.com/2014/08/tf-idf-text-search-in-postgres.html

answered Apr 29, 2016 at 18:47

Neil McGuigan

48.5k12 gold badges130 silver badges156 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL Full Text Search: Cant get a partial match of tsvector

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