{"id":2004,"date":"2024-06-28T09:36:35","date_gmt":"2024-06-28T09:36:35","guid":{"rendered":"https:\/\/www.w3computing.com\/articles\/?p=2004"},"modified":"2024-06-28T09:36:40","modified_gmt":"2024-06-28T09:36:40","slug":"implementing-full-text-search-with-postgresqls-tsvector-and-tsquery","status":"publish","type":"post","link":"https:\/\/www.w3computing.com\/articles\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\/","title":{"rendered":"Implementing Full-Text Search with PostgreSQL’s tsvector and tsquery"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

Full-text search is a powerful feature in modern databases that allows users to search through large amounts of text quickly and efficiently. PostgreSQL, one of the most advanced open-source databases, provides robust support for full-text search through its tsvector<\/code> and tsquery<\/code> data types. These tools enable efficient text search capabilities by indexing and querying text data in a way that is both flexible and performant. This tutorial will guide you through the process of implementing full-text search in PostgreSQL using tsvector<\/code> and tsquery<\/code>.<\/p>\n\n\n\n

By the end of this tutorial, you will have a comprehensive understanding of how to leverage PostgreSQL’s full-text search capabilities to enhance your applications.<\/p>\n\n\n\n

1. Understanding Full-Text Search Concepts<\/h2>\n\n\n\n

Before diving into the implementation, it’s crucial to understand some key concepts related to full-text search in PostgreSQL.<\/p>\n\n\n\n

1.1 tsvector<\/code><\/h3>\n\n\n\n

A tsvector<\/code> (text search vector) is a data type in PostgreSQL that stores preprocessed text data for efficient searching. It converts the text into a searchable format by breaking it down into tokens (words) and normalizing them (e.g., removing punctuation and converting to lowercase). Each token is then mapped to its positions in the original text.<\/p>\n\n\n\n

1.2 tsquery<\/code><\/h3>\n\n\n\n

A tsquery<\/code> (text search query) is a data type used to represent search queries. It consists of tokens and operators (AND, OR, NOT) that define the search criteria. tsquery<\/code> is used to match against tsvector<\/code> to find relevant text entries.<\/p>\n\n\n\n

1.3 Text Search Configuration<\/h3>\n\n\n\n

PostgreSQL uses text search configurations to control how text is tokenized and normalized. Configurations include dictionaries and tokenizers that define the rules for processing text. PostgreSQL provides several built-in configurations for different languages.<\/p>\n\n\n\n

2. Setting Up PostgreSQL for Full-Text Search<\/h2>\n\n\n\n

Before you can start using full-text search, you need to set up PostgreSQL. Ensure you have PostgreSQL installed on your system. You can download and install PostgreSQL from the official website<\/a>.<\/p>\n\n\n\n

Once installed, you can create a new database and connect to it using the PostgreSQL command-line tool psql<\/code> or any other PostgreSQL client.<\/p>\n\n\n

# Create a new database<\/span>\ncreatedb full_text_search_db\n\n# Connect to the database<\/span>\npsql full_text_search_db<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
3. Creating and Populating Tables<\/h2>\n\n\n\n
Let’s create a sample table to work with. For this tutorial, we’ll create a table called documents<\/code> to store text data.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> documents (\n    id<\/span> SERIAL<\/span> PRIMARY KEY<\/span>,\n    title TEXT<\/span> NOT<\/span> NULL<\/span>,\n    content<\/span> TEXT<\/span> NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Next, let’s populate the table with some sample data.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> documents (title, content<\/span>) VALUES<\/span>\n('Introduction to PostgreSQL'<\/span>, 'PostgreSQL is a powerful, open source object-relational database system.'<\/span>),\n('Full-Text Search in PostgreSQL'<\/span>, 'Learn how to implement full-text search using PostgreSQL tsvector and tsquery.'<\/span>),\n('Advanced PostgreSQL Features'<\/span>, 'Explore advanced features in PostgreSQL including indexing, partitioning, and more.'<\/span>),\n('PostgreSQL Performance Tuning'<\/span>, 'Tips and techniques for optimizing PostgreSQL performance.'<\/span>),\n('Scaling PostgreSQL'<\/span>, 'Strategies for scaling PostgreSQL databases to handle large amounts of data.'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
4. Generating tsvector<\/code> Columns<\/h2>\n\n\n\n
To enable full-text search, we need to generate tsvector<\/code> columns that store the tokenized and normalized text data. We’ll add a tsvector<\/code> column to the documents<\/code> table.<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> documents ADD<\/span> COLUMN<\/span> tsv_content tsvector;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Next, we’ll populate the tsvector<\/code> column with data from the title<\/code> and content<\/code> columns.<\/p>\n\n\n
UPDATE<\/span> documents SET<\/span> tsv_content = to_tsvector('english'<\/span>, title || ' '<\/span> || content<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here, we’re using the to_tsvector<\/code> function to convert the combined title<\/code> and content<\/code> into a tsvector<\/code> using the ‘english’ text search configuration.<\/p>\n\n\n\n
5. Creating Indexes on tsvector<\/code> Columns<\/h2>\n\n\n\n
To make full-text search queries fast, we need to create an index on the tsvector<\/code> column. PostgreSQL provides the GIN<\/code> (Generalized Inverted Index) index type, which is well-suited for full-text search.<\/p>\n\n\n
CREATE<\/span> INDEX<\/span> idx_tsv_content ON<\/span> documents USING<\/span> GIN (tsv_content);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This index will allow PostgreSQL to quickly search through the tokenized text data.<\/p>\n\n\n\n
6. Querying with tsquery<\/code><\/h2>\n\n\n\n
With the tsvector<\/code> column and index in place, we can start querying the data using tsquery<\/code>. Let’s perform some basic full-text search queries.<\/p>\n\n\n\n
6.1 Simple Search<\/h3>\n\n\n
SELECT<\/span> title, content<\/span>\nFROM<\/span> documents\nWHERE<\/span> tsv_content @@ to_tsquery('english'<\/span>, 'PostgreSQL'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This query searches for documents that contain the term “PostgreSQL”. The @@<\/code> operator is used to match the tsvector<\/code> against the tsquery<\/code>.<\/p>\n\n\n\n
6.2 Phrase Search<\/h3>\n\n\n
SELECT<\/span> title, content<\/span>\nFROM<\/span> documents\nWHERE<\/span> tsv_content @@ to_tsquery('english'<\/span>, 'full & text & search'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This query searches for documents that contain the phrase “full text search”. The &<\/code> operator specifies an AND condition between terms.<\/p>\n\n\n\n
6.3 Negation<\/h3>\n\n\n
SELECT<\/span> title, content<\/span>\nFROM<\/span> documents\nWHERE<\/span> tsv_content @@ to_tsquery('english'<\/span>, 'PostgreSQL & !performance'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This query searches for documents that contain “PostgreSQL” but not “performance”. The !<\/code> operator specifies a NOT condition.<\/p>\n\n\n\n
7. Advanced Search Techniques<\/h2>\n\n\n\n
PostgreSQL’s full-text search capabilities go beyond simple term matching. Let’s explore some advanced search techniques.<\/p>\n\n\n\n
7.1 Weighting and Ranking<\/h3>\n\n\n\n
You can assign different weights to different parts of the document and rank the search results based on relevance.<\/p>\n\n\n
UPDATE<\/span> documents\nSET<\/span> tsv_content = setweight(to_tsvector('english'<\/span>, title), 'A'<\/span>) ||\n                  setweight(to_tsvector('english'<\/span>, content<\/span>), 'B'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here, we assign a higher weight (‘A’) to the title<\/code> and a lower weight (‘B’) to the content<\/code>.<\/p>\n\n\n\n
To rank the results, use the ts_rank<\/code> function.<\/p>\n\n\n
SELECT<\/span> title, content<\/span>, ts_rank(tsv_content, to_tsquery('english'<\/span>, 'PostgreSQL'<\/span>)) AS<\/span> rank<\/span>\nFROM<\/span> documents\nWHERE<\/span> tsv_content @@ to_tsquery('english'<\/span>, 'PostgreSQL'<\/span>)\nORDER<\/span> BY<\/span> rank<\/span> DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
7.2 Highlighting Search Terms<\/h3>\n\n\n\n
Highlighting search terms in the results can improve user experience. PostgreSQL provides the ts_headline<\/code> function for this purpose.<\/p>\n\n\n
SELECT<\/span> title, ts_headline('english'<\/span>, content<\/span>, to_tsquery('english'<\/span>, 'PostgreSQL'<\/span>)) AS<\/span> highlighted_content\nFROM<\/span> documents\nWHERE<\/span> tsv_content @@ to_tsquery('english'<\/span>, 'PostgreSQL'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This query returns the content with search terms highlighted.<\/p>\n\n\n\n
7.3 Synonyms and Dictionaries<\/h3>\n\n\n\n
You can enhance full-text search by using synonyms and custom dictionaries. This allows you to account for variations in search terms.<\/p>\n\n\n\n
First, create a synonym dictionary.<\/p>\n\n\n
CREATE<\/span> TEXT<\/span> SEARCH<\/span> DICTIONARY synonym_dict (\n    TEMPLATE<\/span> = synonym<\/span>,\n    SYNONYMS = 'path\/to\/synonym\/file'<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Next, create a text search configuration that uses the synonym dictionary.<\/p>\n\n\n
CREATE<\/span> TEXT<\/span> SEARCH<\/span> CONFIGURATION custom_english (COPY = english);\nALTER<\/span> TEXT<\/span> SEARCH<\/span> CONFIGURATION custom_english\n    ALTER<\/span> MAPPING<\/span> FOR<\/span> asciiword WITH<\/span> synonym_dict, english_stem;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, use the custom configuration in your queries.<\/p>\n\n\n
UPDATE<\/span> documents SET<\/span> tsv_content = to_tsvector('custom_english'<\/span>, title || ' '<\/span> || content<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
8. Performance Considerations<\/h2>\n\n\n\n
Full-text search can be resource-intensive, especially with large datasets. Here are some performance optimization tips.<\/p>\n\n\n\n
8.1 Index Maintenance<\/h3>\n\n\n\n
Regularly maintain your indexes to keep them efficient.<\/p>\n\n\n
REINDEX INDEX idx_tsv_content;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
8.2 Partitioning<\/h3>\n\n\n\n
For very large datasets, consider partitioning your tables to improve query performance.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> documents_2023 PARTITION<\/span> OF<\/span> documents FOR<\/span> VALUES<\/span> FROM<\/span> ('2023-01-01'<\/span>) TO<\/span> ('2024-01-01'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
8.3 Configuration Tuning<\/h3>\n\n\n\n
Tune PostgreSQL configurations to optimize full-text search performance. Key parameters include work_mem<\/code>, maintenance_work_mem<\/code>, and shared_buffers<\/code>.<\/p>\n\n\n
SET<\/span> work_mem = '64MB'<\/span>;\nSET<\/span> maintenance_work_mem = '256MB'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
9. Real-World Examples<\/h2>\n\n\n\n
Let’s look at a couple of real-world examples to see how full-text search can be applied.<\/p>\n\n\n\n
9.1 Blog Search<\/h3>\n\n\n\n
Imagine you have a blog with thousands of articles. You want to implement a search feature that allows users to find articles by keywords.<\/p>\n\n\n\n
First, create the articles table.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> articles (\n    id<\/span> SERIAL<\/span> PRIMARY KEY<\/span>,\n    title TEXT<\/span> NOT<\/span> NULL<\/span>,\n    body<\/span> TEXT<\/span> NOT<\/span> NULL<\/span>,\n    tsv_body tsvector\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Next, populate the tsvector<\/code> column.<\/p>\n\n\n
UPDATE<\/span> articles SET<\/span> tsv_body = to_tsvector('english'<\/span>, title || ' '<\/span> || body<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Create an index on the tsvector<\/code> column.<\/p>\n\n\n
CREATE INDEX idx_tsv_body ON articles USING GIN (tsv_body);<\/code><\/span><\/pre>\n\n\n
Finally, implement the search functionality.<\/p>\n\n\n
SELECT<\/span> title, ts_headline('english'<\/span>, body<\/span>, to_tsquery('english'<\/span>, 'search & feature'<\/span>)) AS<\/span> highlighted_body\nFROM<\/span> articles\nWHERE<\/span> tsv_body @@ to_tsquery('english'<\/span>, 'search & feature'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
9.2 E-commerce Product Search<\/h3>\n\n\n\n
For an e-commerce platform, you want to implement a search feature that allows users to find products by name and description.<\/p>\n\n\n\n
First, create the products table.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> products (\n    id<\/span> SERIAL<\/span> PRIMARY KEY<\/span>,\n    name<\/span> TEXT<\/span> NOT<\/span> NULL<\/span>,\n    description TEXT<\/span> NOT<\/span> NULL<\/span>,\n    tsv_description tsvector\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Next, populate the tsvector<\/code> column.<\/p>\n\n\n
UPDATE<\/span> products SET<\/span> tsv_description = to_tsvector('english'<\/span>, name<\/span> || ' '<\/span> || description);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Create an index on the tsvector<\/code> column.<\/p>\n\n\n
CREATE<\/span> INDEX<\/span> idx_tsv_description ON<\/span> products USING<\/span> GIN (tsv_description);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, implement the search functionality.<\/p>\n\n\n
SELECT<\/span> name<\/span>, ts_headline('english'<\/span>, description, to_tsquery('english'<\/span>, 'search & product'<\/span>)) AS<\/span> highlighted_description\nFROM<\/span> products\nWHERE<\/span> tsv_description @@ to_tsquery('english'<\/span>, 'search & product'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
10. Conclusion<\/h2>\n\n\n\n
Implementing full-text search with PostgreSQL’s tsvector<\/code> and tsquery<\/code> provides a powerful and efficient way to search through large text data. By understanding the concepts, setting up the database, creating indexes, and utilizing advanced search techniques, you can enhance your applications with robust search capabilities.<\/p>\n\n\n\n
Remember to optimize your configurations and maintain your indexes to ensure optimal performance. With practice and experimentation, you’ll be able to leverage PostgreSQL’s full-text search to its fullest potential.<\/p>\n","protected":false},"excerpt":{"rendered":"
Introduction Full-text search is a powerful feature in modern databases that allows users to search through large amounts of text quickly and efficiently. PostgreSQL, one of the most advanced open-source databases, provides robust support for full-text search through its tsvector and tsquery data types. These tools enable efficient text search capabilities by indexing and querying […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_genesis_hide_title":false,"_genesis_hide_breadcrumbs":false,"_genesis_hide_singular_image":false,"_genesis_hide_footer_widgets":false,"_genesis_custom_body_class":"","_genesis_custom_post_class":"","_genesis_layout":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[14,22],"tags":[],"class_list":["post-2004","post","type-post","status-publish","format-standard","category-database-development","category-postgresql","entry"],"yoast_head":"\nImplementing Full-Text Search with PostgreSQL's tsvector, tsquery<\/title>\n<meta name=\"description\" content=\"A tsvector (text search vector) is a data type in PostgreSQL that stores preprocessed text data for efficient searching.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.w3computing.com\/articles\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Implementing Full-Text Search with PostgreSQL's tsvector, tsquery\" \/>\n<meta property=\"og:description\" content=\"A tsvector (text search vector) is a data type in PostgreSQL that stores preprocessed text data for efficient searching.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.w3computing.com\/articles\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-06-28T09:36:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-06-28T09:36:40+00:00\" \/>\n<meta name=\"author\" content=\"w3compadmin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"w3compadmin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"TechArticle\",\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\\\/\"},\"author\":{\"name\":\"w3compadmin\",\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/#\\\/schema\\\/person\\\/a550b3e20d78bb4f79b7c6b7b53f0561\"},\"headline\":\"Implementing Full-Text Search with PostgreSQL’s tsvector and tsquery\",\"datePublished\":\"2024-06-28T09:36:35+00:00\",\"dateModified\":\"2024-06-28T09:36:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\\\/\"},\"wordCount\":930,\"articleSection\":[\"Database Development\",\"PostgreSQL\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\\\/\",\"url\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\\\/\",\"name\":\"Implementing Full-Text Search with PostgreSQL's tsvector, tsquery\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/#website\"},\"datePublished\":\"2024-06-28T09:36:35+00:00\",\"dateModified\":\"2024-06-28T09:36:40+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/#\\\/schema\\\/person\\\/a550b3e20d78bb4f79b7c6b7b53f0561\"},\"description\":\"A tsvector (text search vector) is a data type in PostgreSQL that stores preprocessed text data for efficient searching.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Articles Home\",\"item\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Database Development\",\"item\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/database-development\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Implementing Full-Text Search with PostgreSQL’s tsvector and tsquery\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/#website\",\"url\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/\",\"name\":\"Developer Articles Hub\",\"description\":\"\",\"alternateName\":\"Developer Articles\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/#\\\/schema\\\/person\\\/a550b3e20d78bb4f79b7c6b7b53f0561\",\"name\":\"w3compadmin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/wp-content\\\/litespeed\\\/avatar\\\/bd481d404e42caa2763662a3bfe825f8.jpg?ver=1783167470\",\"url\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/wp-content\\\/litespeed\\\/avatar\\\/bd481d404e42caa2763662a3bfe825f8.jpg?ver=1783167470\",\"contentUrl\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/wp-content\\\/litespeed\\\/avatar\\\/bd481d404e42caa2763662a3bfe825f8.jpg?ver=1783167470\",\"caption\":\"w3compadmin\"},\"sameAs\":[\"http:\\\/\\\/w3computing.com\\\/articles\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Implementing Full-Text Search with PostgreSQL's tsvector, tsquery","description":"A tsvector (text search vector) is a data type in PostgreSQL that stores preprocessed text data for efficient searching.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.w3computing.com\/articles\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\/","og_locale":"en_US","og_type":"article","og_title":"Implementing Full-Text Search with PostgreSQL's tsvector, tsquery","og_description":"A tsvector (text search vector) is a data type in PostgreSQL that stores preprocessed text data for efficient searching.","og_url":"https:\/\/www.w3computing.com\/articles\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\/","article_published_time":"2024-06-28T09:36:35+00:00","article_modified_time":"2024-06-28T09:36:40+00:00","author":"w3compadmin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"w3compadmin","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"TechArticle","@id":"https:\/\/www.w3computing.com\/articles\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\/#article","isPartOf":{"@id":"https:\/\/www.w3computing.com\/articles\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\/"},"author":{"name":"w3compadmin","@id":"https:\/\/www.w3computing.com\/articles\/#\/schema\/person\/a550b3e20d78bb4f79b7c6b7b53f0561"},"headline":"Implementing Full-Text Search with PostgreSQL’s tsvector and tsquery","datePublished":"2024-06-28T09:36:35+00:00","dateModified":"2024-06-28T09:36:40+00:00","mainEntityOfPage":{"@id":"https:\/\/www.w3computing.com\/articles\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\/"},"wordCount":930,"articleSection":["Database Development","PostgreSQL"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.w3computing.com\/articles\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\/","url":"https:\/\/www.w3computing.com\/articles\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\/","name":"Implementing Full-Text Search with PostgreSQL's tsvector, tsquery","isPartOf":{"@id":"https:\/\/www.w3computing.com\/articles\/#website"},"datePublished":"2024-06-28T09:36:35+00:00","dateModified":"2024-06-28T09:36:40+00:00","author":{"@id":"https:\/\/www.w3computing.com\/articles\/#\/schema\/person\/a550b3e20d78bb4f79b7c6b7b53f0561"},"description":"A tsvector (text search vector) is a data type in PostgreSQL that stores preprocessed text data for efficient searching.","breadcrumb":{"@id":"https:\/\/www.w3computing.com\/articles\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.w3computing.com\/articles\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.w3computing.com\/articles\/implementing-full-text-search-with-postgresqls-tsvector-and-tsquery\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Articles Home","item":"https:\/\/www.w3computing.com\/articles\/"},{"@type":"ListItem","position":2,"name":"Database Development","item":"https:\/\/www.w3computing.com\/articles\/database-development\/"},{"@type":"ListItem","position":3,"name":"Implementing Full-Text Search with PostgreSQL’s tsvector and tsquery"}]},{"@type":"WebSite","@id":"https:\/\/www.w3computing.com\/articles\/#website","url":"https:\/\/www.w3computing.com\/articles\/","name":"Developer Articles Hub","description":"","alternateName":"Developer Articles","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.w3computing.com\/articles\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.w3computing.com\/articles\/#\/schema\/person\/a550b3e20d78bb4f79b7c6b7b53f0561","name":"w3compadmin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.w3computing.com\/articles\/wp-content\/litespeed\/avatar\/bd481d404e42caa2763662a3bfe825f8.jpg?ver=1783167470","url":"https:\/\/www.w3computing.com\/articles\/wp-content\/litespeed\/avatar\/bd481d404e42caa2763662a3bfe825f8.jpg?ver=1783167470","contentUrl":"https:\/\/www.w3computing.com\/articles\/wp-content\/litespeed\/avatar\/bd481d404e42caa2763662a3bfe825f8.jpg?ver=1783167470","caption":"w3compadmin"},"sameAs":["http:\/\/w3computing.com\/articles"]}]}},"featured_image_src":null,"featured_image_src_square":null,"author_info":{"display_name":"w3compadmin","author_link":"https:\/\/www.w3computing.com\/articles\/author\/w3compadmin\/"},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.w3computing.com\/articles\/wp-json\/wp\/v2\/posts\/2004","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.w3computing.com\/articles\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.w3computing.com\/articles\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.w3computing.com\/articles\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.w3computing.com\/articles\/wp-json\/wp\/v2\/comments?post=2004"}],"version-history":[{"count":1,"href":"https:\/\/www.w3computing.com\/articles\/wp-json\/wp\/v2\/posts\/2004\/revisions"}],"predecessor-version":[{"id":2005,"href":"https:\/\/www.w3computing.com\/articles\/wp-json\/wp\/v2\/posts\/2004\/revisions\/2005"}],"wp:attachment":[{"href":"https:\/\/www.w3computing.com\/articles\/wp-json\/wp\/v2\/media?parent=2004"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.w3computing.com\/articles\/wp-json\/wp\/v2\/categories?post=2004"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.w3computing.com\/articles\/wp-json\/wp\/v2\/tags?post=2004"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}