Today I could be a lucky 10,000 - are emails case sensitive? I had always assumed you could pre-process (ie lowercase) them before insertion so that database case sensitivity was not an issue.

Technically speaking the portion of the email address before the @ is case-sensitive. However in practice it is ubiquitous that they’re treated as case-insensitive across all mail platforms.

This means however that you should store the original email and not just lower case it on insert. Imagine if you could reset password for [email protected] by registering the [email protected] address (assuming example.com does differentiate between the two) and requesting password reset for that.

Surely this is why standards are important. An email server could use whatever logic it wants to determine which account to deliver an email to. But if email is to be used by other services as an authentication mechanism there certainly better be a widely adopting standard for how emails get delivered.

It goes deeper than that. If emails are case sensitive, everything changes in the context of unique accounts. If you have jane.doe@ and Jane.doe@ attempts to login - what do you do?

You create a contact address from a normalized version of the entered address (after address verification) and an independent account ID. You can also generate an account ID derived from that normalized address.

The positive response of the address verification will tell you the address is deliverable and the user has access to it. Later if someone tries to register a capitalized form of the address it'll get rejected because of that account ID collision. Then the user can be pushed to a password recovery path where they'll need access to the e-mail/MFA to get control of the account.

My point was that I think it is bad user experience if my email is "jane.doe@", but autocorrect has me input "Jane.doe@" (something I have experienced before). As a user, I "entered the same thing". On a technical level, they are different, but a decision must be made as to what is the true representation.

Amusingly, the context of this thread was in using case-insensitive search for email fields, but if emails are truly case sensitive, this is all moot, because you can only do direct comparisons.

In practical terms e-mail addresses are case insensitive. So if on account creation your normalize the address (lower case, trim white space) and send a verification e-mail and they successfully verify you can safely derive an ID from that normalized address. It won't matter later if autocorrect tries a mixed case address since you normalize and compare it on the back end.

If you run into a case where their e-mail server enforces case sensitivity they have bigger problems to deal with. E-mail has long been a system that requires loose adherence to the specs.

Does anyone know a single example of a case sensitive email provider or email server implementation? I believe I saw a positive answer to this 10 years back (an old university mail server?) but these must be quite rare.

Just use the citext extension and move on with your life. This is a solved problem: citext recalls the casing but querying and indexing against it is case-insensitive.

> are emails case sensitive?

IIRC, as per spec/rfc, e-mail addresses ARE case sensitive.

However the de-facto standard is to ignore such thing and deliver emails for [email protected], [email protected] and [email protected] all to the same mailbox.

There's an extension for trigram similarity operators which is useful for a quick and easy fuzzy search for small things like email and name:

https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11....

is case insensitive fulltext faster or slower than case insensitive index search on a varchar

The index should not be slower unless something is seriously wrong.