How to Use the PostgreSQL Like Operator

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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 command psql -V in the terminal.

Screenshot of the psql status

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.