In PostgreSQL, we have LIKE and ILIKE operators for simple search purposes. These operators return true when a specified term matches with the term in the data. There also exists an operator that works opposite to these operators. This operator is referred to as the NOT LIKE operator. The NOT LIKE operator returns false if the match to the specified text is found and true when the match is not found after searching. This means that when searching for a term with the NOT LIKE operator, the query will retrieve all those records that do not contain or match that text/string.

This write-up will investigate the functioning NOT LIKE operator in detail.

What Does NOT LIKE Operator Do in Postgres?

The search operators basically use wildcards to do the text/string matching. These wildcards are specified using 2 operators: the percentage sign “%” that matches the set of characters and the underscore sign “_” that matches a character. The matching is done using the wildcards and the values are returned accordingly. The NOT LIKE operator matches the string using the wildcards and returns the values that are not matched to the specified string. The basic syntax for the NOT LIKE operator is given as:

SELECT FROM tab_name
WHERE col_name NOT LIKE match_pattern;

In this syntax:

● First, we have to specify the table name after the SELECT FROM clause.

● After the WHERE statement, we will specify the name of the column in which we want to search for the string.

● Lastly, after the “NOT LIKE” operator, we will specify the match_pattern of our choice using the wildcards.

Let’s move towards an example to get a good grip on this topic.

Example: How Does the NOT LIKE Operator Work in Postgres?

Let’s consider a table named “simplesearch”, the data of which is depicted in the following screenshot:

Suppose we want to get the document entry that does not contain the word “ate. To perform this specific thing, we will execute the following syntax:

SELECT document 
 FROM simplesearch
 WHERE document NOT LIKE '%ate%';

The above query will give all the data which do not contain the “ate” word. The output is:

The query has searched for the word ”ate” in the whole list of documents. If any record matches the string “ate” it will not be returned. Conversely, if the string does not match any record, it will be included in the query results.

This is how the NOT LIKE operator works in Postgres.

Conclusion

The NOT LIKE operator functions opposite the LIKE and ILIKE operators in Postgres. This operator looks for the specified character/text/string in the database and returns those records that do not contain that specified string/pattern. The pattern is specified using the wildcards. In this post, we have learned about the NOT LIKE operator in Postgres with a practical example and how is it different from the other simple search operators.