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.

Septuple

Answer 1

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

Driftwood

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). – Septuple

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. – Septuple

For that to work, you have to move tags to a separate table. I do not see any other possibility. – Driftwood

Where is this immutable_array_to_string function from? – Jehias

I can't find any documentation for immutable_array_to_string – Evanevander

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; – Blockbusting

Like others are saying, immutable_array_to_string doesn't seem to be a thing. If immutable_array_to_string is something you need to create can OP provide info on how they did this? – Orms

Create a simple IMMUTABLE SQL function that wraps array_to_string function. As in @HelloWorld's reply: CREATE OR REPLACE FUNCTION immutable_array_to_string(text[]) RETURNS text as $$ SELECT array_to_string($1, ','); $$ LANGUAGE sql IMMUTABLE; – Driftwood

Using a created immutable_array_to_string doesn't seem to work. Running the query with EXPLAIN ANALYZE, postgres doesn't use the index. – Deshabille