Newbie to full text search, I have a feed_items table as follows
feed_items (...title varchar, summary varchar, fullarticle varchar,...)
I want to create a ts_vector column to search query from either the title or the summary with higher priority given if the query is found in the title.
Option 1 I can create a generated column which goes like this
ALTER TABLE feed_items ADD COLUMN textsearchable_index_col tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(summary, ''))) STORED;
Option 2 I can create a trigger after insert or update like this, the example below is not perfect, I created a separate column for title_vector and summary_vector but I think I need to merge them into one column and yet decide how to give more importance to query found in title
ALTER TABLE "feed_items" ADD IF NOT EXISTS "title_vector" tsvector; COMMENT ON COLUMN "feed_items"."title_vector" IS $pga$vector representation of title$pga$; UPDATE feed_items SET title_vector = to_tsvector('english', title); CREATE INDEX "idx_title_vector" ON "feed_items" USING gin ("title_vector"); CREATE TRIGGER "trigger_update_title_vector" BEFORE INSERT OR UPDATE ON "feed_items" FOR EACH ROW EXECUTE PROCEDURE "tsvector_update_trigger"($pga$title_vector$pga$, $pga$pg_catalog.english$pga$, $pga$title$pga$);
My main question is what is the difference between both? and which one should I use?