← back to Stayclaim

db/migrations/005_listing_search.sql

17 lines

-- Postgres tsvector full-text search across the listing's editorial spine.
-- Generated column = no trigger, no maintenance overhead, always fresh.
ALTER TABLE listing
  ADD COLUMN IF NOT EXISTS search_doc tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title,'')),                    'A') ||
    setweight(to_tsvector('english', coalesce(headline,'')),                 'B') ||
    setweight(to_tsvector('english', coalesce(address_line1,'')),            'B') ||
    setweight(to_tsvector('english', coalesce(city,'')),                     'C') ||
    setweight(to_tsvector('english', coalesce(state,'')),                    'C') ||
    setweight(to_tsvector('english', coalesce(postal_code,'')),              'C') ||
    setweight(to_tsvector('english', coalesce(property_type,'')),            'C') ||
    setweight(to_tsvector('english', coalesce(description,'')),              'D')
  ) STORED;

CREATE INDEX IF NOT EXISTS idx_listing_search_doc ON listing USING GIN (search_doc);