PostgreSQL Like Clause

You use the LIKE clause when you need to execute a PostgreSQL query that matches a given pattern. It fetches the data from the specified table where the LIKE condition is met. For instance, if you have a case where you don’t know the exact name of an entry in the table, you can use the LIKE clause to fetch the data based on the specified condition.

The LIKE clause uses two wildcards in its query to specify the condition to be met. We will cover the PostgreSQL LIKE clause using different examples to understand how to fetch the data using various conditions.

Working with PostgreSQL Like Clause

You must understand how the two wildcards work to use the PostgreSQL LIKE clause. The percentage wildcard (%) matches zero, one, or multiple characters or numbers in the specified column. However, the underscore wildcard (_) matches a single character or number.

Let’s give some various examples to implement both wildcards with the PostgreSQL LIKE clause.

Let’s work with the following table throughout the post:

The LIKE clause mainly works with the SELECT statement and has the following syntax:

SELECT value FROM table-name WHERE COLUMN LIKE ‘pattern’;

Example 1: Where the Values Start with a Character/Value

When you want your query to return with different values that begin with a given number or character, you use the percentage wildcard after the target value or character. The following command fetches all entries that end with 400 in the “Salary” column:

When working with integers, add the ::text expression for the LIKE clause to recognize the target number as a character.

However, the command is direct for characters, like in the following example, where we fetch the data with their “lname” which begins with the “te” characters.

Suppose you only want to extract specific data from the table. You can tune the SELECT statement to specify the columns to select, as in the following case. We only select the “fname”, “salary”, and “age” columns and left out the rest.

Example 2: Return Where the Value Ends with a Character/Value

Similar to how we returned the values that start with specified condition, adding a percentage at the start of the specified value returns all the data with the specified character as the last value.

Example 3: Return the Data that Contains Specified Character within the Value

Sometimes, you may have a case where you want to fetch a given value, but you only know that it has a given character or number within. For instance, if the target data contains 21 following each other at any point, you can add %21% as the pattern to retrieve it.

Suppose you want to fetch the data with given characters following each other in a given position. Combine the underscore and the percentage wildcards. The following example retrieves the values in the “fname” column with “oh” as the second and third values:

When you want to specify a condition where you find the values with a given character in the second position and a given character at the end, you can execute your query like the following example. Here, we match the values with the “e” character as the second position and end with “r” as the last character.

Example 4: Find the Values that Start with a Given Character and Have Specific Length

When your target data begins with a given character but you don’t know the other characters, you can specify a pattern to match any data that matches the specified condition. For this example, our search “fname” value begins with “t” and has a length of five characters.

For that, we execute our command as presented in the following:

Example 5: Find the Values of a Given Length Starting and Ending with Specific Characters

When you know the starting and ending character of a given value and the length of the value, you can use the underscore wildcard. The following example fetches the values with their “fname” starting with “t” and ending with “y”, yet the value is five characters long:


The PostgreSQL LIKE clause is ideal when fetching the values that match a given condition. How you use the LIKE clause depends on your query goals and the wildcard that you use. We presented different examples of working with the LIKE clause. With that, you can comfortably use the PostgreSQL LIKE clause.

About the author

Denis Kariuki

Denis is a Computer Scientist with a passion for Networking and Cyber Security. I love the terminal, and using Linux is a hobby. I am passionate about sharing tips and ideas about Linux and computing.