agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedtype for storing emails?
7+ messages / 6 participants
[nested] [flat]
* type for storing emails?
@ 2019-11-11 17:59 stan <stanb@panix.com>
0 siblings, 2 replies; 7+ messages in thread
From: stan @ 2019-11-11 17:59 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: type for storing emails?
@ 2019-11-11 18:19 Dmitry Igrishin <dmitigr@gmail.com>
parent: stan <stanb@panix.com>
1 sibling, 1 reply; 7+ messages in thread
From: Dmitry Igrishin @ 2019-11-11 18:19 UTC (permalink / raw)
To: stan <stanb@panix.com>; +Cc: pgsql-sql@lists.postgresql.org
https://github.com/petere/pgemailaddr
On Mon, 11 Nov 2019, 20:59 stan, <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
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: type for storing emails?
@ 2019-11-11 18:29 Rene Romero Benavides <rene.romero.b@gmail.com>
parent: stan <stanb@panix.com>
1 sibling, 0 replies; 7+ messages in thread
From: Rene Romero Benavides @ 2019-11-11 18:29 UTC (permalink / raw)
To: stan <stanb@panix.com>; +Cc: pgsql-sql@lists.postgresql.org
On Mon, Nov 11, 2019 at 11:59 AM stan <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
>
>
>
How are you going to handle invalid / non existent emails?
--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: type for storing emails?
@ 2019-11-12 07:37 Martin Edlman <martin.edlman@gmail.com>
parent: Dmitry Igrishin <dmitigr@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Martin Edlman @ 2019-11-12 07:37 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org; +Cc: Dmitry Igrishin <dmitigr@gmail.com>; stan <stanb@panix.com>
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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* FW: Re: type for storing emails?
@ 2019-11-12 15:07 stan <stanb@panix.com>
0 siblings, 1 reply; 7+ messages in thread
From: stan @ 2019-11-12 15:07 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
On Mon, Nov 11, 2019 at 12:29:03PM -0600, Rene Romero Benavides wrote:
> On Mon, Nov 11, 2019 at 11:59 AM stan <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
> >
> >
> >
> How are you going to handle invalid / non existent emails?
>
For the moment, I am just going to verify that what is entered is a valid
email format. Verifying that the email exists, may come later.
Thanks for making me think about this, though.
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FW: Re: type for storing emails?
@ 2019-11-12 15:50 Steve Midgley <science@misuse.org>
parent: stan <stanb@panix.com>
0 siblings, 1 reply; 7+ messages in thread
From: Steve Midgley @ 2019-11-12 15:50 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql@lists.postgresql.org
On Tue, Nov 12, 2019 at 7:21 AM Andreas Joseph Krogh <andreas@visena.com>
wrote:
> På tirsdag 12. november 2019 kl. 16:07:47, skrev stan <stanb@panix.com>:
>
> [...]
> For the moment, I am just going to verify that what is entered is a valid
> email format. Verifying that the email exists, may come later.
>
> Thanks for making me think about this, though.
>
>
>
> Note that it's only possible to verify that the email-address is *correctly
> structured and a syntactically correct*. There is no way to verify that
> an email-address actually exists, ie. that a recipient will receive emails
> sent to it.
>
Not to be pedantic but in the data model, you could have a couple of
verification data fields (e.g. verification_code; email_verified), and
actually send an email and record if the user comes back with the correct
verification code. It's obviously not something that can be handled solely
via specs or database, but a data model can accommodate actual email
verification (as I know you know, but thought I should make clear for
future readers of the archives).
Attachments:
[image/png] noname (1.9K, ../../CAJexoSK2164CXFF_82u=OufPTh0Hk2F_YLSKE6_4hq4-TN2e-A@mail.gmail.com/3-noname)
download | view image
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FW: Re: type for storing emails?
@ 2019-11-12 16:44 Andreas Joseph Krogh <andreas@visena.com>
parent: Steve Midgley <science@misuse.org>
0 siblings, 0 replies; 7+ messages in thread
From: Andreas Joseph Krogh @ 2019-11-12 16:44 UTC (permalink / raw)
To: Steve Midgley <science@misuse.org>; +Cc: pgsql-sql@lists.postgresql.org
På tirsdag 12. november 2019 kl. 16:50:35, skrev Steve Midgley <
science@misuse.org <mailto:science@misuse.org>>:
[...]
Not to be pedantic but in the data model, you could have a couple of
verification data fields (e.g. verification_code; email_verified), and actually
send an email and record if the user comes back with the correct verification
code. It's obviously not something that can be handled solely via specs or
database, but a data model can accommodate actual email verification (as I know
you know, but thought I should make clear for future readers of the archives).
My point is; There is no way to tecnichally verify that an email actually
«exists» (as in being active and will be routed to an actual recipient). Yes,
you can do all sorts of acrobatics, and may in many attempts succeed, but there
exists no method guaranteeing this. Many email-servers will for example happily
receive the email, respond "OK, got it", but throw the email in an "unknown
recipients"-bin, effectively ignoring any email sent to a non-existing address.
--
Andreas Joseph Krogh
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2019-11-12 16:44 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-11 17:59 type for storing emails? stan <stanb@panix.com>
2019-11-11 18:19 ` Dmitry Igrishin <dmitigr@gmail.com>
2019-11-12 07:37 ` Martin Edlman <martin.edlman@gmail.com>
2019-11-11 18:29 ` Rene Romero Benavides <rene.romero.b@gmail.com>
2019-11-12 15:07 FW: Re: type for storing emails? stan <stanb@panix.com>
2019-11-12 15:50 ` Steve Midgley <science@misuse.org>
2019-11-12 16:44 ` Andreas Joseph Krogh <andreas@visena.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox