Postgres tsearch2

De Stoq Wiki
Ir para: navegação, pesquisa

PostgreSQL full text indexing using tsearch2

Step-by-Step guide to enable tsearch2 on a PostgreSQL table:

Installing tsearch2

  • First, make sure that your database knows about the tsearch2 indexes:

Adding a full text index

  • Add the column to a table
ALTER TABLE table ADD COLUMN column_fti tsvector;
  • Populate the newly added message with a column
UPDATE table SET column_fti = to_tsvector('default', column);
  • Update indexes:
VACUUM FULL ANALYZE table;
  • Create the full text index:
CREATE INDEX table_column_fti ON table USING gist(column_fti);
  • Update the full text index:
VACUUM FULL ANALYZE table;

Querying the full text index

  • Querying
SELECT id, column FROM table WHERE table_column_fti @@ 'test'::tsquery;

Example

ALTER TABLE base_sellable_info ADD COLUMN description_fti tsvector;
VACUUM FULL ANALYZE base_sellable_info;
UPDATE base_sellable_info SET description_fti = to_tsvector('default', description);
VACUUM FULL ANALYZE base_sellable_info;
SELECT id, description FROM base_sellable_info WHERE description_fti @@ 'test'::tsquery;