How to Use the PostgreSQL Like Operator
Introduction
In this ObjectRocket tutorial we will be discussing how to use the PostgreSQL LIKE operator. It’s primary function is to compare strings. For example if you wanted to query user emails that ended in @gmail.com
. That’s something you could do with the LIKE operator. As you will see the LIKE operator is great to know because it is flexible, simple, and it’s a very common query performed in the real-world.
Prerequisites
Before we proceed on discussing how to use the like operator in PostgreSQL we need to make certain prerequisites.
Make sure that there is a PostgreSQL server installed in your local device, execute the terminal command
sudo systemctl status PostgreSQL
that uses the systemd software suite in a Linux distribution system to view its status.You can also verify the version of the
psql
shell used for SQL queries in PostgreSQL by executing the commandpsql -V
in the terminal.
Accessing the Terminal in Postgres
PostgreSQL also has an interactive terminal that is used for executing SQL queries or commands for the database.
To access the psql
, execute the bash command :
1 | sudo -u PostgreSQL psql |
Create a Database and Table
Before we proceed on the topic of this article, we must first create a database and a table for our like operator example in PostgreSQL.
To create a database, execute the command :
1 | CREATE DATABASE sampledb; |
NOTE: The command \c
is used to connect and navigate inside the PostgreSQL database and the command \l
is used to get a list of database names in your local device.
And to create a table in PostgreSQL you must execute the command :
1 2 3 | CREATE TABLE some_tablename( COLUMN_NAME + DATA_TYPE + CONSTRAINT [OPTIONAL] ); |
The PostgreSQL LIKE Operator
The PostgreSQL LIKE
operator assigns text values patterns utilizing wildcards. Unless the statement query can be compared to the expression, it will return true.
Below is the following syntax for using a LIKE
operator.
1 | SELECT FROM some_tablename WHERE some_column_name LIKE 'expression + wildcard'; |
The two wildcards that represent the conjunction in a LIKE
operator are :
The percent sign
%
that represents as zero or multiple characters or numbers that are used for sequence matching of characters.The underscore sign
_
that represents a single character or number that is used for matching the single character.
PostgreSQL Like Query Example
Supposedly we have a table with a value like this :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | id | firstname | lastname | gender | email | dob | country ----+-----------+------------+--------+----------------------------+------------+--------------- 1 | Alfredo | Brandon | Male | abrandon@gmail.com | 1953-11-15 | Russia 2 | Colmore | Omar | Male | colmore@gmail.com | 1960-05-12 | Peru 3 | Adriana | Matuschek | Female | matuschekadriana@gmail.com | 1977-12-01 | China 4 | Amalita | Ofiler | Female | ofiler@yahoo.com | 1989-03-05 | China 5 | Gerri | Foulspon | Male | gerri@hotmail.com | 1955-01-12 | Norway 6 | Caroline | Airton | Female | cairton@hotmail.com | 1990-03-18 | Poland 7 | Niko | Kime | Male | kimenik@yahoomail.com | 1979-12-20 | United States 8 | Ingmar | Eaglestone | Male | ingmar@gmail.com | 1953-11-15 | Russia 9 | Holly | Aether | Male | hether@gmail.com | 1960-06-14 | Peru 10 | Mashia | Brenly | Female | Brenly@gmail.com | 1977-12-01 | China 11 | Nollie | Probet | Male | probet@yahoo.com | 1990-07-10 | China 12 | Louis | Sherbok | Male | sherbok@hotmail.com | 1955-01-12 | Norway 13 | Leena | Bogie | Female | lbogie@hotmail.com | 1992-03-18 | Poland 14 | Janot | Domigon | Female | dumigon@gmail.com | 1979-11-20 | United States |
And we want to display names that contain the string ine
. We can use the LIKE
operator to do this query.
To execute :
1 | SELECT * FROM some_user WHERE firstname LIKE '%ine%'; |
The output returns the following result:
1 2 3 | id | firstname | lastname | gender | email | dob | country ----+-----------+----------+--------+---------------------+------------+--------- 6 | Caroline | Airton | Female | cairton@hotmail.com | 1990-03-18 | Poland |
NOTE: We used the asterisk sign *
to return all of the columns from the some_user table.
Here’s another example of a query to search for entries in the some_user
table where the dob
field contains the string 199
. We use the %
to match any string.
1 | SELECT firstname, dob FROM some_user WHERE dob::text LIKE '%199%'; |
The result of this query is:
1 2 3 4 5 | firstname | dob -----------+------------ Caroline | 1990-03-18 Nollie | 1990-07-10 Leena | 1992-03-18 |
This will return rows that have 199
in any position.
Conclusion
We hope this tutorial on the LIKE operator in PostgreSQL was helpful to you. The use of this operator is very common so it is a good operator to become very familiar with. Thank you for joining us for this ObjectRocket tutorial.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started