PostgreSQL LIKE Operator

The LIKE operator in PostgreSQL matches a string value against a pattern. It is the standard way to perform wildcard-based string searches in WHERE clauses and is case-sensitive by default. PostgreSQL also provides ILIKE for case-insensitive matching.

Syntax

-- Match pattern
value LIKE pattern

-- Case-insensitive (PostgreSQL extension)
value ILIKE pattern

-- Negation
value NOT LIKE pattern
value NOT ILIKE pattern

PostgreSQL also supports symbolic shorthand:

If the pattern contains no wildcard characters, LIKE behaves identically to the = operator.

Pattern Wildcards

Practical Example

Create a sample product catalog for testing pattern searches:

CREATE TABLE products (
  product_id  SERIAL PRIMARY KEY,
  sku         VARCHAR(20)  NOT NULL UNIQUE,
  name        VARCHAR(150) NOT NULL,
  category    VARCHAR(60)  NOT NULL,
  description TEXT
);

INSERT INTO products (sku, name, category, description) VALUES
  ('EL-001', 'Wireless Bluetooth Headset',  'Electronics', 'Over-ear, noise cancelling'),
  ('EL-002', 'USB-C Charging Hub',           'Electronics', '7-port hub, 100W PD'),
  ('EL-003', 'Portable Bluetooth Speaker',   'Electronics', 'Waterproof, 20h battery'),
  ('FN-001', 'Ergonomic Office Chair',       'Furniture',   'Lumbar support, adjustable'),
  ('FN-002', 'Height-Adjustable Desk',       'Furniture',   'Electric, 3-preset memory'),
  ('AC-001', 'Laptop Cooling Stand',         'Accessories', 'Aluminium, 2 USB ports'),
  ('AC-002', 'Cable Management Kit',         'Accessories', 'Velcro straps and clips');

Find all electronics products:

SELECT sku, name
FROM products
WHERE category LIKE 'Electr%';

Find products with “Bluetooth” anywhere in the name (case-sensitive):

SELECT sku, name
FROM products
WHERE name LIKE '%Bluetooth%';

Expected result:

  sku   |            name
--------+-----------------------------
 EL-001 | Wireless Bluetooth Headset
 EL-003 | Portable Bluetooth Speaker

Find products with “bluetooth” (any case) using ILIKE:

SELECT sku, name
FROM products
WHERE name ILIKE '%bluetooth%';
-- Returns the same 2 rows, regardless of case in the stored data

Use the _ wildcard to match SKUs with a specific format (2 letters, dash, 3 digits):

SELECT sku, name
FROM products
WHERE sku LIKE '__-___';

This returns all rows because all SKUs follow that pattern.

ILIKE and Case-Insensitive Searching

-- Only matches 'Ergonomic Office Chair' — exact case
SELECT name FROM products WHERE name LIKE 'ergonomic%';  -- 0 rows

-- Matches regardless of case
SELECT name FROM products WHERE name ILIKE 'ergonomic%';
-- Returns: Ergonomic Office Chair

ESCAPE for Literal Wildcards

If your data contains literal % or _ characters, use the ESCAPE clause:

-- Find descriptions containing a literal percent sign, e.g. "20% off"
SELECT sku, description
FROM products
WHERE description LIKE '%20!%%' ESCAPE '!';

The !% sequence is treated as a literal % rather than a wildcard.

Performance Notes

Create a trigram index to support substring searches:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_products_name_trgm
ON products
USING GIN (name gin_trgm_ops);

After creating the index, verify it is used:

EXPLAIN ANALYZE
SELECT sku, name FROM products WHERE name ILIKE '%bluetooth%';

Testing with Vela

Schema changes like adding a trigram index for LIKE performance improvements are exactly the kind of migration to validate before applying to production. Use Vela’s database branching to create a production-data branch, add the GIN index, and benchmark LIKE '%term%' queries with EXPLAIN ANALYZE to confirm the index is used and query times improve — all without touching the live database.

Production Tips

• LIKE 'prefix%' (trailing wildcard only) can use a standard B-tree index. This is the most efficient pattern form.
• LIKE '%anything' or LIKE '%anything%' forces a sequential scan without a trigram index. Use pg_trgm GIN indexes for substring searches on large tables.
• Standard B-tree indexes are case-sensitive and do not support ILIKE. Use a citext column, a lower(column) functional index, or a pg_trgm GIN index with gin_trgm_ops for case-insensitive indexed searches.
• For language-aware full-text search (stemming, ranking), use PostgreSQL’s native tsvector/tsquery instead of LIKE.
• Avoid using LIKE on very high-cardinality columns without an index — it will result in a full sequential scan on large tables.
• Use EXPLAIN ANALYZE to confirm whether the query planner uses your index or reverts to a sequential scan.