Difference between revisions of "PostgreSQL full text searching"

From Jon's Wiki
(New page: '''Steps:''' # Add a column to the table that will hold the text search data (of type TSVECTOR). You need one column for each different type of search you want to do (e.g. on several diffe...)
 
m (syntax bug)
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
'''Steps:'''
+
;Steps
 
# Add a column to the table that will hold the text search data (of type TSVECTOR). You need one column for each different type of search you want to do (e.g. on several different single columns, across multiple columns, etc.)
 
# Add a column to the table that will hold the text search data (of type TSVECTOR). You need one column for each different type of search you want to do (e.g. on several different single columns, across multiple columns, etc.)
 
# Populate the column with the search data (you will probably want to VACUUM FULL ANALYZE after this step on a large table)
 
# Populate the column with the search data (you will probably want to VACUUM FULL ANALYZE after this step on a large table)
Line 5: Line 5:
 
# Create an update trigger on the new column to update the search data on UPDATE or INSERT.
 
# Create an update trigger on the new column to update the search data on UPDATE or INSERT.
  
'''Single Column Example:'''
+
;Single Column Example
For step 2, use the COALESCE function to turn NULL values into '' strings.
+
:For step 2, use the COALESCE function to turn NULL values into <nowiki>''</nowiki> strings.
  
 
  ALTER TABLE book
 
  ALTER TABLE book
Line 12: Line 12:
 
   
 
   
 
  UPDATE book
 
  UPDATE book
     SET title_fti = TO_TSVECTOR('default', COALESCE(title, ''));
+
     <nowiki>SET title_fti = TO_TSVECTOR('default', COALESCE(title, ''));</nowiki>
 
   
 
   
 
  CREATE INDEX book_title_fti_idx
 
  CREATE INDEX book_title_fti_idx
Line 24: Line 24:
 
     TSEARCH2(title_fti, title);
 
     TSEARCH2(title_fti, title);
  
'''Multiple Column Example:'''
+
;Multiple Column Example
For a multi-column text search, you simply concatenate the columns together for step 2.
+
:For a multi-column text search, you simply concatenate the columns together for step 2.
  
 
  ALTER TABLE book
 
  ALTER TABLE book
Line 32: Line 32:
 
  UPDATE book
 
  UPDATE book
 
     SET title_description_fti = TO_TSVECTOR('default',
 
     SET title_description_fti = TO_TSVECTOR('default',
         COALESCE(title, '') || ' ' || COALESCE(description, ''));
+
         <nowiki>COALESCE(title, '') || ' ' || COALESCE(description, ''));</nowiki>
 
   
 
   
 
  CREATE INDEX book_title_description_fti_idx
 
  CREATE INDEX book_title_description_fti_idx

Latest revision as of 22:28, 7 February 2008

Steps
  1. Add a column to the table that will hold the text search data (of type TSVECTOR). You need one column for each different type of search you want to do (e.g. on several different single columns, across multiple columns, etc.)
  2. Populate the column with the search data (you will probably want to VACUUM FULL ANALYZE after this step on a large table)
  3. Create a GIST index on the new column (another VACUUM FULL ANALYZE after this)
  4. Create an update trigger on the new column to update the search data on UPDATE or INSERT.
Single Column Example
For step 2, use the COALESCE function to turn NULL values into '' strings.
ALTER TABLE book
    ADD COLUMN title_fti TSVECTOR;

UPDATE book
    SET title_fti = TO_TSVECTOR('default', COALESCE(title, ''));

CREATE INDEX book_title_fti_idx
    ON book
    USING GIST(title_fti);

CREATE TRIGGER book_title_fti_update
    BEFORE INSERT OR UPDATE
    ON book
    FOR EACH ROW EXECUTE PROCEDURE
    TSEARCH2(title_fti, title);
Multiple Column Example
For a multi-column text search, you simply concatenate the columns together for step 2.
ALTER TABLE book
    ADD COLUMN title_description_fti TSVECTOR;

UPDATE book
    SET title_description_fti = TO_TSVECTOR('default',
        COALESCE(title, '') || ' ' || COALESCE(description, ''));

CREATE INDEX book_title_description_fti_idx
    ON book
    USING GIST(title_description_fti);

CREATE TRIGGER book_title_description_fti_update
    BEFORE INSERT OR UPDATE
    ON book
    FOR EACH ROW EXECUTE PROCEDURE
    TSEARCH2(title_description_fti, title, description);