type for storing emails?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

type for storing emails?

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


Reply | Threaded
Open this post in threaded view
|

Re: type for storing emails?

Dmitry Igrishin

On Mon, 11 Nov 2019, 20:59 stan, <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: type for storing emails?

ichbinrene
In reply to this post by stan-9

On Mon, Nov 11, 2019 at 11:59 AM stan <[hidden email]> 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



How are you going to handle invalid / non existent emails?

--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison


Reply | Threaded
Open this post in threaded view
|

Re: type for storing emails?

Martin Edlman-2
In reply to this post by Dmitry Igrishin
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 ('[hidden email]');
Query returned successfully: one row affected

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

Regards, Martin

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