---
description: The PostgreSQL LIKE operator helps us to match text values against patterns using wildcards. It is possible to match the search expression to the pattern expression. If a match occurs, the LIKE operat
title: PostgreSQL LIKE, Not Like, Wildcards (%, _ ) Examples
image: https://www.guru99.com/images/postgresql-like-query-1.png
---

 

[Skip to content](#main) 

**⚡ Smart Summary**

PostgreSQL LIKE matches text against patterns using two wildcards — the percent sign and the underscore. This tutorial shows how to use LIKE and NOT LIKE in SELECT statements, with worked psql and pgAdmin examples for each case.

* 🔍 **Core Operator:** LIKE compares a column or expression against a wildcard pattern and returns true on a match.
* 🌟 **Two Wildcards:** `%` matches zero or more characters; `_` matches exactly one character.
* ✋ **Inverse Match:** Combine NOT with LIKE to return rows that do _not_ match the pattern.
* 🛠️ **Two Interfaces:** Run the same queries from the psql command line or graphically inside pgAdmin.
* 🎯 **Escape Character:** Use the ESCAPE clause to match literal % or \_ characters inside the pattern.
* 🤖 **AI Boost:** AI database tools translate plain-language search needs into LIKE patterns and suggest ILIKE when case-insensitive matching is required.

[ Read More ](javascript:void%280%29;) 

![PostgreSQL LIKE, Not Like, Wildcards \(%, _ \) Examples]()

The PostgreSQL **LIKE** operator matches text values against patterns using wildcards. If the search expression matches the pattern, LIKE returns `true`. Wildcards work in the `WHERE` clause of `SELECT`, `UPDATE`, `INSERT`, or `DELETE`.

## What are PostgreSQL Wildcards?

PostgreSQL supports two LIKE wildcards:

* **Percent sign (`%`):** matches zero, one, or many characters or digits.
* **Underscore (`_`):** matches exactly one character or digit.

The two symbols can be combined inside the same pattern. If LIKE is used without either wildcard, it behaves like the equals (`=`) operator.

## PostgreSQL LIKE Syntax

The basic LIKE syntax is:

```
expression LIKE pattern [ ESCAPE 'escape-character' ]
```

* **expression** — a character expression, typically a column or field name.
* **pattern** — a character expression that contains wildcards.
* **escape-character** — optional. Lets you match literal `%` or `_` characters. When omitted, the backslash (`\`) is the default escape character.

## PostgreSQL LIKE with the % Wildcard

The `%` sign matches zero, one, or more characters. Consider the following `Book` table:

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL1.png)

To find books whose name starts with “Lear”, run the query below.

```
SELECT *
FROM
   Book
WHERE
   name LIKE 'Lear%';
```

The query returns:

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL2.png)

To find books whose name contains “by” anywhere:

```
SELECT *
FROM
   Book
WHERE
   name LIKE '%by%';
```

The query returns:

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL3.png)

## PostgreSQL LIKE with the \_ Wildcard

The `_` sign matches exactly one character. The following query finds names where the first character is any single letter, followed by “earn” and then any suffix:

```
SELECT *
FROM
   Book
WHERE
   name LIKE '_earn%';
```

The query returns:

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL4.png)

Another example — match any text that ends with “Beginner” plus one more character:

```
SELECT *
FROM
   Book
WHERE
   name LIKE '%Beginner_';
```

The query returns:

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL5.png)

### RELATED ARTICLES

* [How to Download & Install PostgreSQL for Windows ](https://www.guru99.com/download-install-postgresql.html "How to Download & Install PostgreSQL for Windows")
* [PostgreSQL DROP/Delete DATABASE Using Command Line ](https://www.guru99.com/postgresql-drop-database.html "PostgreSQL DROP/Delete DATABASE Using Command Line")
* [PostgreSQL SUBSTRING() Function with Regex Example ](https://www.guru99.com/postgresql-substring.html "PostgreSQL SUBSTRING() Function with Regex Example")
* [PostgreSQL BETWEEN Query with Example ](https://www.guru99.com/postgresql-between.html "PostgreSQL BETWEEN Query with Example")

## PostgreSQL NOT LIKE Operator

Combine LIKE with NOT to return rows that do _not_ match the pattern. For example, list every book whose name does not start with “Post”:

```
SELECT *
FROM
   Book
WHERE
   name NOT LIKE 'Post%';
```

The query returns:

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL6.png)

Only one book satisfies the condition. Now list every book whose name does _not_ contain the word “Made”:

```
SELECT *
FROM
   Book
WHERE
   name NOT LIKE '%Made%';
```

The query returns:

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL7.png)

Three rows satisfy the condition.

## Using LIKE with pgAdmin

The same queries can also be run graphically inside pgAdmin’s Query Tool.

### % Wildcard in pgAdmin

**Step 1)** Log in to pgAdmin.

**Step 2)** In the navigation bar on the left, click **Databases**, then click **Demo**.

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL8.png)

**Step 3)** Type the query in the Query Editor:

```
SELECT *
FROM
   Book
WHERE
   name LIKE 'Lear%';
```

**Step 4)** Click **Execute**.

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL9.png)

The result pane shows the matching books:

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL10.png)

To search for a book with “by” anywhere in its name:

**Step 1)** Type the following in the Query Editor:

```
SELECT *
FROM
   Book
WHERE
   name LIKE '%by%';
```

**Step 2)** Click **Execute**.

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL11.png)

The result pane shows:

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL12.png)

### \_ Wildcard in pgAdmin

**Step 1)** Log in to pgAdmin.

**Step 2)** In the navigation bar on the left, click **Databases**, then click **Demo**.

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL13.png)

**Step 3)** Type the query in the Query Editor:

```
SELECT *
FROM
   Book
WHERE
   name LIKE '_earn%';
```

**Step 4)** Click **Execute**.

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL14.png)

The result pane shows:

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL15.png)

**Step 5)** Run the second example:

1. Type the query in the Query Editor:

```
SELECT *
FROM
   Book
WHERE
   name LIKE '%Beginner_';
```

1. Click **Execute**.

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL16.png)

The result pane shows:

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL17.png)

### NOT LIKE in pgAdmin

**Step 1)** Log in to pgAdmin.

**Step 2)** In the navigation bar on the left, click **Databases**, then click **Demo**.

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL18.png)

**Step 3)** To list every book whose name does not start with “Post”, type:

```
SELECT *
FROM
   Book
WHERE
   name NOT LIKE 'Post%';
```

**Step 4)** Click **Execute**.

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL19.png)

The result pane shows:

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL20.png)

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL21.png)

To list books whose name does not contain the word “Made”:

**Step 1)** Type the following in the Query Editor:

```
SELECT *
FROM
   Book
WHERE
   name NOT LIKE '%Made%';
```

**Step 2)** Click **Execute**.

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL22.png)

The result pane shows:

[](https://www.guru99.com/images/1/111119%5F0846%5FPostgreSQLL23.png)

[Download the database used in this tutorial](https://drive.google.com/uc?export=download&id=1BqQSvYTmNz4hvZok7iYG9ha%5Fo48%5FVF2b).

## FAQs

⚡ What is the difference between LIKE and ILIKE in PostgreSQL?

LIKE is case-sensitive while ILIKE performs case-insensitive matching. ILIKE is a PostgreSQL extension. For example, name ILIKE ‘lear%’ matches “Learn” and “LEAR” alike. Use ILIKE when user input case is unpredictable.

🚀 How do I match a literal % or \_ inside a LIKE pattern?

Use the ESCAPE clause to declare an escape character, then prefix the literal wildcard. For example, WHERE code LIKE ’50!%%’ ESCAPE ‘!’ finds values starting with “50%” because the exclamation mark escapes the percent sign.

💡 When should I use LIKE versus regular expressions in PostgreSQL?

Use LIKE for simple prefix, suffix, or substring matches. Switch to POSIX regex (\~ and \~\*) when you need character classes, alternation, or quantifiers. Regex is more powerful but typically slower than LIKE for basic patterns.

🤖 Can AI tools generate PostgreSQL LIKE queries from plain English?

Yes. [AI](https://www.guru99.com/ai-tutorial.html) assistants such as text-to-SQL copilots translate prompts like “find customers whose email contains acme” into name LIKE ‘%acme%’ and explain the wildcard logic, accelerating ad-hoc reporting.

🧠 How does generative AI optimise slow LIKE queries?

Generative AI inspects the query plan, recommends a trigram index (pg\_trgm) for leading-wildcard LIKE searches, and rewrites patterns that prevent index usage. This turns full table scans into millisecond lookups on large PostgreSQL tables.

#### Summarize this post with:

ChatGPT Perplexity Grok Google AI 

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** 

##### Sign up for the newsletter

Subscribe for Free 

You have successfully subscribed.  
Please check your inbox. 

![AI-Newsletter]() Chosen by over **350,000+** professionals 

[Scroll to top ](#wrapper)Scroll to top 

× 

Toggle Menu Close 

Search for: 

Search

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/postgresql-like-query-1.png","url":"https://www.guru99.com/images/postgresql-like-query-1.png","width":"200","height":"200","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/postgresql-like-query.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/postgresql","name":"PostgreSQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/postgresql-like-query.html","name":"PostgreSQL LIKE, Not Like, Wildcards (%, _ ) Examples"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/postgresql-like-query.html#webpage","url":"https://www.guru99.com/postgresql-like-query.html","name":"PostgreSQL LIKE, Not Like, Wildcards (%, _ ) Examples","dateModified":"2026-05-19T12:54:17+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/postgresql-like-query-1.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/postgresql-like-query.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/juniper","name":"Juniper Willow","description":"I am Juniper Willow, a PostgreSQL Developer, offering expert guidance to help you optimize and master PostgreSQL database development.","url":"https://www.guru99.com/author/juniper","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/juniper-willow-author.png","url":"https://www.guru99.com/images/juniper-willow-author.png","caption":"Juniper Willow","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"@type":"NewsArticle","headline":"PostgreSQL LIKE, Not Like, Wildcards (%, _ ) Examples","keywords":"postgresql, sql","dateModified":"2026-05-19T12:54:17+05:30","articleSection":"PostgreSQL","author":{"@id":"https://www.guru99.com/author/juniper","name":"Juniper Willow"},"publisher":{"@id":"https://www.guru99.com/#organization"},"description":"The PostgreSQL LIKE operator helps us to match text values against patterns using wildcards. It is possible to match the search expression to the pattern expression. If a match occurs, the LIKE operat","copyrightYear":"2026","copyrightHolder":{"@id":"https://www.guru99.com/#organization"},"name":"PostgreSQL LIKE, Not Like, Wildcards (%, _ ) Examples","@id":"https://www.guru99.com/postgresql-like-query.html#richSnippet","isPartOf":{"@id":"https://www.guru99.com/postgresql-like-query.html#webpage"},"image":{"@id":"https://www.guru99.com/images/postgresql-like-query-1.png"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/postgresql-like-query.html#webpage"}}]}
```