Thread: type for storing emails?

Does anyone have a type they have developed for storing emails. I need
to do that, and the things that are in my thoughts on this are storing it as
a derived type of citext, as case should not matter, and enforcing the at
sign with pretty much anything on the left side of it, and something that
looks like a domain on the right side of it.


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

I use domains for such types (email, url, zip code, phone, ...). Using the 
regexp constraint you can validate the value. I took the regexp from some 
web site, you can change it if you wish.

CREATE DOMAIN email_address
   AS character varying(100)
   COLLATE pg_catalog."default"
   CONSTRAINT email_address_check CHECK (VALUE::text ~* 
'^[-+_\.a-z0-9]+@([a-z0-9]+(-[a-z0-9]+)*\.)+[a-z0-9]+(-[a-z0-9]+)*$'::text 
OR VALUE::text = ''::text);

 > create table tbl (id serial, email email_address);
 > insert into tbl (email) values ('bad@email');
ERROR:  value for domain email_address violates check constraint 
"email_address_check"
 > insert into tbl (email) values ('correct@email.dot.domain');
Query returned successfully: one row affected

 > select 'bad@email'::email_address;
ERROR:  value for domain email_address violates check constraint 
"email_address_check"
 > select 'correct@email.dot.domain'::email_address
correct@email.dot.domain

Regards, Martin

> https://github.com/petere/pgemailaddr
> 
> On Mon, 11 Nov 2019, 20:59 stan, <stanb@panix.com <mailto:stanb@panix.com>> 
> wrote:
> 
>     Does anyone have a type they have developed for storing emails. I need
>     to do that, and the things that are in my thoughts on this are storing
>     it as
>     a derived type of citext, as case should not matter, and enforcing the at
>     sign with pretty much anything on the left side of it, and something that
>     looks like a domain on the right side of it.
> 
> 
>     -- 
>     "They that would give up essential liberty for temporary safety deserve
>     neither liberty nor safety."
>                                                      -- Benjamin Franklin
> 
>