How to use the Postgres NOT LIKE Clause

Introduction

When you’re querying data using SQL statements, there are times when you don’t necessarily want to return records that match a particular criteria, but you’d prefer to omit records that match the pattern. In these cases, the SQL NOT LIKE clause is used in PostgreSQL to handle this pattern matching. This article will take a closer look at the Postgres NOT LIKE clause and provide some examples of how to use this condition in SQL statements.

Prerequisites

Before we begin our discussion of the Postgres NOT LIKE clause, let’s take a moment to review the prerequisites that are required for this tutorial:

  • You’ll need to have PostgreSQL installed and running. If you’re working in a Linux environment, run the service postgresql status command in a terminal to check that PostgreSQL is running and then CTRL + C to exit. You can also just grep for the Postgres processes running on your machine or server with the following command:
ps aux | grep postgres
  • We’ll be using psql or the following command to access the psql command-line console under the default postgres username:
sudo su - postgres

NOTE: You can use the -U option to specify another username and the -d option for the database name, as seen in the following example: psql -U objectrocket -d some_database.

Postgres ‘NOT LIKE’ clause

In SQL, a LIKE clause is used to match a corresponding value within a record that returns a true value using wildcards. For this tutorial, we’ll use the LIKE clause in conjunction with the NOT operator to identify records where the condition is not matched.

There are two types of wildcards that are used in SQL statements:

  • % – The percent sign wildcard is used to represent one or many characters or numbers.
  • _ – The underscore wildcard is used to represent a single number or character.

Before we test out any queries, we’ll need to create a database and a table on which to run them.

Create a PostgreSQL database

Using the terminal in a Linux or macOS environment or Command Prompt on windows, we’ll enter the psql command-line interface so that we can create our database and table.

To create a database, we’ll use the simple command shown below:

CREATE DATABASE demo;

Now we can use the \c option to enter the database and create a table.

To create a table in PostgreSQL, we use this command:

CREATE TABLE some_tbl(
        id SERIAL PRIMARY KEY,
        str TEXT
);

Now that we’ve set up our table, let’s insert some records into it.

To insert data into our table, we use the following syntax:

INSERT INTO tbl_name(col1, col2) VALUES(val1, val2);

After inserting the records, we can display our inserted data using a SELECT statement query:

demo=# SELECT * FROM some_tbl;
 id |              str
----+--------------------------------
  1 | Hello World!
  2 | Kamusta Kayo?
  3 | PostgreSQL Tutorial
  4 | ObjectRocket
  5 | Easy Programming IN PostgreSQL
(5 ROWS)

Using wildcards with the Postgres ‘NOT LIKE’ condition

At this point, we’re ready to use the (%) percent sign wildcard to select all records that do not start with the letter B from our some_tbl table:

SELECT * FROM some_tbl WHERE str NOT LIKE 'B%';

The SQL statement shown above should return the following results:

 id |              str
----+--------------------------------
  2 | Kamusta Kayo?
  3 | PostgreSQL Tutorial
  4 | ObjectRocket
  5 | Easy Programming in PostgreSQL
(4 rows)

It’s important to note that the pattern matching performed in these statements is case sensitive. Let’s say we had two records in our table where one had a column value of hello, world and the other had a value of Hello, world with a capital H. In this case, a NOT LIKE condition filtering out records that start with H would return only the first of these two records.

Screenshot of psql executing NOT LIKE SQL clause to SELECT PostgreSQL records

Our next example filters out any partial string matches for the word "Postre":

SELECT * FROM some_tbl WHERE str NOT LIKE '%Postgre%';
 id |      str
----+---------------
  1 | Hello World!
  2 | Kamusta Kayo?
  4 | ObjectRocket
(3 ROWS)

NOTE: You can use the % wildcard in multiple places to create a broader query. Make sure to enclose your SQL strings with single quotation marks (') to avoid errors.

We can also use underscores (_) between two letters as wildcards in our queries. The following example filters out the records with "Kamusta" in it because the underscore will select all records that don’t have a letter in between the K and m in the str column:

SELECT * FROM some_tbl WHERE str NOT LIKE 'K_m%';
 id |              str
----+--------------------------------
  1 | Hello World!
  3 | PostgreSQL Tutorial
  4 | ObjectRocket
  5 | Easy Programming IN PostgreSQL
(4 ROWS)

NOTE: The underscore stands for a single letter, but you can use multiple underscores with this clause if you’d like to use a wildcard for multiple characters. For example, the SQL clause NOT LIKE 'f___b%' would omit all Postgres records for a column containing the values 'foo bar', foo_bar, fos bar, etc…

Conclusion

When you’re searching for data using SQL queries, you may need to filter out records that match a certain pattern. The Postgres NOT LIKE clause can be a helpful tool for identifying and omitting records that match a perticular pattern. In this tutorial, we showed multiple examples of the NOT LIKE clause used in conjunction with various wildcard operators. With these instructions and examples, you’ll be able to write your own queries to help you retrieve the exact PostgreSQL data you need.

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.