Thread: Looking for types: phone number, email addresses

Looking for types: phone number, email addresses

From
Stephane Bortzmeyer
Date:
Lazy programmer is looking for implementation of the following
datatypes:

* phone (or fax) numbers (with format checking) like +33 1 39 30 83 46
* email addresses (with format checking and ideally a test that the MX
  record does exist)

I've looked in the following places:

* contrib/ (isbn_issn is a very useful starting point, if I need to
  do it myself)

* techdocs.postgresql.org (the datatypes section of the cookbook is
  empty)

* FAQ There is "5.2) How can I contribute some nifty new types and
  functions to PostgreSQL?" but not the reverse :-)




Re: Looking for types: phone number, email addresses

From
Oliver Elphick
Date:
On Fri, 2002-07-19 at 12:52, Stephane Bortzmeyer wrote:
> Lazy programmer is looking for implementation of the following
> datatypes:
>
> * phone (or fax) numbers (with format checking) like +33 1 39 30 83 46

CHECK (phone ~ '^\\+33 [0-9]( [0-9]{2}){4}$')

That's only good for French numbers, since only France uses that
grouping of digits for phone numbers.  (Which is why +33 is explicit.)

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Blessed is the man that trusteth in the LORD, and
      whose hope the LORD is."            Jeremiah 17:7


Re: Looking for types: phone number, email addresses

From
Stephane Bortzmeyer
Date:
On Fri, Jul 19, 2002 at 02:47:43PM +0100,
 Oliver Elphick <olly@lfix.co.uk> wrote
 a message of 25 lines which said:

> CHECK (phone ~ '^\\+33 [0-9]( [0-9]{2}){4}$')
>
> That's only good for French numbers, since only France uses that
> grouping of digits for phone numbers.

OK, let's check:

CHECK (phone ~ '^\\+[0-9]+[ 0-9]+$')

which is, I believe, international (although the above regexp does not
prevent strange things like two consecutive spaces).

Re: Looking for types: phone number, email addresses

From
Lee Kindness
Date:
Stephane Bortzmeyer writes:
 >  Oliver Elphick <olly@lfix.co.uk> wrote
 > > CHECK (phone ~ '^\\+33 [0-9]( [0-9]{2}){4}$')
 > > That's only good for French numbers
 > OK, let's check:
 > CHECK (phone ~ '^\\+[0-9]+[ 0-9]+$')
 > which is, I believe, international (although the above regexp does not
 > prevent strange things like two consecutive spaces).

Still a loosing battle and IMO a needless constraint... For example
I'd normally write my work number as '+44 (0)131 557 5595' which the
above regex wouldn't grok... And it's also common in several counties
for people to use '.' and '-' as separators!

Is the field going to be used by humans (in which case they can sort
out minor formatting issues on the fly) or for automated dialing
(in which case you'd probably want to strip out everything bar
numerics)?

Lee.

Re: Looking for types: phone number, email addresses

From
Oliver Elphick
Date:
On Fri, 2002-07-19 at 15:07, Stephane Bortzmeyer wrote:
> OK, let's check:
>
> CHECK (phone ~ '^\\+[0-9]+[ 0-9]+$')
>
> which is, I believe, international (although the above regexp does not
> prevent strange things like two consecutive spaces).

That would match "+0 ", which is probably not a valid number.  You can
combine several patterns as alternatives in the CHECK constraint or even
put alternatives in the pattern.
--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Blessed is the man that trusteth in the LORD, and
      whose hope the LORD is."            Jeremiah 17:7


Re: Looking for types: phone number, email addresses

From
Stephane Bortzmeyer
Date:
On Fri, Jul 19, 2002 at 03:27:19PM +0100,
 Lee Kindness <lkindness@csl.co.uk> wrote
 a message of 20 lines which said:

> Is the field going to be used by humans (in which case they can sort
> out minor formatting issues on the fly) or for automated dialing
> (in which case you'd probably want to strip out everything bar
> numerics)?

Mostly by humans (I don't foresee automatic use) but the idea was to
catch mispellings (such as typing O "o" instead of 0 "zero").

Email addresses, on the other hand, *will* be used automatically
(opt-in only, do not worry).

At the present time, I use:

 -- Not perfectly RFC 2822-compliant :-(
 email TEXT NOT NULL CHECK (email ~ '^[a-zA-Z0-9\+\.\_\-]+@[a-zA-Z0-9\.\-]+$'),
 phone TEXT CHECK (phone ~ '^\\+[0-9]+[ 0-9]+$'),
 fax TEXT CHECK (fax ~ '^\\+[0-9]+[ 0-9]+$'),


Re: Looking for types: phone number, email addresses

From
Josh Jore
Date:
Remember to allow quotes and spaces as well. This is a valid address (no,
not that I actually defined it as an alias):

"Josh Jore"@greentechnologist.org


Joshua b. Jore ; http://www.greentechnologist.org

On Fri, 19 Jul 2002, Stephane Bortzmeyer wrote:

> On Fri, Jul 19, 2002 at 03:27:19PM +0100,
>  Lee Kindness <lkindness@csl.co.uk> wrote
>  a message of 20 lines which said:
>
> Email addresses, on the other hand, *will* be used automatically
> (opt-in only, do not worry).
>
> At the present time, I use:
>
>  -- Not perfectly RFC 2822-compliant :-(
>  email TEXT NOT NULL CHECK (email ~ '^[a-zA-Z0-9\+\.\_\-]+@[a-zA-Z0-9\.\-]+$'),
>  phone TEXT CHECK (phone ~ '^\\+[0-9]+[ 0-9]+$'),
>  fax TEXT CHECK (fax ~ '^\\+[0-9]+[ 0-9]+$'),
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Re: Looking for types: phone number, email addresses

From
Stephane Bortzmeyer
Date:
On Fri, Jul 19, 2002 at 03:40:36PM +0100,
 Oliver Elphick <olly@lfix.co.uk> wrote
 a message of 23 lines which said:

> That would match "+0 ", which is probably not a valid number.

With integrity constraints, you have to know when you stop :-) Not
everything can be declared in SQL (I'm still searching for a way to
have a trigger fire at the end of a *transaction*, in order to check
cross-table integrity), so no check is perfect. I just wanted to catch
the majority of the mistakes that people can do when they type phone
numbers, not all of them.

Many thanks to all for the advices (and to Oliver for the Debian
package since this is the one I use).


Re: Looking for types: phone number, email addresses

From
Jan Wieck
Date:
Stephane Bortzmeyer wrote:
>
> On Fri, Jul 19, 2002 at 02:47:43PM +0100,
>  Oliver Elphick <olly@lfix.co.uk> wrote
>  a message of 25 lines which said:
>
> > CHECK (phone ~ '^\\+33 [0-9]( [0-9]{2}){4}$')
> >
> > That's only good for French numbers, since only France uses that
> > grouping of digits for phone numbers.
>
> OK, let's check:
>
> CHECK (phone ~ '^\\+[0-9]+[ 0-9]+$')

Many phone systems here in the US use a central phone number and then
touch tone dialing for the extension you want to reach. Written it looks
like

+1 234 567-8901 x234

where the extension can have any number of digits (depends on the phone
system, usually 3 or 4).

Also since nearly all phones here have letters, it is very common to
give you easy to remember phone numbers like 1-800-UCALLME which in
reality is 1-800-822-5563.

I don't think that restricting it one way or the other is a good idea at
all. It doesn't prevent from entering the wrong number anyway, so what
good is it?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: Looking for types: phone number, email addresses

From
Stephane Bortzmeyer
Date:
On Fri, Jul 19, 2002 at 10:11:43AM -0500,
 Josh Jore <josh@greentechnologist.org> wrote
 a message of 28 lines which said:

> Remember to allow quotes and spaces as well. This is a valid address (no,
> not that I actually defined it as an alias):
>
> "Josh Jore"@greentechnologist.org

Yes, but to parse such addresses (not just to allow blindly spaces and
quotes in any order), regexps are not sufficient (hence my initial
request for a type, with a real parser).

Checking that an email address is actually RFC 2822 is far from a
trivial task...


Re: Looking for types: phone number, email addresses

From
Bruno Wolff III
Date:
On Fri, Jul 19, 2002 at 10:11:43 -0500,
  Josh Jore <josh@greentechnologist.org> wrote:
> Remember to allow quotes and spaces as well. This is a valid address (no,
> not that I actually defined it as an alias):
>
> "Josh Jore"@greentechnologist.org

You may not want to store rfc 821 encodings of addresses.
Josh Jore@greentechnologist.org
is a valid email address. It just needs to be encoded when used in an
smtp transaction. Some MUAs don't have a way to inject messages with
unencoded email addresses. Others (such as qmail) do. Constraints on
unencoded addresses are going to be a lot simpler than those on rfc 821
encoded addresses.