1

I have about 26 applications interacting with our database, some of these will store email addresses in mixed case. Since I made the mistake of using varchar as the column type and not a case insensitive one, I would like to make some kind of trigger or rule, which will convert all attempts to save email addresses as mixed case, into lower case. I could achieve the same using the following query:

ALTER TABLE your_table
ADD CONSTRAINT your_table_the_column_lowercase_ck
CHECK (the_column = lower(the_column));

But that would throw error messages in all the applications, and potentially stopping new customers from registering. Therefore, I am looking for this auto-converting rule/trigger which will handle any updates or inserts into this table. Is it possible to do this at all, so it won't come back and bite me later?

  • 1
    Just to note, varchar isn't case sensitive or case insensitive - that's down to the collation setting on the relevant column. – George.Palacios May 25 '17 at 12:58
  • 1
    The domain name part of an email address is case-insensitive, but the username part is actually case-sensitive – Neil McGuigan May 25 '17 at 21:33
  • Did any of these answers assist you, can mark one as chosen? – Evan Carroll Dec 1 '17 at 20:51
1

Because we're talking about email addresses you should first read my answer to the question, What is the best way to store an email address in PostgreSQL?. I would adopt that method. It's not too late.

That said, the code above is certainly not ideal. You should instead just migrate to citext. This overrides the = to essentially be lower(a) = lower(b)

ALTER TABLE your_table
  ALTER COLUMN the_column
  SET DATA TYPE citext;
0

This is pretty similar to a question like:

https://stackoverflow.com/questions/22540221/postgresql-trigger-function-on-after-insert-updates-stack-depth-limit-error

You will need two things:

  1. a trigger function that basically just does lower() on the new column value.
  2. a trigger on that table on INSERT or UPDATE that runs the trigger function.
0

I'd say don't do it because the first part of an e-mail address may be Case.Sensitive@this-part-not-case-sensitive.com, see: https://en.wikipedia.org/wiki/Email_address

Although maybe it isn't, 99% of the time. Or 90%. Or 65%. I don't know. A side-effect is that nowadays, if e-mail isn't delivered, probably no one tells you.

Of course you can just write "lower(the_column)" when you write a query to get data - but perhaps you want to sort them by address and the case is spoiling it. (I'm not sure why you would sort e-mail addresses except to please a boss who is a bit confused about things, which sometimes we have to do.)

On the other hand, validating e-mail address text is a huge topic. See above, I suppose. We get personal name included, we get two e-mails in the field, we get a phone number maybe for text messaging (which we didn't say we will do)...

Per Wikipedia, maybe the thing to insist on is "display name for the recipient, which is followed by the address specification surrounded by angled brackets, for example:"

John Smith <john.smith@example.org>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.