Specialized indexing and querying for efficient text searches.
PostgreSQL powerful text search engine using tsvector for document indexing and tsquery for search expressions. Supports stemming, ranking, and phrase search. Can replace simple LIKE queries with much faster indexed search.
Compatibility
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | Native | 3.23.23 | Engine-dependent |
| PostgreSQL | Native | 8.3 | tsvector/tsquery |
| SQL Server | Native | 2008 | Full-Text Service needed |
| Oracle | Native | 8i | Oracle Text |
| SQLite | Native | 3.9.0 | FTS3/4/5 modules |
Details
Syntax and capabilities vary widely by system.
Standard Syntax
-- varies by engine
SELECT *
FROM articles
WHERE MATCH(title, body) AGAINST('terms' IN BOOLEAN MODE);
Version Support
MySQL: Native since 3.23.23
PostgreSQL: Native since 8.3
SQL Server: Native since 2008
Oracle: Native since 8i
SQLite: Native since 3.9.0
Per-Database Syntax & Notes
MySQL Native syntax
Engine-dependent
SELECT *
FROM articles
WHERE MATCH(title,body) AGAINST('terms' IN BOOLEAN MODE);
PostgreSQL Native syntax
tsvector/tsquery
SELECT *
FROM articles
WHERE to_tsvector('english',title||' '||body)
@@ plainto_tsquery('terms');
SQL Server Native syntax
Full-Text Service needed
SELECT *
FROM articles
WHERE CONTAINS((title,body),'"terms"');
Oracle Native syntax
Oracle Text
SELECT *
FROM articles
WHERE CONTAINS(title||body,'terms')>0;
SQLite Native syntax
FTS3/4/5 modules
SELECT rowid, *
FROM articles_fts
WHERE articles_fts MATCH 'terms';