Re: Email address column verification for address list

2010/4/13 Andrus <kobruleht2(at)hot(dot)ee>:
> Email address field email has type character(200)
> It can contain multiple e-mail addresses separated by comma.
> Applying validation code below removes and does not allow comma separated
> address list.
>
> How to modify this so that comma separated address list is allowed ?
> How to simplify/improve  this validation, probably some newer root domain
> names are not allowed by this validation  ?
>
> Andrus.

Why don't you just separate them into individual fields (perhaps in a
separate table to have arbitrary many addresses)? What you're doing
violates 1NF - fields should be atomic (i.e. in their simplest form,
so you never have to parse values from them).

Here's a reasonable email address domain. Note that it is AS text, not
AS character(200). It does not attempt to match the TLD to a list of
known TLDs, nor should it (that's very probably impractical, at least
with regex):

CREATE DOMAIN email_address
AS text
CONSTRAINT email_address_check CHECK ((VALUE ~
'^(([A-Za-z0-9]+_+)|([A-Za-z0-9]+\\-+)|([A-Za-z0-9]+\\.+)|([A-Za-z0-9]+\\++))*[A-Za-z0-9]+@((\\w+\\-+)|(\\w+\\.))*\\w{1,63}\\.[a-zA-Z]{2,6}$'::text));

If you absolutely must put an arbitrary number of e-mail addresses in
one field, I suggest you use the domain in an array. Unfortunately,
arrays of domains are not directly supported, last I checked.

Could someone weigh in on how to roll this domain into a custom
email_address_array domain?

Regards,
Peter Geoghegan