PostgreSQL LIKE and ILIKE Operators

The LIKE and ILIKE operators in PostgreSQL perform pattern matching on text using wildcards. LIKE is case-sensitive while ILIKE is case-insensitive. They're essential for searching text data with partial matches and patterns.

Syntax

-- Case-sensitive pattern matching
column LIKE 'pattern'
column NOT LIKE 'pattern'

-- Case-insensitive pattern matching (PostgreSQL specific)
column ILIKE 'pattern'
column NOT ILIKE 'pattern'

-- Wildcards:
-- % matches zero or more characters
-- _ matches exactly one character

Official Documentation

Example usage

-- Match pattern with % wildcard
SELECT * FROM users
WHERE email LIKE '%@gmail.com';

-- Match pattern at start
SELECT * FROM products
WHERE name LIKE 'Apple%';

-- Match pattern at end
SELECT * FROM files
WHERE filename LIKE '%.pdf';

-- Match pattern in middle
SELECT * FROM articles
WHERE title LIKE '%PostgreSQL%';

-- Single character wildcard (_)
SELECT * FROM codes
WHERE code LIKE 'A_C';  -- Matches 'ABC', 'A1C', 'AZC', etc.

-- Case-insensitive search (ILIKE)
SELECT * FROM users
WHERE username ILIKE 'john%';
-- Matches 'john', 'John', 'JOHN', 'JohnDoe', etc.

-- Multiple wildcards
SELECT * FROM products
WHERE description LIKE '%eco%friendly%';

-- NOT LIKE
SELECT * FROM emails
WHERE email NOT LIKE '%@spam.com';

-- Exact single character matching
SELECT * FROM product_codes
WHERE code LIKE '___-___';  -- Matches 'ABC-123', etc. (3 chars, dash, 3 chars)

-- Escape special characters
SELECT * FROM filenames
WHERE name LIKE '%\_%' ESCAPE '\';
-- Matches filenames containing underscore

-- Alternative escape syntax
SELECT * FROM data
WHERE text LIKE '%#%%' ESCAPE '#';
-- Matches strings containing literal %

-- Combining LIKE conditions
SELECT * FROM products
WHERE (name LIKE '%phone%' OR name LIKE '%mobile%')
  AND name NOT LIKE '%case%';

-- Case-sensitive vs insensitive
SELECT
    'Test' LIKE 'test' AS case_sensitive,        -- false
    'Test' ILIKE 'test' AS case_insensitive;     -- true

-- LIKE with indexes (requires pattern index)
CREATE INDEX idx_users_email_pattern ON users (email text_pattern_ops);
SELECT * FROM users WHERE email LIKE 'john%';

-- Performance: prefix matching works with indexes
SELECT * FROM users WHERE email LIKE 'john%';  -- Can use index
SELECT * FROM users WHERE email LIKE '%john';  -- Cannot use index

-- Complex patterns
SELECT * FROM products
WHERE sku LIKE '[A-Z][A-Z][A-Z]-[0-9][0-9][0-9]';
-- Note: PostgreSQL LIKE doesn't support [] range syntax, use SIMILAR TO instead

-- Filter file extensions
SELECT * FROM documents
WHERE filename ILIKE '%.pdf'
   OR filename ILIKE '%.doc'
   OR filename ILIKE '%.docx';

-- Search multiple columns
SELECT * FROM contacts
WHERE first_name ILIKE '%smith%'
   OR last_name ILIKE '%smith%'
   OR email ILIKE '%smith%';

-- Partial phone number match
SELECT * FROM customers
WHERE phone LIKE '%555%';

-- URL pattern matching
SELECT * FROM links
WHERE url LIKE 'https://%';

Common issues

1. Leading wildcards: Patterns like '%term' cannot use indexes and are slow on large tables.
2. Case sensitivity: LIKE is case-sensitive; use ILIKE for case-insensitive searches.
3. Special characters: % and _ are wildcards. Escape them with \ to match literally.
4. No regex: LIKE doesn't support regex. Use ~ operator or SIMILAR TO for regex patterns.

Best practices

1. Use ILIKE for user-facing searches where case doesn't matter.
2. Put wildcards at the end when possible for index usage: LIKE 'prefix%'.
3. For regex patterns, use ~ or ~* operators instead of LIKE.
4. Create text_pattern_ops indexes for LIKE queries: CREATE INDEX ON table (column text_pattern_ops).

Frequently Asked Questions

Q: What's the difference between LIKE and ILIKE?
A: LIKE is case-sensitive: 'Hello' LIKE 'hello' is false. ILIKE is case-insensitive (PostgreSQL specific): 'Hello' ILIKE 'hello' is true.

Q: Can LIKE use indexes?
A: Yes, but only for prefix patterns like 'prefix%'. Patterns with leading wildcards '%suffix' or '%middle%' cannot use regular B-tree indexes. Create a text_pattern_ops index for LIKE optimization.

Q: How do I match a literal % or _ character?
A: Escape with backslash: LIKE '%\_%' matches strings containing underscore. Or use custom escape: LIKE '%#%%' ESCAPE '#'.

Q: Is there a more powerful pattern matching than LIKE?
A: Yes, use SIMILAR TO for SQL regex, or ~ and ~* operators for full POSIX regular expressions. For example: WHERE text ~ '[0-9]{3}-[0-9]{4}'.

Q: Why is my LIKE query slow?
A: Likely using leading wildcards: LIKE '%term'. This requires full table scan. Consider full-text search (tsvector/tsquery) or trigram indexes for better performance on substring searches.