Postgres: How can I include weights in a generated column?

Question

I have following schema:

CREATE TABLE books (
  title VARCHAR(255),
  subtitle TEXT
);

Adding a generated column without weights is working fine:

ALTER TABLE books ADD COLUMN full_text_search TSVECTOR
  GENERATED ALWAYS AS (to_tsvector('english',
    coalesce(title, '') ||' '||
    coalesce(subtitle, '')
  )) STORED; -- ✅ Working

Now I want to add weights and it is not working:

ALTER TABLE books ADD COLUMN full_text_search_weighted TSVECTOR
  GENERATED ALWAYS AS (to_tsvector('english',
    setweight(coalesce(title, ''), 'A') ||' '||
    setweight(coalesce(subtitle, ''), 'B')
  )) STORED; -- ❌ Not working

Is there a way to include weights with a generated column in postgres?

Reproduction Link: https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/1385

score 4 · Accepted Answer · answered Mar 18 '21 at 12:27

After reading the docs I found out that setweight is returning tsvector. I had to modify it like this:

ALTER TABLE books ADD COLUMN full_text_search_weighted TSVECTOR
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||' '||
    setweight(to_tsvector('english', coalesce(subtitle, '')), 'B')
  ) STORED; -- ✅ Working

Now we can order the result with following query:

SELECT ts_rank(full_text_search_weighted , plainto_tsquery('english', 'book')), title, subtitle
  FROM "books"
  WHERE full_text_search_weighted @@ plainto_tsquery('english', 'book')
  ORDER BY ts_rank(full_text_search_weighted , plainto_tsquery('english', 'book')) DESC;

Reproduction link: https://www.db-fiddle.com/f/sffBR96NJtWej9c1Pcfg2H/0

Postgres: How can I include weights in a generated column?

1 Answers1