4. Full-text Search and PostgreSQL Extension

Introduction to full-text search

Extend the capabilities

• full text search
• extending postgresql
• im[roving full text search

The LIKE operator

_wildcard: used to match exactly one character.

$ wildcard: Used to match zero or more characters

A Review of the LIKE operator

SELECT *
FROM film
WHERE title LIKE 'GOLD%';

SELECT *
FROM film
-- Select only records that end with the word 'GOLD'
WHERE title LIKE '%GOLD';

SELECT *
FROM film
-- Select only records that contain the word 'GOLD'
WHERE title LIKE '%GOLD%';

tsvector

convert strings to tsvector and tsquery in the video and in this exercise.

-- Select the film description as a tsvector
SELECT to_tsvector(description)
FROM film;

Extending PostgreSQL

-- Select the title and description
SELECT title, description
FROM film
-- Convert the title to a tsvector and match it against the tsquery 
WHERE to_tsvector(title) @@ to_tsquery('elf');

Extending PostgreSQL

User-defined data types