4 ms·

It is not very useful to add full text search to an email field used for login. A regular unique index, perhaps case insensitive, is what you should be using.

VWWHFSfQ 4y ago
is case insensitive fulltext faster or slower than case insensitive index search on a varchar
- microsoftdoes 4y ago
  The index should not be slower unless something is seriously wrong.
lolinder 4y ago
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.7.44.6 https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11....
fbdab103 4y ago
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.
- datalopers 4y ago
  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.
  - 8organicbits 4y ago
    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.
  - notpushkin 4y ago
    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 Jane.Doe@example.com by registering the jane.doe@example.com address (assuming example.com does differentiate between the two) and requesting password reset for that.
    - fbdab103 4y ago
      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?
      - giantrobot 4y ago
        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.
        fbdab103 4y ago
        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.
        giantrobot 4y ago
        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.
- deleted 4y ago
  [deleted]
- mickeyp 4y ago
  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.
- znpy 4y ago
  > 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 Bob@example.com, bob@example.com and BoB@example.com all to the same mailbox.

VWWHFSfQ 4y ago

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

microsoftdoes 4y ago
The index should not be slower unless something is seriously wrong.

lolinder 4y ago

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.7.44.6 https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11....

fbdab103 4y ago

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.

datalopers 4y ago
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.
- 8organicbits 4y ago
  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.
- notpushkin 4y ago
  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 Jane.Doe@example.com by registering the jane.doe@example.com address (assuming example.com does differentiate between the two) and requesting password reset for that.
  - fbdab103 4y ago
    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?
    - giantrobot 4y ago
      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.
      - fbdab103 4y ago
        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.
        giantrobot 4y ago
        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.
deleted 4y ago
[deleted]
mickeyp 4y ago
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.
znpy 4y ago
> 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 Bob@example.com, bob@example.com and BoB@example.com all to the same mailbox.