Postgres Full Text Search on Array Column

Question

I have a posts that has a column tags. I'd like to be able to do full text search across the tags. For VARCHAR columns I've 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 function to_tsvector does not exist. How can a query be written that will run against each of the tags (ideally matching if any single tag matches)?

Note: I see that it would be pretty easy to do a conversion to a string (array_to_string) but if possible I'd like to convert each individual tag to a tsvector.

TavoloPerUno · Accepted Answer · 2015-04-15 03:14:28Z

4

You could index the character varying using gin for search options. Try this :

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 desired. If an exact match is not desired, you should consider storing your tags as a text column. Think more on the significance of these 'tags'. String array types lack text indexing, stemming and inflection support, and hence you won't be able to match bates such as 'Dancing' with 'Dance'.

If that is not an option, you could circumvent this with an immutable version of array_to_string function. Your queries would then be :

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')));

edited Apr 15, 2015 at 3:14

answered Apr 15, 2015 at 2:07

TavoloPerUno

5792 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Kevin Sylvestre Over a year ago

Thanks for the response, but that won't use a full text search. That requires an exact match and won't account for spelling and english similarities (i.e. dancing vs dance).

Kevin Sylvestre Over a year ago

Yeah, I kind of called that out in my question - that I looking to try and apply the tsvector on each element of the array - not join the array and then apply. The issue being that this will match strings between tags.

Jonathan Allard Over a year ago

Where is this immutable_array_to_string function from?

Carlos Martinez Over a year ago

I can't find any documentation for immutable_array_to_string

HelloWorld Over a year ago

would look something like this:

CREATE OR REPLACE FUNCTION immutable_array_to_string(text[]) RETURNS text as $$ SELECT array_to_string($1, ','); $$ LANGUAGE sql IMMUTABLE;

|

Collectives™ on Stack Overflow

Postgres Full Text Search on Array Column

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related