Subject: Re: type for storing emails?
To: pgsql-sql@lists.postgresql.org
Cc: Dmitry Igrishin <dmitigr@gmail.com>, stan <stanb@panix.com>
References: <20191111175920.GA16283@panix.com>
 <CAAfz9KMASGyv9eToPJ15phRqaiQwmes4KNU9AdS9Ge53+hmW3w@mail.gmail.com>
From: Martin Edlman <martin.edlman@gmail.com>
Message-ID: <ebd4974b-4102-51cc-f1c6-e5fb81ee4faa@gmail.com>
Date: Tue, 12 Nov 2019 08:37:29 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.2.0
MIME-Version: 1.0
In-Reply-To: 
 <CAAfz9KMASGyv9eToPJ15phRqaiQwmes4KNU9AdS9Ge53+hmW3w@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: cs
Content-Transfer-Encoding: 8bit
Precedence: bulk

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