CODEWITHSUNDEEP
postgresqlfull-text-searchstring-matchingtsvector
Tosh
Tosh

Reputation: 281

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

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.

Upvotes: 0

Views: 944

Answers (1)

Neil McGuigan
Neil McGuigan

Reputation: 48246

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

Upvotes: 1

Related Questions