This post is aimed at providing only the information necessary to rapidly understand and deploy Full Text Search into your PostgreSQL environment.
1) Suppose we have a table named "fts" with the following schema :
id <serial>
body <text>
body_tsvector <tsvector>
CREATE TABLE fts (
id serial NOT NULL,
body text,
body_tsvector tsvector
)
2) Now let's suppose the following data is present within the table:
INSERT INTO fts (body) VALUES
('An artist cannot speak about his art any more than a plant can discuss horticulture.'),
('I like work; it fascinates me. I can sit and look at it for hours.'),
('Love is a great beautifier.');
3) Next, let's populate the body_tsvector column:
UPDATE fts SET body_tsvector = to_tsvector('english', body);
4) For speed's sake let's create an index on our body_tsvector column:
CREATE INDEX fts_body_tsvector_gin_idx ON fts USING GIN(body_tsvector);
5) Finally, let's create a trigger which automatically updates or populates our body_tsvector column whenever a row is updated or inserted:
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON fts FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('body_tsvector', 'pg_catalog.english', 'body');
Now that we have a table which has been populated with data, has full text indexing set up, and will automatically update accordingly whenever a future update or insert occurs, let's run some tests to ensure full text search works as expected.
SELECT * FROM fts WHERE body_tsvector @@ plainto_tsquery('english', 'Discuss')
Should return: ID 1
SELECT * FROM fts WHERE body_tsvector @@ plainto_tsquery('english', 'Discuss LOVE')
Should return: IDs 1 and 3
SELECT * FROM fts WHERE body_tsvector @@ plainto_tsquery('english', 'Discuss') AND NOT (body_tsvector @@ plainto_tsquery('english', 'Love'))
Should return: ID 1
For more documentation, I'd recommend checking out the following URLs:
No comments:
Post a Comment