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%';

title
SQL join guide

Seq Scan on articles  (cost=0.00..20.62 rows=4 width=32)
  Filter: (title ~~ 'SQL%'::text)

ILIKE works like LIKE, but ignores letter case.

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

title
PostgreSQL indexing guide
JSON in Postgres

Seq Scan on articles  (cost=0.00..20.62 rows=1 width=32)
  Filter: (title ~~* '%postgres%'::text)

LOWER can make a regular LIKE comparison case-insensitive.

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

title
Vector search notes

Seq Scan on articles  (cost=0.00..22.75 rows=1 width=32)
  Filter: (lower(body) ~~ '%search%'::text)

Patterns need the exact characters. For word forms, see full-text search; for typos, fuzzy search; for meaning, vectors. Text search in Postgres compares them all.