CODEWITHSUNDEEP
sqlpostgresql
user3791980
user3791980

Reputation: 445

fuzzy full text search in postgres

I'm trying to implement search on a web application where I can do a full text search on multiple fields within the table that also support partial or slightly off query matches.

My plan is to create a materialized view and then create a index for fuzzy (trigram) searches. But I'm not quite sure if that is the correct way to go about it.

Here is what I have so far. I'm pretty sure the SQL is incorrect but I don't really know how to correct it. I'm still kind of new to it. First time working outside of an ORM.

I am using postgres 9.5.5.

CREATE MATERIALIZED VIEW search AS
SELECT word FROM ts_stat(
    'SELECT (
        setweight(to_tsvector(''simple'', location.name), ''A'')
        || setweight(to_tsvector(''simple'',locations.street), ''B'')
        || setweight(to_tsvector(''simple'',locations.state), ''C'')
        || setweight(to_tsvector(''simple'',locations.city), ''C'')
        || setweight(to_tsvector(''simple'',locations.zip,), ''B'') AS document,
    )
    FROM locations
    GROUP BY locations.id
    '
);

TABLE locations
name
street
state
city
zip
geolocation

Upvotes: 2

Views: 3352

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

The materialized view definition in the question does not seem to make any sense at all. ts_stat() is for debugging a text search setup.

Full text search is completely distinct from pattern matching with LIKE or regular expressions (using trigram indexes).

Fuzzy pattern matching on multiple columns for "slightly off" patterns is not trivial at all. The setup needs to consider your exact requirements to get the "right" results and exclude the "wrong" ones, while keeping performance decent.

To get you started:

Upvotes: 2

Related Questions