csvProfiler showing a contacts export with 20,074 filled e-mail values but only 19,000 distinct values

J.Smith+shop@Gmail.com and jsmith@gmail.com can reach the same inbox. That does not make every dot, plus sign or capital letter disposable. Apply Gmail's rule to a company domain and john.smith@example.com can be a different person from johnsmith@example.com; remove every plus suffix and a perfectly ordinary mailbox whose name contains + can collide with another row.

The useful design is two columns: keep the address exactly as the user supplied it, and derive a separate matching key under rules you can defend. On a made-up list of 20 rows below, that conservative key found all six deliberately repeated rows and merged no people. A popular aggressive recipe made the dedupe number look better—ten rows appeared redundant—but four of those matches were wrong.

What is safe without knowing the provider

text
change                              safe for every domain?   why
trim surrounding spaces              yes*                     form/import cleanup; reject internal spaces
lowercase the domain                 yes                      DNS names are case-insensitive
lowercase the part before @          no                       SMTP says preserve local-part case
remove text from the first +         no                       only the receiving domain defines +
remove dots before @                 no                       a Gmail consumer-account rule
googlemail.com -> gmail.com           provider-specific        apply only to personal Google mail

*Trim before validation. A quoted local part can legally contain spaces, so do not silently rewrite
valid quoted addresses if your application accepts that uncommon syntax.

RFC 5321 is awkward but clear: a mailbox local part must be treated as case-sensitive, while the domain follows case-insensitive DNS rules. It also discourages providers from exploiting case sensitivity. That is why lowercasing everything often works in practice, but “often” is not enough for a unique key that can attach one person's orders to another person's account.

Provider documentation lets us go further in narrow places. Personal Gmail addresses ignore dots; Google's own page explicitly says dots do matter on work, school and other organization domains. Google documents +word variations, as do Outlook.com and Fastmail. Those promises belong to the named service, not to every domain whose MX happens to point there. A custom-domain administrator can change provider or routing later.

The 20-row test: six good matches, four tempting bad ones

We assigned the following invented rows to 14 invented people. The same labels are the ground truth; separate labels must remain separate. This is not a claim about a production customer list. It is a small fixture you can paste into a test and extend with the providers your application accepts.

text
person  address
A       J.Smith+shop@Gmail.com
A       jsmith@gmail.com
A       j.smith+news@googlemail.com
A       JSMITH@gmail.com
A       js.mith@gmail.com
B       chester.beane@outlook.com
B       chester.beane+microsoft@outlook.com
C       trail@fastmail.com
C       trail+hiking@fastmail.com
D       Sales@customer.example
E       sales@customer.example             different person
F       john.smith@customer.example
G       johnsmith@customer.example          different person
H       pat+shop@customer.example
I       pat@customer.example                different person
J       other@gmail.com
K       john.smith@outlook.com
L       johnsmith@outlook.com               different person
M       maria@example.net
N       lee@example.org
text
policy                                      distinct keys   rows matched   false merges
raw address, byte for byte                         20              0              0
provider-aware rules below                         14              6              0
lowercase + strip dots/+ for every domain          10             10              4

provider-aware gain: (20 - 14) / 20 = 30% of rows joined to an existing person
aggressive apparent gain: (20 - 10) / 20 = 50%, but 4 of its 10 matches are false

The four bad pairs are Sales/sales at the custom domain, the two custom-domain dot variants, the two custom-domain plus variants, and the Outlook dot variants. Outlook documents plus addressing; it does not say dots are ignored. An apparent 50% match rate is therefore worse data than an honest 30%.

A provider-aware canonical expression

The examples below deliberately recognize only the public domains supported by the cited rules. They lowercase personal Gmail local parts, remove Gmail dots and plus tags, map googlemail.com to gmail.com, and remove a plus tag at Outlook.com and Fastmail while preserving the base local part's case. Everything else preserves the local part and lowercases only the domain. Validate that there is exactly one @ before this expression runs; parsing every legal quoted mailbox is a job for an e-mail parser, not split_part.

sql
-- PostgreSQL 18: a stored generated column
ALTER TABLE contacts ADD COLUMN email_key text
GENERATED ALWAYS AS (
  CASE lower(split_part(btrim(email), '@', 2))
    WHEN 'gmail.com' THEN
      lower(regexp_replace(split_part(split_part(btrim(email), '@', 1), '+', 1), '\.', '', 'g'))
      || '@gmail.com'
    WHEN 'googlemail.com' THEN
      lower(regexp_replace(split_part(split_part(btrim(email), '@', 1), '+', 1), '\.', '', 'g'))
      || '@gmail.com'
    WHEN 'outlook.com' THEN
      split_part(split_part(btrim(email), '@', 1), '+', 1) || '@outlook.com'
    WHEN 'fastmail.com' THEN
      split_part(split_part(btrim(email), '@', 1), '+', 1) || '@fastmail.com'
    ELSE split_part(btrim(email), '@', 1) || '@' || lower(split_part(btrim(email), '@', 2))
  END
) STORED;
sql
-- MySQL 8.4: STORED generated column; REGEXP_REPLACE removes every dot
ALTER TABLE contacts ADD COLUMN email_key varchar(320)
GENERATED ALWAYS AS (
  CASE lower(substring_index(trim(email), '@', -1))
    WHEN 'gmail.com' THEN concat(lower(regexp_replace(substring_index(substring_index(trim(email), '@', 1), '+', 1), '\\.', '')), '@gmail.com')
    WHEN 'googlemail.com' THEN concat(lower(regexp_replace(substring_index(substring_index(trim(email), '@', 1), '+', 1), '\\.', '')), '@gmail.com')
    WHEN 'outlook.com' THEN concat(substring_index(substring_index(trim(email), '@', 1), '+', 1), '@outlook.com')
    WHEN 'fastmail.com' THEN concat(substring_index(substring_index(trim(email), '@', 1), '+', 1), '@fastmail.com')
    ELSE concat(substring_index(trim(email), '@', 1), '@', lower(substring_index(trim(email), '@', -1)))
  END
) STORED;
sql
-- SQLite 3.31+: instr/substr instead of split_part
ALTER TABLE contacts ADD COLUMN email_key TEXT
GENERATED ALWAYS AS (
  CASE lower(substr(trim(email), instr(trim(email), '@') + 1))
    WHEN 'gmail.com' THEN lower(replace(substr(trim(email), 1,
      CASE WHEN instr(substr(trim(email), 1, instr(trim(email), '@') - 1), '+') > 0
           THEN instr(substr(trim(email), 1, instr(trim(email), '@') - 1), '+') - 1
           ELSE instr(trim(email), '@') - 1 END), '.', '')) || '@gmail.com'
    WHEN 'googlemail.com' THEN lower(replace(substr(trim(email), 1,
      CASE WHEN instr(substr(trim(email), 1, instr(trim(email), '@') - 1), '+') > 0
           THEN instr(substr(trim(email), 1, instr(trim(email), '@') - 1), '+') - 1
           ELSE instr(trim(email), '@') - 1 END), '.', '')) || '@gmail.com'
    ELSE substr(trim(email), 1, instr(trim(email), '@') - 1) || '@' ||
         lower(substr(trim(email), instr(trim(email), '@') + 1))
  END
) VIRTUAL;

-- SQLite ALTER TABLE can add a VIRTUAL generated column, not a STORED one.
-- This expression is intentionally Gmail-only. Add tested branches for other providers,
-- preferably through application code, rather than turning this into unreadable SQL.

The SQLite version demonstrates the generated-column mechanism, but it also shows the maintenance cost of encoding policy in string expressions. In a real product, put this behavior in one well-tested application function, save its result, and rebuild keys when the policy version changes. A generated column is attractive when the provider list is deliberately short and schema changes are controlled.

Inspect collisions before adding UNIQUE

sql
SELECT email_key, count(*) AS rows,
       string_agg(email, ' | ' ORDER BY email) AS originals
FROM contacts
WHERE email_key IS NOT NULL
GROUP BY email_key
HAVING count(*) > 1
ORDER BY rows DESC, email_key;              -- PostgreSQL

-- MySQL: GROUP_CONCAT(email ORDER BY email SEPARATOR ' | ')
-- SQLite: group_concat(email, ' | ') (ordering needs an input subquery)

Review those groups with account history, verification status and user identifiers. Do not let an index choose the surviving row. Also decide whether a plus address is identity or intent: stripping +billing may be right for dedupe, while keeping the raw address is still necessary for replies, auditing and knowing which sign-up route was used.

sql
-- After resolving every existing collision
CREATE UNIQUE INDEX contacts_email_key_uq
  ON contacts (email_key)
  WHERE email_key IS NOT NULL;              -- PostgreSQL / SQLite

CREATE UNIQUE INDEX contacts_email_key_uq
  ON contacts (email_key);                  -- MySQL permits multiple NULL values

A unique canonical key is suitable only when one inbox is allowed one account. It is the wrong constraint for family accounts, shared team inboxes, or systems where the same mailbox may represent several legal entities. In those systems, use the key to propose matches, not to reject inserts.

Three rules that prevent most damage

First, verify the address. Canonicalization predicts delivery equivalence; a successful e-mail code proves control. Second, keep the raw address forever alongside the key. Third, version the policy. If you later add Yahoo, a custom domain, or a new Gmail rule, recompute into email_key_v2, inspect the changed collision groups, then move the index. A quiet in-place rewrite can merge accounts during an ordinary deployment.

Have the addresses in a CSV? csvProfiler shows distinct counts and the most frequent values in your browser, without uploading the file. Use it to size the cleanup before you write an UPDATE.

Open csvProfiler

Open the collision query and the affected rows side by side in Pilotbase, a free database client for Windows, macOS and Linux or Docker on a server.

Get Pilotbase

Related: Addresses and phone numbers that match: normalising them in SQL before you compare · Merging duplicate customers without breaking orders

Sources, checked 7 November 2026: RFC 5321, sections 2.3.11 and 2.4 (the local part is interpreted by its domain and must be preserved; domains are case-insensitive); Gmail Help: dots don't matter (personal Gmail, and the explicit Workspace exception); Google Workspace Learning Center: plus variations; Microsoft Support: site-specific + addresses in Outlook.com; Fastmail: plus and subdomain addressing. The 20-row fixture and calculations are our own.