PostgreSQL full text searching - Revision history

Johnno: syntax bug

2008-02-07T22:28:34Z

syntax bug

Johnno: Formatting

2008-02-07T22:25:28Z

Formatting

Johnno: 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...

2008-02-07T22:23:36Z

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...

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 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)
# Create a GIST index on the new column (another VACUUM FULL ANALYZE after this)
# 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);

@@ Line 6: / Line 6: @@
 ;Single Column Example
-:For step 2, use the COALESCE function to turn NULL values into empty strings.
+:For step 2, use the COALESCE function to turn NULL values into <nowiki>''</nowiki> strings.
   ALTER TABLE book
@@ Line 12: / Line 12: @@
   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
@@ Line 32: / Line 32: @@
   UPDATE book
       SET title_description_fti = TO_TSVECTOR('default',
-          COALESCE(title, '') || ' ' || COALESCE(description, ''));
+          <nowiki>COALESCE(title, '') || ' ' || COALESCE(description, ''));</nowiki>
   CREATE INDEX book_title_description_fti_idx

@@ 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.)
 # 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.
-'''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 empty strings.
   ALTER TABLE book
@@ Line 24: / Line 24: @@
       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