db/schema_0073-add-full-text-search-index.sql


1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE INDEX CONCURRENTLY problem_fulltext_idx ON problem USING GIN(
    to_tsvector(
        'DB_FULL_TEXT_SEARCH_CONFIG',
        translate(id || ' ' || coalesce(external_id,'') || ' ' || coalesce(bodies_str,'') || ' ' || name || ' ' || title || ' ' || detail, '/.', '  ')
    )
);

CREATE INDEX CONCURRENTLY comment_fulltext_idx on comment USING GIN(
    to_tsvector(
        'DB_FULL_TEXT_SEARCH_CONFIG',
        translate(id || ' ' || problem_id || ' ' || coalesce(name,'') || ' ' || text, '/.', '  ')
    )
);