Create Full Text Search (FTS)

Create a new FTS column for fast searching of the Gazetteer

PL/pgSQL

ALTER TABLE gazetteer.gazetteer ADD COLUMN search_fts tsvector;
COMMIT;
 
UPDATE gazetteer.gazetteer SET search_fts = to_tsvector('english', def_nam);
COMMIT;
 
CREATE INDEX idx_gazetteer_search_fts ON gazetteer.gazetteer USING GIN(search_fts);
COMMIT;

--The following is an example query
SELECT * FROM gazetteer.gazetteer WHERE search_fts @@ to_tsquery('english', 'Hors:*');