Full-Text Search producing no results

Question

I have the following View:

CREATE VIEW public.profiles_search AS
    SELECT
        profiles.id,
        profiles.bio,
        profiles.title,
        (
            setweight(to_tsvector(profiles.search_language::regconfig, profiles.title::text), 'B'::"char") ||
            setweight(to_tsvector(profiles.search_language::regconfig, profiles.bio), 'A'::"char") ||
            setweight(to_tsvector(profiles.search_language::regconfig, profiles.category::text), 'B'::"char") ||
            setweight(to_tsvector(profiles.search_language::regconfig, array_to_string(profiles.tags, ',', '*')), 'C'::"char")
        ) AS document
    FROM profiles
    GROUP BY profiles.id;

However, if profiles.tags is empty then document is empty, even if the rest of the fields (title, bio, and category) contain data.

Is there some way to make the make that field optional such that it having empty data doesn't result in an an empty document?

Is profiles.tags null in those cases? If you use `coalesce(profiles.tags, ARRAY[]::text[])` instead of profiles.tags (assuming it's a text array), defaulting to an empty array instead of null, would it work? — approxiblue, Jul 19 '18 at 19:44

score 3 · Accepted Answer · answered Jul 20 '18 at 08:59

This seems to be the common string concatenation issue - concatenating a NULL value makes the whole result NULL.

Here it is suggested you should always provide a default value for any input with coalesce():

UPDATE tt SET ti =
    setweight(to_tsvector(coalesce(title,'')), 'A')    ||
    setweight(to_tsvector(coalesce(keyword,'')), 'B')  ||
    setweight(to_tsvector(coalesce(abstract,'')), 'C') ||
    setweight(to_tsvector(coalesce(body,'')), 'D');

If you do not want to provide default values for complex datatypes (like coalesce(profiles.tags, ARRAY[]::text[]) as suggested by @approxiblue), I suspect you could simply do:

CREATE VIEW public.profiles_search AS
    SELECT
        profiles.id,
        profiles.bio,
        profiles.title,
        (
            setweight(to_tsvector(profiles.search_language::regconfig, profiles.title::text), 'B'::"char") ||
            setweight(to_tsvector(profiles.search_language::regconfig, profiles.bio), 'A'::"char") ||
            setweight(to_tsvector(profiles.search_language::regconfig, profiles.category::text), 'B'::"char") ||
            setweight(to_tsvector(profiles.search_language::regconfig, coalesce(array_to_string(profiles.tags, ',', '*'), '')), 'C'::"char")
        ) AS document
    FROM profiles
    GROUP BY profiles.id;

Full-Text Search producing no results

1 Answers1