O'Reilly logo

Learning PostgreSQL by Andrey Volkov, Achim Vannahme, Salahaldin Juba

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required