Trigger or rule to convert all strings to lower case

Question

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?

Answer 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;

Answer 2

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.

Answer 3

I'd say don't do it because the first part of an e-mail address may be [email protected], 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 <[email protected]>