Learning PostgreSQL

A PostgreSQL full text search

PostgreSQL provides a full text search capability, which is used to overcome SQL pattern matching operators, including LIKE and ILIKE, boosting the performance of the text search. For example, even though an index on text using the text_pattern_op class is supported, this index cannot be used to match a nonanchored text search. To explain this limitation, let's create the following table:

CREATE TABLE document(
  document_id serial primary key,
  document_body text
);

CREATE INDEX on document (document_body text_pattern_ops);

INSERT INTO document VALUES (default, 'Can not use text_pattern_op class to search for non-anchored text');

To test the index with anchored and nonanchored text search, let's disable sequential ...