Text search basics

Draft

Text search starts with patterns. LIKE and ILIKE are simple tools for prefix, suffix, and contains-style matching.

Prefix search

LIKE uses % as a wildcard. This pattern matches titles starting with SQL.

SELECT title
FROM articles
WHERE title LIKE 'SQL%';

Loading plan...

ILIKE works like LIKE, but ignores letter case.

SELECT title
FROM articles
WHERE title ILIKE '%postgres%';

Loading plan...

LOWER can make a regular LIKE comparison case-insensitive.

SELECT title
FROM articles
WHERE LOWER(body) LIKE '%search%';

Loading plan...