Re: type for storing emails?

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 ('[email protected]');
Query returned successfully: one row affected

 > select 'bad@email'::email_address;
ERROR:  value for domain email_address violates check constraint 
"email_address_check"
 > select '[email protected]'::email_address
[email protected]

Regards, Martin

> https://github.com/petere/pgemailaddr
> 
> On Mon, 11 Nov 2019, 20:59 stan, <[email protected] <mailto:[email protected]>> 
> 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
> 
>