{"id":1998,"date":"2024-06-27T21:02:36","date_gmt":"2024-06-27T21:02:36","guid":{"rendered":"https:\/\/www.w3computing.com\/articles\/?p=1998"},"modified":"2024-06-27T21:02:41","modified_gmt":"2024-06-27T21:02:41","slug":"how-to-implement-full-text-search-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.w3computing.com\/articles\/how-to-implement-full-text-search-in-postgresql\/","title":{"rendered":"How to Implement Full-Text Search in PostgreSQL"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">PostgreSQL, a powerful and versatile open-source relational database system, offers robust full-text search (FTS) capabilities. This tutorial will guide you through the process of implementing full-text search in PostgreSQL, leveraging its built-in features to create efficient and scalable search functionalities. This tutorial is targeted at non-beginners, assuming you have a solid understanding of PostgreSQL basics and SQL queries.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">1. Introduction to Full-Text Search<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Full-text search in PostgreSQL allows you to efficiently search textual data stored in your database. Unlike simple pattern matching with <code>LIKE<\/code>, full-text search enables more complex search capabilities, including:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Tokenization: Breaking text into meaningful units (lexemes).<\/li>\n\n\n\n<li>Normalization: Converting lexemes to a standard form.<\/li>\n\n\n\n<li>Searching: Matching search queries against indexed text.<\/li>\n\n\n\n<li>Ranking: Ordering search results based on relevance.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">2. Setting Up PostgreSQL<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Before diving into full-text search, ensure you have PostgreSQL installed. You can download PostgreSQL from the official website and follow the installation instructions for your operating system. Once installed, you can start the PostgreSQL service and access the database using <code>psql<\/code> or any other PostgreSQL client.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"Shell Session\" data-shcb-language-slug=\"shell\"><span><code class=\"hljs language-shell\"><span class=\"hljs-meta\">#<\/span><span class=\"bash\"> Start PostgreSQL service<\/span>\nsudo service postgresql start\n<span class=\"hljs-meta\">\n#<\/span><span class=\"bash\"> Access PostgreSQL using psql<\/span>\npsql -U postgres<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">Shell Session<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">shell<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">3. Creating and Populating a Sample Database<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s create a sample database to work with. We&#8217;ll create a simple blog application database with tables for posts and comments.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">DATABASE<\/span> blog;\n\\c blog\n\n<span class=\"hljs-comment\">-- Create posts table<\/span>\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> posts (\n    <span class=\"hljs-keyword\">id<\/span> <span class=\"hljs-built_in\">SERIAL<\/span> PRIMARY <span class=\"hljs-keyword\">KEY<\/span>,\n    title <span class=\"hljs-built_in\">TEXT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    <span class=\"hljs-keyword\">body<\/span> <span class=\"hljs-built_in\">TEXT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    created_at <span class=\"hljs-built_in\">TIMESTAMP<\/span> <span class=\"hljs-keyword\">DEFAULT<\/span> <span class=\"hljs-keyword\">CURRENT_TIMESTAMP<\/span>\n);\n\n<span class=\"hljs-comment\">-- Create comments table<\/span>\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> comments (\n    <span class=\"hljs-keyword\">id<\/span> <span class=\"hljs-built_in\">SERIAL<\/span> PRIMARY <span class=\"hljs-keyword\">KEY<\/span>,\n    post_id <span class=\"hljs-built_in\">INTEGER<\/span> <span class=\"hljs-keyword\">REFERENCES<\/span> posts(<span class=\"hljs-keyword\">id<\/span>),\n    <span class=\"hljs-keyword\">comment<\/span> <span class=\"hljs-built_in\">TEXT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    created_at <span class=\"hljs-built_in\">TIMESTAMP<\/span> <span class=\"hljs-keyword\">DEFAULT<\/span> <span class=\"hljs-keyword\">CURRENT_TIMESTAMP<\/span>\n);\n\n<span class=\"hljs-comment\">-- Insert sample data<\/span>\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> posts (title, <span class=\"hljs-keyword\">body<\/span>) <span class=\"hljs-keyword\">VALUES<\/span>\n(<span class=\"hljs-string\">'First Post'<\/span>, <span class=\"hljs-string\">'This is the body of the first post.'<\/span>),\n(<span class=\"hljs-string\">'Second Post'<\/span>, <span class=\"hljs-string\">'This is the body of the second post.'<\/span>);\n\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> comments (post_id, <span class=\"hljs-keyword\">comment<\/span>) <span class=\"hljs-keyword\">VALUES<\/span>\n(<span class=\"hljs-number\">1<\/span>, <span class=\"hljs-string\">'This is a comment on the first post.'<\/span>),\n(<span class=\"hljs-number\">2<\/span>, <span class=\"hljs-string\">'This is a comment on the second post.'<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">4. Understanding Full-Text Search Concepts<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Lexemes<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Lexemes are the units of meaning extracted from text. For example, the phrase &#8220;Cats are running&#8221; can be broken down into the lexemes &#8220;cat&#8221; and &#8220;run&#8221;.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Tsvector and Tsquery<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>tsvector<\/code>: A data type used to store lexemes for full-text search.<\/li>\n\n\n\n<li><code>tsquery<\/code>: A data type used to represent search queries.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Dictionaries<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Dictionaries define how text is tokenized and normalized. PostgreSQL comes with several built-in dictionaries, including Snowball stemmers for different languages.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Configurations<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Configurations specify the dictionaries and rules used for full-text search. The default configuration in PostgreSQL is <code>english<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">5. Implementing Full-Text Search<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Creating Indexes<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To enable full-text search, we need to create a <code>tsvector<\/code> column and index it.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">-- Add tsvector column to posts table<\/span>\n<span class=\"hljs-keyword\">ALTER<\/span> <span class=\"hljs-keyword\">TABLE<\/span> posts <span class=\"hljs-keyword\">ADD<\/span> <span class=\"hljs-keyword\">COLUMN<\/span> tsv tsvector;\n\n<span class=\"hljs-comment\">-- Populate tsvector column<\/span>\n<span class=\"hljs-keyword\">UPDATE<\/span> posts <span class=\"hljs-keyword\">SET<\/span> tsv = to_tsvector(<span class=\"hljs-string\">'english'<\/span>, title || <span class=\"hljs-string\">' '<\/span> || <span class=\"hljs-keyword\">body<\/span>);\n\n<span class=\"hljs-comment\">-- Create index on tsvector column<\/span>\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">INDEX<\/span> tsv_idx <span class=\"hljs-keyword\">ON<\/span> posts <span class=\"hljs-keyword\">USING<\/span> gin(tsv);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Querying with Full-Text Search<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">You can use the <code>to_tsquery<\/code> function to create a search query and match it against the <code>tsvector<\/code> column.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">-- Search for posts containing 'first'<\/span>\n<span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> posts <span class=\"hljs-keyword\">WHERE<\/span> tsv @@ to_tsquery(<span class=\"hljs-string\">'english'<\/span>, <span class=\"hljs-string\">'first'<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Ranking Search Results<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To rank search results by relevance, use the <code>ts_rank<\/code> function.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">-- Search and rank results<\/span>\n<span class=\"hljs-keyword\">SELECT<\/span> *, ts_rank(tsv, to_tsquery(<span class=\"hljs-string\">'english'<\/span>, <span class=\"hljs-string\">'first'<\/span>)) <span class=\"hljs-keyword\">AS<\/span> <span class=\"hljs-keyword\">rank<\/span>\n<span class=\"hljs-keyword\">FROM<\/span> posts\n<span class=\"hljs-keyword\">WHERE<\/span> tsv @@ to_tsquery(<span class=\"hljs-string\">'english'<\/span>, <span class=\"hljs-string\">'first'<\/span>)\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">rank<\/span> <span class=\"hljs-keyword\">DESC<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">6. Advanced Full-Text Search Techniques<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Combining Full-Text Search with Other Conditions<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">You can combine full-text search with other SQL conditions to refine your results.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">-- Search and filter by creation date<\/span>\n<span class=\"hljs-keyword\">SELECT<\/span> *, ts_rank(tsv, to_tsquery(<span class=\"hljs-string\">'english'<\/span>, <span class=\"hljs-string\">'first'<\/span>)) <span class=\"hljs-keyword\">AS<\/span> <span class=\"hljs-keyword\">rank<\/span>\n<span class=\"hljs-keyword\">FROM<\/span> posts\n<span class=\"hljs-keyword\">WHERE<\/span> tsv @@ to_tsquery(<span class=\"hljs-string\">'english'<\/span>, <span class=\"hljs-string\">'first'<\/span>) <span class=\"hljs-keyword\">AND<\/span> created_at &gt; <span class=\"hljs-keyword\">NOW<\/span>() - <span class=\"hljs-built_in\">INTERVAL<\/span> <span class=\"hljs-string\">'7 days'<\/span>\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">rank<\/span> <span class=\"hljs-keyword\">DESC<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Using Triggers for Real-Time Index Updates<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To keep the <code>tsvector<\/code> column up-to-date, use a trigger.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">-- Create trigger function<\/span>\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">FUNCTION<\/span> update_tsvector() <span class=\"hljs-keyword\">RETURNS<\/span> <span class=\"hljs-keyword\">trigger<\/span> <span class=\"hljs-keyword\">AS<\/span> $$\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    NEW.tsv := to_tsvector(<span class=\"hljs-string\">'english'<\/span>, NEW.title || <span class=\"hljs-string\">' '<\/span> || NEW.body);\n    RETURN NEW;\n<span class=\"hljs-keyword\">END<\/span>\n$$ <span class=\"hljs-keyword\">LANGUAGE<\/span> plpgsql;\n\n<span class=\"hljs-comment\">-- Attach trigger to posts table<\/span>\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TRIGGER<\/span> tsvector_update <span class=\"hljs-keyword\">BEFORE<\/span> <span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">OR<\/span> <span class=\"hljs-keyword\">UPDATE<\/span> <span class=\"hljs-keyword\">ON<\/span> posts\n<span class=\"hljs-keyword\">FOR<\/span> <span class=\"hljs-keyword\">EACH<\/span> <span class=\"hljs-keyword\">ROW<\/span> <span class=\"hljs-keyword\">EXECUTE<\/span> <span class=\"hljs-keyword\">FUNCTION<\/span> update_tsvector();<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Multilingual Search<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To handle multiple languages, create different configurations and use them as needed.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">-- Create Spanish configuration<\/span>\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-built_in\">TEXT<\/span> <span class=\"hljs-keyword\">SEARCH<\/span> CONFIGURATION spanish ( COPY = pg_catalog.spanish );\n\n<span class=\"hljs-comment\">-- Use Spanish configuration<\/span>\n<span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> posts <span class=\"hljs-keyword\">WHERE<\/span> to_tsvector(<span class=\"hljs-string\">'spanish'<\/span>, <span class=\"hljs-keyword\">body<\/span>) @@ to_tsquery(<span class=\"hljs-string\">'spanish'<\/span>, <span class=\"hljs-string\">'gato'<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">7. Performance Optimization<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Index Optimization<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Ensure your indexes are optimized for full-text search. Use <code>GIN<\/code> or <code>GiST<\/code> indexes based on your needs.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">-- Create GIN index (recommended for most cases)<\/span>\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">INDEX<\/span> tsv_idx <span class=\"hljs-keyword\">ON<\/span> posts <span class=\"hljs-keyword\">USING<\/span> gin(tsv);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Query Optimization<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Optimize queries by using appropriate configurations and avoiding unnecessary calculations.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">-- Use tsquery only once<\/span>\n<span class=\"hljs-keyword\">WITH<\/span> <span class=\"hljs-keyword\">query<\/span> <span class=\"hljs-keyword\">AS<\/span> (<span class=\"hljs-keyword\">SELECT<\/span> to_tsquery(<span class=\"hljs-string\">'english'<\/span>, <span class=\"hljs-string\">'first'<\/span>) <span class=\"hljs-keyword\">AS<\/span> q)\n<span class=\"hljs-keyword\">SELECT<\/span> *, ts_rank(tsv, q) <span class=\"hljs-keyword\">AS<\/span> <span class=\"hljs-keyword\">rank<\/span>\n<span class=\"hljs-keyword\">FROM<\/span> posts, <span class=\"hljs-keyword\">query<\/span>\n<span class=\"hljs-keyword\">WHERE<\/span> tsv @@ q\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">rank<\/span> <span class=\"hljs-keyword\">DESC<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">8. Security Considerations<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">When implementing full-text search, consider security aspects such as SQL injection. Use parameterized queries to prevent injection attacks.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">-- Use parameterized query<\/span>\n<span class=\"hljs-keyword\">PREPARE<\/span> <span class=\"hljs-keyword\">search<\/span>(<span class=\"hljs-built_in\">text<\/span>) <span class=\"hljs-keyword\">AS<\/span>\n<span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> posts <span class=\"hljs-keyword\">WHERE<\/span> tsv @@ to_tsquery(<span class=\"hljs-string\">'english'<\/span>, $<span class=\"hljs-number\">1<\/span>);\n\n<span class=\"hljs-keyword\">EXECUTE<\/span> <span class=\"hljs-keyword\">search<\/span>(<span class=\"hljs-string\">'first'<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">9. Case Study: Implementing Full-Text Search in a Blog Application<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s apply what we&#8217;ve learned to a practical example. We&#8217;ll implement full-text search in a blog application.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1: Set Up Database<\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">-- Create database and tables<\/span>\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">DATABASE<\/span> blog;\n\\c blog\n\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> posts (\n    <span class=\"hljs-keyword\">id<\/span> <span class=\"hljs-built_in\">SERIAL<\/span> PRIMARY <span class=\"hljs-keyword\">KEY<\/span>,\n    title <span class=\"hljs-built_in\">TEXT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    <span class=\"hljs-keyword\">body<\/span> <span class=\"hljs-built_in\">TEXT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    created_at <span class=\"hljs-built_in\">TIMESTAMP<\/span> <span class=\"hljs-keyword\">DEFAULT<\/span> <span class=\"hljs-keyword\">CURRENT_TIMESTAMP<\/span>\n);\n\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> comments (\n    <span class=\"hljs-keyword\">id<\/span> <span class=\"hljs-built_in\">SERIAL<\/span> PRIMARY <span class=\"hljs-keyword\">KEY<\/span>,\n    post_id <span class=\"hljs-built_in\">INTEGER<\/span> <span class=\"hljs-keyword\">REFERENCES<\/span> posts(<span class=\"hljs-keyword\">id<\/span>),\n    <span class=\"hljs-keyword\">comment<\/span> <span class=\"hljs-built_in\">TEXT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    created_at <span class=\"hljs-built_in\">TIMESTAMP<\/span> <span class=\"hljs-keyword\">DEFAULT<\/span> <span class=\"hljs-keyword\">CURRENT_TIMESTAMP<\/span>\n);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Step 2: Populate Sample Data<\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">-- Insert sample data<\/span>\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> posts (title, <span class=\"hljs-keyword\">body<\/span>) <span class=\"hljs-keyword\">VALUES<\/span>\n(<span class=\"hljs-string\">'Introduction to PostgreSQL'<\/span>, <span class=\"hljs-string\">'PostgreSQL is a powerful, open-source database system.'<\/span>),\n(<span class=\"hljs-string\">'Advanced PostgreSQL Features'<\/span>, <span class=\"hljs-string\">'This post covers advanced features of PostgreSQL.'<\/span>);\n\n<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> comments (post_id, <span class=\"hljs-keyword\">comment<\/span>) <span class=\"hljs-keyword\">VALUES<\/span>\n(<span class=\"hljs-number\">1<\/span>, <span class=\"hljs-string\">'Great introduction to PostgreSQL!'<\/span>),\n(<span class=\"hljs-number\">2<\/span>, <span class=\"hljs-string\">'I found the advanced features section very useful.'<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Step 3: Implement Full-Text Search<\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">-- Add tsvector column and index<\/span>\n<span class=\"hljs-keyword\">ALTER<\/span> <span class=\"hljs-keyword\">TABLE<\/span> posts <span class=\"hljs-keyword\">ADD<\/span> <span class=\"hljs-keyword\">COLUMN<\/span> tsv tsvector;\n\n<span class=\"hljs-keyword\">UPDATE<\/span> posts <span class=\"hljs-keyword\">SET<\/span> tsv = to_tsvector(<span class=\"hljs-string\">'english'<\/span>, title || <span class=\"hljs-string\">' '<\/span> || <span class=\"hljs-keyword\">body<\/span>);\n\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">INDEX<\/span> tsv_idx <span class=\"hljs-keyword\">ON<\/span> posts <span class=\"hljs-keyword\">USING<\/span> gin(tsv);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Step 4: Create Search Functionality<\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-15\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">-- Function to search posts<\/span>\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">FUNCTION<\/span> search_posts(<span class=\"hljs-keyword\">query<\/span> <span class=\"hljs-built_in\">TEXT<\/span>) <span class=\"hljs-keyword\">RETURNS<\/span> <span class=\"hljs-keyword\">TABLE<\/span>(<span class=\"hljs-keyword\">id<\/span> <span class=\"hljs-built_in\">INTEGER<\/span>, title <span class=\"hljs-built_in\">TEXT<\/span>, <span class=\"hljs-keyword\">body<\/span> <span class=\"hljs-built_in\">TEXT<\/span>, <span class=\"hljs-keyword\">rank<\/span> <span class=\"hljs-built_in\">REAL<\/span>) <span class=\"hljs-keyword\">AS<\/span> $$\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    <span class=\"hljs-keyword\">RETURN<\/span> <span class=\"hljs-keyword\">QUERY<\/span>\n    <span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">id<\/span>, title, <span class=\"hljs-keyword\">body<\/span>, ts_rank(tsv, to_tsquery(<span class=\"hljs-string\">'english'<\/span>, <span class=\"hljs-keyword\">query<\/span>)) <span class=\"hljs-keyword\">AS<\/span> <span class=\"hljs-keyword\">rank<\/span>\n    <span class=\"hljs-keyword\">FROM<\/span> posts\n    <span class=\"hljs-keyword\">WHERE<\/span> tsv @@ to_tsquery(<span class=\"hljs-string\">'english'<\/span>, <span class=\"hljs-keyword\">query<\/span>)\n    <span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">rank<\/span> <span class=\"hljs-keyword\">DESC<\/span>;\n<span class=\"hljs-keyword\">END<\/span>\n$$ <span class=\"hljs-keyword\">LANGUAGE<\/span> plpgsql;\n\n<span class=\"hljs-comment\">-- Test search function<\/span>\n<span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> search_posts(<span class=\"hljs-string\">'PostgreSQL'<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-15\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Step 5: Implement Triggers for Real-Time Index Updates<\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-16\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">-- Create trigger function<\/span>\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">FUNCTION<\/span> update_tsvector() <span class=\"hljs-keyword\">RETURNS<\/span> <span class=\"hljs-keyword\">trigger<\/span> <span class=\"hljs-keyword\">AS<\/span> $$\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    NEW.tsv := to_tsvector(<span class=\"hljs-string\">'english'<\/span>, NEW.title || <span class=\"hljs-string\">' '<\/span> || NEW.body);\n    RETURN NEW;\n<span class=\"hljs-keyword\">END<\/span>\n\n\n$$ <span class=\"hljs-keyword\">LANGUAGE<\/span> plpgsql;\n\n<span class=\"hljs-comment\">-- Attach trigger to posts table<\/span>\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TRIGGER<\/span> tsvector_update <span class=\"hljs-keyword\">BEFORE<\/span> <span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">OR<\/span> <span class=\"hljs-keyword\">UPDATE<\/span> <span class=\"hljs-keyword\">ON<\/span> posts\n<span class=\"hljs-keyword\">FOR<\/span> <span class=\"hljs-keyword\">EACH<\/span> <span class=\"hljs-keyword\">ROW<\/span> <span class=\"hljs-keyword\">EXECUTE<\/span> <span class=\"hljs-keyword\">FUNCTION<\/span> update_tsvector();<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-16\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Step 6: Integrate with Application<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Integrate the search functionality with your application logic, ensuring users can perform searches and view ranked results.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">10. Conclusion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Implementing full-text search in PostgreSQL provides powerful capabilities to handle complex search requirements efficiently. By understanding the core concepts and leveraging PostgreSQL&#8217;s built-in features, you can create scalable and high-performance search functionalities for your applications. This tutorial has covered the fundamental aspects and advanced techniques, offering a comprehensive guide to mastering full-text search in PostgreSQL. <\/p>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL, a powerful and versatile open-source relational database system, offers robust full-text search (FTS) capabilities. This tutorial will guide you through the process of implementing full-text search in PostgreSQL, leveraging its built-in features to create efficient and scalable search functionalities. This tutorial is targeted at non-beginners, assuming you have a solid understanding of PostgreSQL basics [&hellip;]<\/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_memberships_contains_paid_content":false,"footnotes":""},"categories":[14,22],"tags":[],"class_list":["post-1998","post","type-post","status-publish","format-standard","category-database-development","category-postgresql","entry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.6 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>How to Implement Full-Text Search in PostgreSQL<\/title>\n<meta name=\"description\" content=\"PostgreSQL, a powerful and versatile open-source relational database system, offers robust full-text search (FTS) capabilities.\" \/>\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\/how-to-implement-full-text-search-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Implement Full-Text Search in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL, a powerful and versatile open-source relational database system, offers robust full-text search (FTS) capabilities.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.w3computing.com\/articles\/how-to-implement-full-text-search-in-postgresql\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-06-27T21:02:36+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-06-27T21:02:41+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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"TechArticle\",\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/how-to-implement-full-text-search-in-postgresql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/how-to-implement-full-text-search-in-postgresql\\\/\"},\"author\":{\"name\":\"w3compadmin\",\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/#\\\/schema\\\/person\\\/a550b3e20d78bb4f79b7c6b7b53f0561\"},\"headline\":\"How to Implement Full-Text Search in PostgreSQL\",\"datePublished\":\"2024-06-27T21:02:36+00:00\",\"dateModified\":\"2024-06-27T21:02:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/how-to-implement-full-text-search-in-postgresql\\\/\"},\"wordCount\":574,\"articleSection\":[\"Database Development\",\"PostgreSQL\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/how-to-implement-full-text-search-in-postgresql\\\/\",\"url\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/how-to-implement-full-text-search-in-postgresql\\\/\",\"name\":\"How to Implement Full-Text Search in PostgreSQL\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/#website\"},\"datePublished\":\"2024-06-27T21:02:36+00:00\",\"dateModified\":\"2024-06-27T21:02:41+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/#\\\/schema\\\/person\\\/a550b3e20d78bb4f79b7c6b7b53f0561\"},\"description\":\"PostgreSQL, a powerful and versatile open-source relational database system, offers robust full-text search (FTS) capabilities.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/how-to-implement-full-text-search-in-postgresql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/how-to-implement-full-text-search-in-postgresql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/how-to-implement-full-text-search-in-postgresql\\\/#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\":\"How to Implement Full-Text Search in PostgreSQL\"}]},{\"@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=1780747165\",\"url\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/wp-content\\\/litespeed\\\/avatar\\\/bd481d404e42caa2763662a3bfe825f8.jpg?ver=1780747165\",\"contentUrl\":\"https:\\\/\\\/www.w3computing.com\\\/articles\\\/wp-content\\\/litespeed\\\/avatar\\\/bd481d404e42caa2763662a3bfe825f8.jpg?ver=1780747165\",\"caption\":\"w3compadmin\"},\"sameAs\":[\"http:\\\/\\\/w3computing.com\\\/articles\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Implement Full-Text Search in PostgreSQL","description":"PostgreSQL, a powerful and versatile open-source relational database system, offers robust full-text search (FTS) capabilities.","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\/how-to-implement-full-text-search-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"How to Implement Full-Text Search in PostgreSQL","og_description":"PostgreSQL, a powerful and versatile open-source relational database system, offers robust full-text search (FTS) capabilities.","og_url":"https:\/\/www.w3computing.com\/articles\/how-to-implement-full-text-search-in-postgresql\/","article_published_time":"2024-06-27T21:02:36+00:00","article_modified_time":"2024-06-27T21:02:41+00:00","author":"w3compadmin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"w3compadmin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"TechArticle","@id":"https:\/\/www.w3computing.com\/articles\/how-to-implement-full-text-search-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.w3computing.com\/articles\/how-to-implement-full-text-search-in-postgresql\/"},"author":{"name":"w3compadmin","@id":"https:\/\/www.w3computing.com\/articles\/#\/schema\/person\/a550b3e20d78bb4f79b7c6b7b53f0561"},"headline":"How to Implement Full-Text Search in PostgreSQL","datePublished":"2024-06-27T21:02:36+00:00","dateModified":"2024-06-27T21:02:41+00:00","mainEntityOfPage":{"@id":"https:\/\/www.w3computing.com\/articles\/how-to-implement-full-text-search-in-postgresql\/"},"wordCount":574,"articleSection":["Database Development","PostgreSQL"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.w3computing.com\/articles\/how-to-implement-full-text-search-in-postgresql\/","url":"https:\/\/www.w3computing.com\/articles\/how-to-implement-full-text-search-in-postgresql\/","name":"How to Implement Full-Text Search in PostgreSQL","isPartOf":{"@id":"https:\/\/www.w3computing.com\/articles\/#website"},"datePublished":"2024-06-27T21:02:36+00:00","dateModified":"2024-06-27T21:02:41+00:00","author":{"@id":"https:\/\/www.w3computing.com\/articles\/#\/schema\/person\/a550b3e20d78bb4f79b7c6b7b53f0561"},"description":"PostgreSQL, a powerful and versatile open-source relational database system, offers robust full-text search (FTS) capabilities.","breadcrumb":{"@id":"https:\/\/www.w3computing.com\/articles\/how-to-implement-full-text-search-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.w3computing.com\/articles\/how-to-implement-full-text-search-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.w3computing.com\/articles\/how-to-implement-full-text-search-in-postgresql\/#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":"How to Implement Full-Text Search in PostgreSQL"}]},{"@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=1780747165","url":"https:\/\/www.w3computing.com\/articles\/wp-content\/litespeed\/avatar\/bd481d404e42caa2763662a3bfe825f8.jpg?ver=1780747165","contentUrl":"https:\/\/www.w3computing.com\/articles\/wp-content\/litespeed\/avatar\/bd481d404e42caa2763662a3bfe825f8.jpg?ver=1780747165","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\/1998","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=1998"}],"version-history":[{"count":1,"href":"https:\/\/www.w3computing.com\/articles\/wp-json\/wp\/v2\/posts\/1998\/revisions"}],"predecessor-version":[{"id":1999,"href":"https:\/\/www.w3computing.com\/articles\/wp-json\/wp\/v2\/posts\/1998\/revisions\/1999"}],"wp:attachment":[{"href":"https:\/\/www.w3computing.com\/articles\/wp-json\/wp\/v2\/media?parent=1998"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.w3computing.com\/articles\/wp-json\/wp\/v2\/categories?post=1998"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.w3computing.com\/articles\/wp-json\/wp\/v2\/tags?post=1998"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}