Introduction to full-text search

Functions for Manipulating Data in PostgreSQL

Brian Piccolo

Sr. Director, Digital Strategy

Topics

Full Text search
Extending PostgreSQL
Improving full text search with extensions

The LIKE operator

_ wildcard: Used to match exactly one character.

% wildcard: Used to match zero or more characters.

SELECT title
FROM film
WHERE title LIKE 'ELF%';

+----------------------+
| title                |
+----------------------+
| ELF PARTY            |
+----------------------+

The LIKE operator

SELECT title
FROM film
WHERE title LIKE '%ELF';

+----------------------+
| title                |
+----------------------+
| ENCINO ELF           |
| GHOSTBUSTERS ELF     |
+----------------------+

The LIKE operator

SELECT title
FROM film
WHERE title LIKE '%elf%';

+----------------------+
| title                |
+----------------------+

LIKE versus full-text search

SELECT title, description
FROM film
WHERE to_tsvector(title) @@ to_tsquery('elf');

+----------------------+
| title                |
+----------------------+
| ELF PARTY            |
| ENCINO ELF           |
| GHOSTBUSTERS ELF     |
+----------------------+

What is full-text search?

Full text search provides a means for performing natural language queries of text data in your database.

Stemming
Spelling mistakes
Ranking

Full-text search syntax explained

SELECT title, description
FROM film
WHERE to_tsvector(title) @@ to_tsquery('elf');

Let's practice!

Functions for Manipulating Data in PostgreSQL