PostgreSQL Full Text Search Tutorial

By default, searches on PostgreSQL database are exact. What that means is, when users search for ‘x y z’, the PostgreSQL database looks for ‘x y z’ in exact order in the fields of a certain table. If there’s a slight mismatch, for example let’s say you have ‘x y_z’, the database will fail to show any result.

Let’s look at a real life scenario, then you will be clear on what Full Text Search is and what is the need for it.

Let’s say you have an IT store and the user searched for ‘desktop and laptop’. No problem there. But do you actually have any product that is a desktop and a laptop? Do you even have a product whose title says ‘XXX Desktop and Laptop’ exactly as the user searched for? Most probably no! The search would fail to show any relevant results. The user probably wanted to list all the computers in your store that he or she can use as a desktop and a laptop, most likely a convertible tablet. Since the search failed to show any result to the user, the user may think you’re out of stock or you don’t have it in your IT store. But you do have many convertible tablets that can be used as a desktop and a laptop in your store’s database. If the users can’t find it, you won’t get any sales. You do want to your website to list all the convertible computers you have in stock when users do a search query like that. This is where Full Text Search comes into play. Where an ordinary search fails, Full Text Search rescues.

In this article, I will show you how to perform Full Text Search with PostgreSQL. Let’s get started.

Setting Up PostgreSQL for Full Text Search

Before you can practice Full Text Search on PostgreSQL along with me, you have to set up PostgreSQL with some dummy data. In this section, I will show you how to add these on PostgreSQL.

First, create a new database with the following command:

NOTE: Here it_store is the database name.

Now login to the PostgreSQL shell of the it_store database you created with the following command:

You should be logged in to the PostgreSQL’s it_store database as you can see in the screenshot below.

I am not going to go overboard here. I am going to create a simple products table for our IT Store database. The basics fields I need are a Product ID, Product Title, Product Description.

The SQL code for the products table is given below:

Now copy and paste the SQL command to create the products table into the PostgreSQL shell as shown in the screenshot below.

Once you’re done, products table should be created.

Now I am going to insert some products into the products table. The SQL commands are given below.

The products are inserted into the products table.

You can run the following SQL command to verify that:

As you can see, the products are in the products table.

Full Text Searching with PostgreSQL

In the earlier section, I showed you how to add dummy data to your PostgreSQL database. In this section, I will show you how to perform Full Text Search on those data with PostgreSQL.

In PostgreSQL, you use two functions to perform Full Text Search. They are to_tsvector() and to_tsquery(). Let’s see how they work and to use them first.

A Quick Tour of to_tsvector() and to_tsquery() Functions

to_tsvector() function breaks up the input string and creates tokens out of it, which is then used to perform Full Text Search using the to_tsquery() function.

Now to understand what to_tsvector() function does to an input string, open up PostgreSQL shell and run the following command:

As you can see the output of the to_tsvector() function in the marked section of the screenshot below, the to_tsvector() function broke up the words and assigned some numbers in them.

You can use to_tsquery() function as follows:

Conditions of to_tsquery() Function

The to_tsquery() accepts conditions that it uses to compare with the output of to_tsvector() function.

For example, if you’re looking for ‘laptop and desktop’, you should put ‘laptop & desktop’ to to_tsquery() function.

For ‘laptop or desktop’, the condition should be ‘laptop | desktop’.

Now I will go into our IT Store database to show you some real world examples.

Let’s search for a convertible device that can be used as desktop and laptop as I talked about earlier.

Run the following SQL command to do that:

As you can see, the correct computer was found.

You can flip the terms as well. For example, you can search for ‘laptop & desktop’.

The correct result is still displayed.

Let’s say the user wants to list all the desktop or laptop computers in your IT store. The query is ‘desktop or laptop’ and the condition of to_tsquery() function should be ‘desktop | laptop’ or ‘laptop | desktop’.

The SQL command is:

As you can see, all the computers of the IT Store are listed.

Let’s take a look at another example. The user is looking for all the laptops in your store but not the convertible ones. The user query may be ‘not convertible laptops’. The condition of to_tsquery() function may be ‘!convertible & laptops’

The SQL command is:

As you can see, the correct laptop is found.

Have you noticed one thing? I put laptops in the to_tsquery() function, but there is not laptops keyword in the product description. So how did PostgreSQL find it? Well that’s the magic of Full Text Search. The keywords need not to be exact. PostgreSQL can even handle some plural words, tenses and many more.

By default, the Full Text Search works only on English language. But PostgreSQL provides support for some other languages as well. Check the PostgreSQL documentation for more information about it.

That’s how you perform Full Text Search on PostgreSQL. Thanks for reading this article.