In this PostgreSQL tutorial, we will learn about “Postgresql like” which is about pattern matching and cover the following topics.
- PostgreSQL like query
- PostgreSQL like case insensitive
- PostgreSQL like multiple columns
- PostgreSQL like in
- PostgreSQL like regex
- PostgreSQL like multiple values
- PostgreSQL like for integer
- PostgreSQL like any
PostgreSQL like query
In Postgresql, suppose we are a database developer of a company in the United States that sell online products. when we want to find some information like finding information about a specific product. But we don’t remember the full name of a product or we only know about the half name or some words about that product like product starting with “Ref”.
In that situation, we can use the LIKE operator to match some words with the product to find that product starting with the “Ref”.
Run the below query to know “how LIKE operator works” by finding the first name of employees starting with ‘Sh’.
SELECT first_name FROM employee
WHERE first_name like 'Sh%';
In the above code, the WHERE clause contains a special expression that is the first_name, the LIKE operator, and a string that contains a percentage sign ( % ). The string ‘Sh%’ is called a pattern.
The output of the above code is given below.

In the above output, it showed the which start with ‘Sh’ only.
Now, find the name of employees whose first name ends with ‘h’ using the below code.
SELECT first_name from employee
WHERE first_name like '%h';

Now, we are going to see another example with the table us_citites that contains the information about state name, population, density, etc of the United States. The description of the table is given below.

Let’s know the name of cities of the United States whose density is greater than 4000 using the below query and city name starts with ‘S’.
SELECT city FROM us_cities
WHERE densit > 4000 and city like 'S%';

PostgreSQL like case insensitive
In Postgresql, like can’t match case insensitive or work alone. we need to use another function Called LOWER which converts any string values such as names in lower case.
We are going to perform the same query as we performed in the above sub-section.
SELECT first_name
FROM employee
WHERE LOWER(first_name) like 'sh%';
In the above code, we are making these query case insensitive
The output of the above code is given below.

PostgreSQL like multiple columns
The LIKE operator can also be used with multiple columns or more than one column.
Let’s find the first and last names of employees starting with ‘Sa’ and ending with ‘Ma’ using the below code.
SELECT first_name,last_name FROM employee
WHERE first_name like 'Sa%' AND last_name like 'Ma%';
In the above code, we are selecting the name of an employee using the first_name, last_name column from the employee table. Then specifying the condition that select the first name which starts with ‘Sa’ and last_name starts with ‘Ma’ using WHERE clause.

In the above output, the query returns results that show the name of employees whose first name, last_name start with ‘Sa’ and end with ‘Ma’ respectively.
Read PostgreSQL Trim with Examples
PostgreSQL like regex
In Postgresql, we can achieve the functionality of a LIKE operator using the regex (regular expression) operator, and that is (~).
Let’s find the name of the employee whose name starts with ‘Ja’ using the regular expression.
SELECT first_name FROM employee where first_name ~ 'Ja';
In the above code, we are selecting the frist_name from the employee table which starts with ‘Ja’, In the WHERE clause condition is specified using the regex operator (~) that acts like a LIKE operator.
The output of the above code is given below.

The output of the above code shows all first_name start with ‘Ja’.
Read PostgreSQL Length + 12 Examples
PostgreSQL like multiple values
In Postgresql, the like operator can also match the multiple values in a column.
Run the below code to find the first name of employees starting with ‘Sa’ and ‘Ma’.
SELECT first_name FROM employee
WHERE first_name like 'Sa%' or first_name like 'Ma%';
As usual, select the first_name column from the employee in the SELECT statement, then specify the condition using the WHERE clause that we need only the name of employees that start with ‘Sa’ or ‘Ma’ and both.
The output of the above code is given below.

In the above output, we can see the result of the query that all the names start with ‘Sa’ and ‘Ma’ only.
Read Postgresql now() function
PostgreSQL like for integer
In Postgresql, we can also find or match the integer using the LIKE operator.
Let’s find the name of employees whose birth year is 1957 using the below code.
SELECT first_name,birth_date FROM employee
WHERE CAST(birth_date AS TEXT) like '1957%';
In the above code, extracting the information about first name and date of birth of employees using SELECT statement. After that specifying the condition using WHERE clause that we need only those names whose birth year starting from 1957.
In the WHERE clause, first, we are casting the birth_date column to text using the CAST function because the LIKE operator doesn’t work on the date data type.
The output of the above code is given below.

The output shows the name of employees whose birth year is 1957.
Read Postgresql Average + Examples
PostgreSQL like any
In Postgresql, Any is an operator that compares a value to a set or list of values, so we can use any with the LIKE operator in Postgresql.
We will use the below code to find the name of any employee whose first name starts with ‘An’, ‘Sa’, and ‘Ma’.
SELECT first_name FROM employee WHERE first_name like any (array['An%', 'Sa%', 'Ma%']);
In the above code, we are selecting the first name of employees whose names start with ‘An’, ‘Sa’, and ‘Ma’.
In the WHERE clause, we are specifying conditions that we need any first name of employees using ANY operator that returns true if any value of the subquery meets the condition.
We have also used the array to create a set of values using the array[].
The output of the above code is given below.

In the above output, it shows any first names of employees that start with ‘An’, ‘Sa’, and ‘Ma’.
Related PostgreSQL tutorials:
- Postgresql group_concat
- Postgresql length of string
- How to migrate from MySQL to Postgres
- Postgresql listen_addresses
- Postgresql current_timestamp
- Postgresql row_number + Examples
- Postgresql auto increment + Examples
So in this PostgreSQL tutorial, we have learned about “PostgreSQL like” and covered the following topics.
- Postgresql like query
- Postgresql like case insensitive
- Postgresql like multiple columns
- Postgresql like in
- Postgresql like regex
- Postgresql like multiple values
- Postgresql like for integer
- Postgresql like any
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.