How to create trigram by joining two columns

by user158781   Last Updated August 18, 2018 15:06 PM

I am new to sql so this might be overly simple question that I am confused about. I am trying to create trigram index by joining three columns (images, tags, images_tags). I used string concatenation to create ts_vector index following this article but I failed to convert that index to trigram index.

Here's my ts_vector code which is working:

SELECT image_id, image_title
FROM (SELECT  images.id as image_id, images.title as image_title,
          to_tsvector(images.title) || 
          to_tsvector(images.description) ||
          to_tsvector(string_agg(tags.title, ' ')) as document
  FROM images
  JOIN images_tags ON images_tags.image_id = images.id
  JOIN tags ON tags.id = images_tags.tag_id
  GROUP BY images.id) p_search
WHERE p_search.document @@ to_tsquery('space')

Here's trigram code which isn't working:

SELECT image_id, image_title FROM
(SELECT images.id as image_id, images.title as image_title,
      images.title || ' ' ||
      images.description || ' ' ||
      string_agg(tags.title, ' ') as document
  FROM images
  JOIN images_tags ON images_tags.image_id = images.id
  JOIN tags ON tags.id = images_tags.tag_id
  GROUP BY images.id) p_search
WHERE p_search.document % 'space'

Trigram returns 0 rows everytime. I am following this article for pg_trgm



Related Questions


Performing search on a column by query

Updated May 22, 2015 22:02 PM

Fuzzy Matching with Postgresql 9.3

Updated April 06, 2017 14:06 PM