From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Email address column verification for address list |
Date: | 2010-04-13 11:49:59 |
Message-ID: | BBA5690DDB60497A8CB67D36B98C6BEF@andrusnotebook |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
update klient set email=null where email='' OR
(trim(trailing from email) !~*
'^[^(at)]*@(?:[^(at)]*\\(dot))?[a-z0-9_-]+\\.(?:a[defgilmnoqrstuwz]|b[abdefghijmnorstvwyz]|c[acdfghiklmnoruvxyz]|d[ejkmoz]|e[ceghrst]|f[ijkmorx]|g[abdefhilmnpqrstuwy]|h[kmnrtu]|i[delnoqrst]|j[mop]|k[eghimnprwyz]|l[abcikrstuvy]|m[acdghklmnopqrstuvwxyz]|n[acefgilopruz]|om|p[aefghklmnrtwy]|qa|r[eouw]|s[abcdeghijklmnortvyz]|t[cdfghjkmnoprtvwz]|u[agkmsyz]|v[aceginu]|w[fs]|y[etu]|z[amw]|edu|com|net|org|gov|mil|info|biz|coop|museum|aero|name|pro|mobi|arpa|eu)$');alter table klient add check (trim(trailing from email) ~*'^[^(at)]*@(?:[^(at)]*\\(dot))?[a-z0-9_-]+\\.(?:a[defgilmnoqrstuwz]|b[abdefghijmnorstvwyz]|c[acdfghiklmnoruvxyz]|d[ejkmoz]|e[ceghrst]|f[ijkmorx]|g[abdefhilmnpqrstuwy]|h[kmnrtu]|i[delnoqrst]|j[mop]|k[eghimnprwyz]|l[abcikrstuvy]|m[acdghklmnopqrstuvwxyz]|n[acefgilopruz]|om|p[aefghklmnrtwy]|qa|r[eouw]|s[abcdeghijklmnortvyz]|t[cdfghjkmnoprtvwz]|u[agkmsyz]|v[aceginu]|w[fs]|y[etu]|z[amw]|edu|com|net|org|gov|mil|info|biz|coop|museum|aero|name|pro|mobi|arpa|eu)$');
From | Date | Subject | |
---|---|---|---|
Next Message | Clemens Eisserer | 2010-04-13 12:14:06 | Re: Where to configure pg_xlog file-size? |
Previous Message | Kenichiro Tanaka | 2010-04-13 11:12:40 | Re: Need some help with a query (uniq -c) |