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(at)email');
ERROR: value for domain email_address violates check constraint
"email_address_check"
> insert into tbl (email) values ('correct(at)email(dot)dot(dot)domain');
Query returned successfully: one row affected

> select 'bad(at)email'::email_address;
ERROR: value for domain email_address violates check constraint
"email_address_check"
> select 'correct(at)email(dot)dot(dot)domain'::email_address
correct(at)email(dot)dot(dot)domain

Regards, Martin

> https://github.com/petere/pgemailaddr
>
> On Mon, 11 Nov 2019, 20:59 stan, <stanb(at)panix(dot)com <mailto:stanb(at)panix(dot)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
>
>