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
>
>
