Find Gmail Customers

You are given a customers table and need to identify everyone using a Gmail address for a targeted email campaign.

Schema

CREATE TABLE customers (
  id    INTEGER PRIMARY KEY,
  name  TEXT,
  email TEXT,
  city  TEXT
);

Sample data:

Task

Return the id, name, and email of every customer whose email ends with the domain @gmail.com. Order the rows by id in ascending order.

Expected output

Approach

Substring matching on text is what LIKE is for. Match the literal domain suffix with the pattern '%@gmail.com': the leading % allows any local part before the @, and the rest of the pattern pins the domain exactly so look-alike domains are rejected.

Query

SELECT id, name, email
FROM customers
WHERE email LIKE '%@gmail.com'
ORDER BY id ASC;

Walkthrough

The pattern requires the string to end in @gmail.com. Alice (alice@gmail.com), Carol (carol@gmail.com), and Erin (erin.g@gmail.com) all satisfy it. Bob (@yahoo.com) and Dan (@company.org) fail on the domain. Frank’s frank@gmailx.com is the trap: it contains the letters gmail but does not end in @gmail.com, so the anchored pattern correctly excludes him. Ordering by id yields rows 1, 3, and 5.

Complexity & notes

A pattern with a leading % cannot use a standard B-tree index, so this is a sequential scan; a trigram (pg_trgm) index or a functional index on a reversed/extracted domain would help at scale. Use ILIKE in PostgreSQL for case-insensitive matching. The common pitfall is under-anchoring: LIKE '%gmail%' would wrongly match frank@gmailx.com and even gmail@other.com, which is why the @ and the trailing .com are part of the pattern.