Peter,
thank you.
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).
This is existing database deployed to many sites and used by many programs.
Re-factoring db and software to add this minor feature seems to be not
reasonable. For 99% of cases field contains only single address.
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.
I don't know how to use arrrays to solve this.
How to create constraint for char(200) column for comma separated email list
?
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
