PostgreSQL Full Text Search

PostgreSQL has a decent Full Text Search capability. And it is quite simple to implement too; just a few SQL statements and you are done.

You want to implement your search function using Full Text Search because this method is comparatively faster than pattern matching with '%'. The 'tsvector' and 'gin' processes indexes your values and makes it easier for your DBMS to retrieve them.

To the SQL code (assuming you have a system schema with a clients table):

 1 - Add a search field to the table:

ALTER TABLE system.clients ADD COLUMN search TSVECTOR;

 2 - Convert the existing 'name' and 'email' fields into a TSVECTOR stored in the new search field:

UPDATE system.clients SET search = to_tsvector(name || email);

 3 - Index the search field:

CREATE INDEX search_client_index ON system.clients USING gin(search);

 4 - Create a trigger so that whenever the fields are updated then the search field is updated too:

CREATE TRIGGER update_ticket_search BEFORE INSERT OR UPDATE ON system.clients FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(search,"pg_catalog.english",title,email);

 5 - To search for entries you run the following code:

SELECT * FROM system.tickets WHERE search @@ to_tsquery('scrubber');

Resources:

•  Linux Gazette article on full text search features in PostgreSQL