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?