fuzzy full text search in postgres

Question

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

*"fuzzy searches"* can be pretty much anything. Please explain *exactly* what you are trying to achieve. And add your table definition, cardinalities, Postgres version. — Erwin Brandstetter, Dec 26 '16 at 23:34
Updated question, I hope this is clearer. Thanks for the tips! — user3791980, Dec 26 '16 at 23:44
A table definition includes data types and constraints. Example: http://stackoverflow.com/a/9790225/939860 — Erwin Brandstetter, Dec 27 '16 at 00:09

score 2 · Answer 1 · edited May 23 '17 at 12:00

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:

PostgreSQL LIKE query performance variations

fuzzy full text search in postgres

1 Answers1