title: fuzzy full text search in postgres
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>I am using postgres 9.5.5.</p>
  
  <pre><code>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
      '
  );
  </code></pre>
  
  <p>TABLE locations<br>
  name<br>
  street<br>
  state<br>
  city<br>
  zip<br>
  geolocation</p>