FULL TEXT SEARCHIndexes & Search
Buy Me A Coffee

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

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 3.23.23 Engine-dependent
PostgreSQL ✓ Supported 8.3 tsvector/tsquery
SQL Server ✓ Supported 2008 Full-Text Service needed
Oracle ✓ Supported 8i Oracle Text
SQLite ✓ Supported 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: Since 3.23.23 PostgreSQL: Since 8.3 SQL Server: Since 2008 Oracle: Since 8i SQLite: Since 3.9.0

Per-Database Syntax & Notes

MySQL

Engine-dependent

SELECT * FROM articles WHERE MATCH(title,body) AGAINST('terms' IN BOOLEAN MODE);

PostgreSQL

tsvector/tsquery

SELECT * FROM articles WHERE to_tsvector('english',title||' '||body) @@ plainto_tsquery('terms');

SQL Server

Full-Text Service needed

SELECT * FROM articles WHERE CONTAINS((title,body),'"terms"');

Oracle

Oracle Text

SELECT * FROM articles WHERE CONTAINS(title||body,'terms')>0;

SQLite

FTS3/4/5 modules

SELECT rowid, * FROM articles_fts WHERE articles_fts MATCH 'terms';