How to Use the Postgres Similar To Operator
Introduction
When you need to find values that match a certain pattern in a PostgreSQL query, there are a few different ways to get the job done. While the LIKE
operator might be the first solution that comes to mind, there are many situations where the SIMILAR TO
operator offers a better way to accomplish the task. What’s the difference between these PostgreSQL operators? We’ll explain exactly what sets these two operators apart later in the tutorial. In addition, we’ll talk about how the Postgres SIMILAR TO
operator works and look at some examples of its use.
Prerequisites
It’s important to make sure the following prerequisites are in place before you attempt to follow along with the examples in this tutoria:
- First, make sure you have PostgreSQL installed and running on your machine. If you’re not sure whether PostgreSQL is installed, you can use the command
psql -V
to return the version number of the psql interface. - You should also have at least some basic knowledge of PostgreSQL. Some familiarity with this database will make it easier to understand our instructions and examples.
Postgres SIMILAR TO syntax
Let’s begin our discussion of the Postgres SIMILAR TO
operator by comparing and contrasting it to the LIKE
operator. Both operators allow you to do pattern matching within a query, returning a value of true
if a match is found and false
if the value doesn’t match the pattern. However, there’s an important difference to note: the SIMILAR TO
operator evaluates patterns according to the definition of a regular expression as established by the SQL standard. Due to this behavior, SIMILAR TO
acts as a hybrid of sorts, combining typical regular expression notation and LIKE
notation. Put simply, the SIMILAR TO
operator provides a bit more pattern-matching power.
We’ll use the following student
table in our examples. If you’re planning to follow along and test out the examples in this tutorial, it’s a good idea to create a table of your own similar to this one:
1 2 3 4 5 6 7 8 9 10 11 12 13 | id | name | gender | grade ----+-----------------+--------+------- 1 | Paulina Bright | Female | 100 2 | Eliza Herring | Female | 100 3 | Darren Ferguson | Male | 90 4 | Artur Cisneros | Male | 89 5 | Brittany Hodges | Female | 100 6 | Joe Green | Male | 90 7 | George Welch | Male | 90 8 | Anthony Mann | Male | 89 9 | Michele Morgan | Female | 88 10 | Alycia Seaton | Female | 100 11 | Tania Schwartz | Female | 100 |
The query shown below will select student records where the gender
is ‘Male’:
1 | SELECT * FROM student WHERE gender SIMILAR TO 'Male'='t'; |
Here’s what the expected output should look like:
1 2 3 4 5 6 7 8 | id | name | gender | grade ----+-----------------+--------+------- 7 | George Welch | Male | 90 6 | Joe Green | Male | 90 4 | Artur Cisneros | Male | 89 8 | Anthony Mann | Male | 89 3 | Darren Ferguson | Male | 90 (5 rows) |
We can see that the pattern we specified with our SIMILAR TO
operator was defined correctly, returning just the records with a ‘Male’ value for gender
.
Our next query will select students where the name
starts with ‘B’:
1 2 3 4 5 | SELECT * FROM student WHERE name SIMILAR TO 'B%'; id | name | gender | grade ----+-----------------+--------+------- 5 | Brittany Hodges | Female | 100 (1 ROW) |
The following query will select students where the name
starts with ‘A’ or ‘J’:
1 2 3 4 5 6 7 8 | SELECT * FROM student WHERE name SIMILAR TO '%(A|J)%'; id | name | gender | grade ----+----------------+--------+------- 6 | Joe Green | Male | 90 4 | Artur Cisneros | Male | 89 8 | Anthony Mann | Male | 89 10 | Alycia Seaton | Female | 100 (4 ROWS) |
Conclusion
When you need to perform any type of pattern matching in PostgreSQL, it’s important to choose the right method to meet your needs. When you need to combine some of the functionality of the LIKE
operator with the pattern-matching power of regular expressions, the SIMILAR TO
operator is the best tool for the job. In this article, we took an in-depth look at the Postgres SIMILAR TO operator, providing several examples to illustrate its use. With our examples to guide you, you’ll be able to implement the SIMILAR TO operator in your own PostgreSQL queries.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started