{"id":1211,"date":"2024-12-23T15:41:47","date_gmt":"2024-12-23T08:41:47","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=1211"},"modified":"2025-01-03T11:32:39","modified_gmt":"2025-01-03T04:32:39","slug":"postgresql-full-text-search","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-full-text-search\/","title":{"rendered":"PostgreSQL Full-text Search"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll explore the PostgreSQL full-text search feature and how to implement it in your databases.<\/p>\n\n\n\n

Introduction to PostgreSQL full-text search #<\/a><\/h2>\n\n\n\n

So far, you’ve learned how to find the text exactly using the WHERE<\/a><\/code> clause with the equal operator (=<\/code>):<\/p>\n\n\n

SELECT<\/span>\n  column1,\n  column2\nFROM<\/span>\n  table_name<\/span>\nWHERE<\/span>\n  column_name<\/span> = 'search_term'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span>\n  column1,\n  column2\nFROM<\/span>\n  table_name<\/span>\nWHERE<\/span>\n  column_name<\/span> LIKE<\/span> pattern;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span>\n  column1,\n  column2\nFROM<\/span>\n  table_name<\/span>\nWHERE<\/span>\n  column1 SIMILAR<\/span> TO<\/span> pattern;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
You can use the full-text search if these options do not meet your requirements.<\/p>\n\n\n\n
In PostgreSQL, full-text search is a powerful feature that allows you to carry complex searches on text. It efficiently searches and ranks text-based data by indexing their words and phrases.<\/p>\n\n\n\n
For example, if you find documents<\/em> that contain the word “advance,” the full-text search will return the documents with the words \"advance\"<\/code> , \"advancing\"<\/code> , and \"advanced\"<\/code>.<\/p>\n\n\n\n
A document is the basic unit of text you want to search through in a full-text search. It can be a column of a table or a combination of columns from multiple tables.<\/p>\n\n\n\n
PostgreSQL full-text search data types #<\/a><\/h2>\n\n\n\n
PostgreSQL provides two specific data types to support full-text searches: tsvector<\/code>and tsquery<\/code>.<\/p>\n\n\n\n
tsvector data type #<\/a><\/h3>\n\n\n\n
The tsvector<\/code> is a data type that supports full-text search. It represents a document optimized for text search by storing a sorted list of distinct normalized words.<\/p>\n\n\n\n
The technical terms of the normalized words are lexemes<\/em>. Lexemes are words without variations, such as teaches<\/code> and teaching<\/code> words have the lexeme teach<\/code>.<\/p>\n\n\n\n
To convert a regular string to a tsvector, you use the to_tsvector function. For example:<\/p>\n\n\n
SELECT<\/span>\n  to_tsvector('teaches'<\/span>),\n  to_tsvector('teaching'<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 to_tsvector | to_tsvector\n-------------+-------------<\/span>\n 'teach'<\/span>:1<\/span>   | 'teach'<\/span>:1<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the to_tsvector<\/code> function converts the words teaches and teaching to the word teach.<\/p>\n\n\n\n
The tsvector<\/code> data type has the following features:<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
If the exact match and pattern matching are not sufficient, you can go with regular expressions<\/a> or using the SIMILAR TO<\/a><\/code> operator:<\/p>\n\n\n
And match the text based on a specified pattern using the LIKE<\/a><\/code> operator:<\/p>\n\n\n