Postgres Full text search in Array Column

Question

I have posts that has a tags column. I would like to be able to do a full text search on tags. For the VARCHAR columns that I used:

 CREATE INDEX posts_fts_idx ON posts USING gin(to_tsvector('english', coalesce(title, '')); SELECT "posts".* FROM "posts" WHERE (to_tsvector('english', coalesce(title, '')) @@ (to_tsquery('english', 'ruby'))); 

However, for character varying[] , the to_tsvector function to_tsvector not exist. How can a request be written that will work against each of the tags (ideally suited if any one tag matches)?

Note: I see that it would be fairly easy to do the conversion to string ( array_to_string ), but if possible, I would like to convert each individual tag to tsvector .

Answer 1

You can index a character that is modified with gin for search parameters. Try the following:

 CREATE INDEX idx_post_tag ON posts USING GIN(tags); SELECT * FROM posts WHERE tags @> (ARRAY['search string'::character varying]); 

This is when an exact match is required. If an exact match is undesirable, you should consider storing your tags as a text column. Think more about the meaning of these "tags." String array types do not have text indexing, stem support, and inflection, and therefore you cannot match things like Dance to Dance.

If this is not an option, you can work around this with a fixed version of the array_to_string function. Then your queries will be as follows:

 CREATE INDEX posts_fts_idx ON posts USING gin(to_tsvector('english', immutable_array_to_string(tags, ' '))); SELECT "posts".* FROM "posts" WHERE (to_tsvector('english', immutable_array_to_string(tags, ' ')) @@ (to_tsquery('english', 'ruby')));