Add a weights to the fields in PostgreSQL full-text search

readme.md

You can give different weights to the fields in PostgreSQL full-text search by using setweight() inside your to_tsvector.

SELECT
    id,
    title,
    area,
    tags,
    content,
    url,
    ts_rank(
        setweight(to_tsvector('portuguese', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('portuguese', coalesce(area, '')), 'B') ||
        setweight(to_tsvector('portuguese', coalesce(content, '')), 'C') ||
        setweight(to_tsvector('portuguese', coalesce(tags, '')), 'D'),
        query
    ) AS relevance_score
FROM
    contextual_help_articles,
    to_tsquery('portuguese', 'Contabilidade | Fiscal | Apoio | Importação | ECD') AS query
WHERE
    (
        setweight(to_tsvector('portuguese', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('portuguese', coalesce(area, '')), 'B') ||
        setweight(to_tsvector('portuguese', coalesce(content, '')), 'C') ||
        setweight(to_tsvector('portuguese', coalesce(tags, '')), 'D')
    ) @@ query
ORDER BY
    relevance_score DESC
LIMIT 40;

Explanation:

setweight(tsvector, 'A'|'B'|'C'|'D') assigns different weights.

• 'A' is the highest (title, for example).
• 'B' is lower (area).
• 'C', 'D' are progressively lower (content, tags).

Then ts_rank takes the weighted vector and adjusts the ranking accordingly.

👉 In your case, you probably want something like:

• Title → 'A'
• Area → 'B'
• Content → 'C'
• Tags → 'D'

That way, matches in title weigh more than in content.