---
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

![PostgreSQL LIKE, Not Like, Wildcards (%, \_ ) Examples](https://www.guru99.com/images/postgresql-like-query-1.png)

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:

[![Sample Book table used in the LIKE examples]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL1.png)

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

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

The query returns:

[![LIKE Lear% query result returning matching books]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL2.png)

To find books whose name contains “by” anywhere:

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

The query returns:

[![LIKE %by% query result with matching books]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL3.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:

[![LIKE \_earn% query result with single-character prefix matches]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL4.png)

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

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

The query returns:

[![LIKE %Beginner\_ query result matching trailing single character]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL5.png)

**Don't Miss:**

- [PostgreSQL Data Types: Numeric, Character, Byte](https://www.guru99.com/postgresql-data-types.html)
- [How to Create Database in PostgreSQL](https://www.guru99.com/postgresql-create-database.html)
- [PostgreSQL Array: Functions, Type, Example](https://www.guru99.com/postgresql-array-functions.html)
- [PostgreSQL SUBSTRING() Function with Regex Example](https://www.guru99.com/postgresql-substring.html)

## 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:

[![NOT LIKE Post% result excluding books that start with Post]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL6.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:

[![NOT LIKE %Made% result returning three matching books]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL7.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**.

[![pgAdmin navigation tree with Databases and Demo expanded]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL8.png)

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

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

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

[![pgAdmin Query Tool with the Execute toolbar button]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL9.png)

The result pane shows the matching books:

[![pgAdmin result pane for LIKE Lear% query]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL10.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**.

[![pgAdmin Query Tool running LIKE %by% query]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL11.png)

The result pane shows:

[![pgAdmin result pane for LIKE %by% query]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL12.png)

### \_ Wildcard in pgAdmin

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

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

[![pgAdmin navigation tree showing Databases tree]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL13.png)

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

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

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

[![pgAdmin Query Tool running LIKE \_earn% query]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL14.png)

The result pane shows:

[![pgAdmin result pane for LIKE \_earn% query]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL15.png)

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

1. Type the query in the Query Editor:

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

2. Click **Execute**.

[![pgAdmin Query Tool running LIKE %Beginner\_ query]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL16.png)

The result pane shows:

[![pgAdmin result pane for LIKE %Beginner\_ query]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL17.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**.

[![pgAdmin navigation tree for NOT LIKE example]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL18.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**.

[![pgAdmin Query Tool running NOT LIKE Post% query]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL19.png)

The result pane shows:

[![pgAdmin result pane for NOT LIKE Post% query]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL20.png)

[![Additional pgAdmin result detail for NOT LIKE query]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL21.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**.

[![pgAdmin Query Tool running NOT LIKE %Made% query]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL22.png)

The result pane shows:

[![pgAdmin result pane for NOT LIKE %Made% query]()](https://www.guru99.com/images/1/111119_0846_PostgreSQLL23.png)

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

## 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:

ChatGPTPerplexityGrokGoogle 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)

×

Search for:

```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-10-08T16:59:01+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/editorial","name":"Editorial Team","url":"https://www.guru99.com/author/editorial","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/favicon-new-logo.png","url":"https://www.guru99.com/images/favicon-new-logo.png","caption":"Editorial Team","inLanguage":"en-US"},"sameAs":["https://www.guru99.com/editorial-team"],"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"@type":"NewsArticle","headline":"PostgreSQL LIKE, Not Like, Wildcards (%, _ ) Examples","keywords":"postgresql, sql","dateModified":"2026-10-08T16:59:01+05:30","articleSection":"PostgreSQL","author":{"@id":"https://www.guru99.com/author/editorial","name":"Editorial Team"},"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"}}]}
```