Thread: US Telephone Number Type

US Telephone Number Type

From
"Karen Hill"
Date:
Hello,

How would one go about creating a US telephone type in the format of
"(555)-555-5555" ?  I am at a loss on how it could be accomplished in
the most correct way possible while not going into the various
different country styles e.g. +01 (555) 555-5555.

Is the difficulty of creating a telephone type the reason it is not in
postgresql already?

Should the telephone type be able to do something such as:

SELECT * from tableFOO where telephone.areacode = 555;

Or would regex be better?


regards,


Re: US Telephone Number Type

From
Martijn van Oosterhout
Date:
On Mon, Jul 10, 2006 at 10:33:52AM -0700, Karen Hill wrote:
> Hello,
>
> How would one go about creating a US telephone type in the format of
> "(555)-555-5555" ?  I am at a loss on how it could be accomplished in
> the most correct way possible while not going into the various
> different country styles e.g. +01 (555) 555-5555.

How do you mean "styles". The above number is, when printed in standard
international format +15555555555. The number needed to dial
international is not relevent. What makes it tricky is that people
don't agree on how numbers should be formatted.

> Is the difficulty of creating a telephone type the reason it is not in
> postgresql already?

It wouldn't be hard, it's just not clear what the advantage is over
just having a string and some functions to display the number.

> Should the telephone type be able to do something such as:
>
> SELECT * from tableFOO where telephone.areacode = 555;

Maybe, but is that useful? Maybe America is different, but my
experience in NL and AU is that you rarely care about the areacode
anyway, so why would you want to pull it out?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

Re: US Telephone Number Type

From
Vivek Khera
Date:
On Jul 10, 2006, at 1:33 PM, Karen Hill wrote:

> Is the difficulty of creating a telephone type the reason it is not in
> postgresql already?
>
> Should the telephone type be able to do something such as:
>
> SELECT * from tableFOO where telephone.areacode = 555;
>
> Or would regex be better?

makes more sense to store them in a a canonical format and then find
things with pattern matches.



Attachment

Re: US Telephone Number Type

From
Jorge Godoy
Date:
"Karen Hill" <karen_hill22@yahoo.com> writes:

> Hello,
>
> How would one go about creating a US telephone type in the format of
> "(555)-555-5555" ?  I am at a loss on how it could be accomplished in
> the most correct way possible while not going into the various
> different country styles e.g. +01 (555) 555-5555.
>
> Is the difficulty of creating a telephone type the reason it is not in
> postgresql already?

The above mask wouldn't be correct for Brazilian phone numbers, for example.
Our prefix has four digits here, and our area code has only two digits, so
we'd need something like +55 (55) 5555-5555.

So, I believe that there's no phone type because type differs from country to
country.  IIRC, in Germany there's a lot more difference from old numbers to
new ones, making it annoying to even define something for localizing phone
numbers for them.

> Should the telephone type be able to do something such as:
>
> SELECT * from tableFOO where telephone.areacode = 555;
>
> Or would regex be better?

It depends on how far into normalization you're willing to go and what kind of
information you're willing to retrieve.

Here we can guarantee that the same prefix grants that the numbers are
phisically near one to the other, so it might be interesting to map it to make
some geographic assumption on data (it is not accurate since one switch can
have several prefixes, but it gives a rough idea anyway).

In one project we did model our phone table as:

   - country code -> inside the country table
   - area code -> city table

   - prefix
   - number

But in a latter project I denormalized this and went with:

   - country code -> country table
   - area code    -> city table

   - prefix + number


We don't want to manipulate individual phone numbers -- they are a "property"
of a person's data and we manipulate it like that.

Also, think about storing numbers not the formatted output.  This will make it
easier to work with and if you need to change something it looks easier.
Writing a function or view to retrieve the information the way you need it is
also an option.


Be seeing you,
--
Jorge Godoy      <jgodoy@gmail.com>

Re: US Telephone Number Type

From
Richard Broersma Jr
Date:
> > Is the difficulty of creating a telephone type the reason it is not in
> > postgresql already?
> >
> > Should the telephone type be able to do something such as:
> >
> > SELECT * from tableFOO where telephone.areacode = 555;
> >
> > Or would regex be better?
>
> makes more sense to store them in a a canonical format and then find
> things with pattern matches.


Also,  due to the problem of keeping area codes segregated in large growing population centers,
there is strong talk about allowing overlapping area codes.  Dialing locally will require 11
digits instead of the usual 7.

I know that this is already the case in the state of Georgia and there is talk about adopting it
in California.

Regards,

Richard Broersma Jr.

Re: US Telephone Number Type

From
Vivek Khera
Date:
On Jul 10, 2006, at 3:46 PM, Richard Broersma Jr wrote:

> Also,  due to the problem of keeping area codes segregated in large
> growing population centers,
> there is strong talk about allowing overlapping area codes.
> Dialing locally will require 11
> digits instead of the usual 7.
>

around here every local call is 10 digits due to ovelapping area
codes (been this way for over 10 years now.)   why would you need to
dial 1 first for a local call?  and how would this make a phone
number format different if stored in a canonical form already?


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD  +1-301-869-4449 x806



Attachment

Re: US Telephone Number Type

From
Tom Lane
Date:
"Karen Hill" <karen_hill22@yahoo.com> writes:
> How would one go about creating a US telephone type in the format of
> "(555)-555-5555" ?

Are you sure that's what you want?  Even within the US there's the issue
of extension numbers; I'm not sure how useful it is to have a datatype
that refuses anything but the basic 10-digit format.

It doesn't seem particularly hard to make a type that stores just the
digits (applying whatever amount of error-checking seems appropriate
on the non-digit stuff it's throwing away) and on output regurgitates
a standardized format.  Minimum support would just be an input function
and an output function, and it doesn't seem like you need too many other
functions besides them ... do you need indexing support?

> Should the telephone type be able to do something such as:
> SELECT * from tableFOO where telephone.areacode = 555;

It'd be syntactically easier as a function:

    areacode(telephone) = 555

            regards, tom lane

Re: US Telephone Number Type

From
Steve Atkins
Date:
On Jul 10, 2006, at 11:07 AM, Martijn van Oosterhout wrote:

> On Mon, Jul 10, 2006 at 10:33:52AM -0700, Karen Hill wrote:
>> Hello,
>>
>> How would one go about creating a US telephone type in the format of
>> "(555)-555-5555" ?  I am at a loss on how it could be accomplished in
>> the most correct way possible while not going into the various
>> different country styles e.g. +01 (555) 555-5555.
>
> How do you mean "styles". The above number is, when printed in
> standard
> international format +15555555555. The number needed to dial
> international is not relevent. What makes it tricky is that people
> don't agree on how numbers should be formatted.
>
>> Is the difficulty of creating a telephone type the reason it is
>> not in
>> postgresql already?
>
> It wouldn't be hard, it's just not clear what the advantage is over
> just having a string and some functions to display the number.
>
>> Should the telephone type be able to do something such as:
>>
>> SELECT * from tableFOO where telephone.areacode = 555;
>
> Maybe, but is that useful? Maybe America is different, but my
> experience in NL and AU is that you rarely care about the areacode
> anyway, so why would you want to pull it out?

Strong correlation to geographical area - very useful for sales
campaigns or geolocation. Also, free numbers (aka 800 numbers
in the US) have distinctive area codes. Of course, identifying
the area code is easy in the US, but much harder (or even
meaningless) elsewhere. In other bits of the world area codes
allow you to identify mobile numbers.

A general phone number type would have a country, an area
code, a local number and an optional extension. Possibly a
"type" (tel, fax, modem) too, possibly not. Possibly an optional
alternate format, so that you can store 1-800-MY-APPLE, but
also be able to treat it as +18006927753. And probably all the
other weirdnesses in RFC 2806 too. Outputs might be E.164,
RFC 2806 URL or (country-specific) "human-readable".

Doing it "right" would be very complex, and overkill for most
applications. Doing a simplistic
version that only supported something like E.164 or only
supported US formating would be easy - but so application
space specific, why bother? Just use a text field or three.

Cheers,
   Steve

Re: US Telephone Number Type

From
SCassidy@overlandstorage.com
Date:
In California, we definitely care about the area code, as there are several
area codes (at least 4) in San Diego County.  I have to use 1+area code to
dial home from work, and vice-versa.

Susan



             Martijn van
             Oosterhout
             <kleptog@svana.or                                          To
             g>                        Karen Hill <karen_hill22@yahoo.com>
             Sent by:                                                   cc
             pgsql-general-own         pgsql-general@postgresql.org
             er@postgresql.org                                     Subject
                                       Re: [GENERAL] US Telephone Number
                                       Type
             07/10/2006 11:07
             AM
                                      |-------------------|
                                      | [ ] Expand Groups |
             Please respond to        |-------------------|
                Martijn van
                Oosterhout
             <kleptog@svana.or
                    g>






On Mon, Jul 10, 2006 at 10:33:52AM -0700, Karen Hill wrote:
> Hello,
----- snip -------

Maybe, but is that useful? Maybe America is different, but my
experience in NL and AU is that you rarely care about the areacode
anyway, so why would you want to pull it out?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
litigate.
(See attached file: signature.asc)

----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------


Attachment

Re: US Telephone Number Type

From
Steve Crawford
Date:
Steve Atkins wrote:
>...
>>> Should the telephone type be able to do something such as:
>>>
>>> SELECT * from tableFOO where telephone.areacode = 555;
>>
>> Maybe, but is that useful? Maybe America is different, but my
>> experience in NL and AU is that you rarely care about the areacode
>> anyway, so why would you want to pull it out?
>
> Strong correlation to geographical area - very useful for sales
> campaigns or geolocation. Also, free numbers (aka 800 numbers
> in the US) have distinctive area codes. Of course, identifying
> the area code is easy in the US, but much harder (or even
> meaningless) elsewhere. In other bits of the world area codes
> allow you to identify mobile numbers.

It's actually quite useful to separate out both the NPA (area-code) and
NXX (prefix) in US numbers. We subscribe to data that lets us determine
lots of things for a given NPA/NXX (MSA, PMSA, lat/lon, ratecenter,
zip-codes covered, time-zone, observes daylight-saving?,
wireless/wireline, etc.)

Of course with number portability you can't rely on just the NPA and NXX
to determine whether the number is wireless but you can subscribe to
other data that lists all the numbers that have been ported from
wireless to wireline or vice-versa to fix that issue.

Cheers,
Steve

Re: US Telephone Number Type

From
"Karen Hill"
Date:
Tom Lane wrote:

> It doesn't seem particularly hard to make a type that stores just the
> digits (applying whatever amount of error-checking seems appropriate
> on the non-digit stuff it's throwing away) and on output regurgitates
> a standardized format.  Minimum support would just be an input function
> and an output function, and it doesn't seem like you need too many other
> functions besides them

I did a quick google and someone mentioned that input and output
functions need to be written in C.  Is that still the case?

Anyway, there could be multiple number types to choose from such as:

telephone-us-basic  : (555) 555-5555
telephone-us-extention : (555) 555-5555 ext 1234

Other locals (EU, etc) could create their own to their local
specifications. This would seem like a nice contrib package.


Re: US Telephone Number Type

From
Chris Browne
Date:
kleptog@svana.org (Martijn van Oosterhout) writes:
> What makes it tricky is that people don't agree on how numbers
> should be formatted.

There is a relevant standard, E.164b, where US/Canadian telnos are
formatted like:

  +1.4166734124

It should be quite clear how *any* phone number in those countries
would be formatted, given that example...

>> Is the difficulty of creating a telephone type the reason it is not
>> in postgresql already?
>
> It wouldn't be hard, it's just not clear what the advantage is over
> just having a string and some functions to display the number.

Unfortunately, the above represents something of a "lowest common
denominator," which, for those that are exchange/area code-happy, is
woefully insufficient.

Mind you, I'd argue that attempts to use more data are quite likely to
be doomed to failure...

>> Should the telephone type be able to do something such as:
>>
>> SELECT * from tableFOO where telephone.areacode = 555;
>
> Maybe, but is that useful? Maybe America is different, but my
> experience in NL and AU is that you rarely care about the areacode
> anyway, so why would you want to pull it out?

At one time, it was a pretty meaningful determinant of location.

But it is growing increasingly useless, as it is increasingly common
for there to be numerous somewhat-overlapping "area codes" for any
given metropolitan region.

The Toronto region (in Canada, albeit, but under much the same rules)
includes "area codes" 416, 905, and 647.

The Dallas/Fort Worth region includes area codes 214, 972, 817, 469,
and 682.

NYC includes area codes 212, 347, 516, 631, 646, 718, 917.

Attempts to evaluate terribly much based on area codes are
increasingly likely to fail...
--
select 'cbbrowne' || '@' || 'acm.org';
http://cbbrowne.com/info/unix.html
Don't be so open-minded that your brains fall out.

Re: US Telephone Number Type

From
Ron Johnson
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Chris Browne wrote:
> kleptog@svana.org (Martijn van Oosterhout) writes:
[snip]
> Attempts to evaluate terribly much based on area codes are
> increasingly likely to fail...

Especially with VoIP and number portability.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEsvSkS9HxQb37XmcRAqcnAJ4kFcr2zAzxpzlAxQVLcZiU8f6niQCg31R4
dwujmmYgvC9WM4guS1VrABU=
=+u53
-----END PGP SIGNATURE-----

Re: US Telephone Number Type

From
Chris Browne
Date:
rabroersma@yahoo.com (Richard Broersma Jr) writes:
>> > Is the difficulty of creating a telephone type the reason it is not in
>> > postgresql already?
>> >
>> > Should the telephone type be able to do something such as:
>> >
>> > SELECT * from tableFOO where telephone.areacode = 555;
>> >
>> > Or would regex be better?
>>
>> makes more sense to store them in a a canonical format and then find
>> things with pattern matches.
>
> Also, due to the problem of keeping area codes segregated in large
> growing population centers, there is strong talk about allowing
> overlapping area codes.  Dialing locally will require 11 digits
> instead of the usual 7.

Ten digit dialing (where the country code is deemed implicit) has been
"de rigeur" in most of the highly populated metropolitan regions that
have 3 or more "area codes" for many years now.

> I know that this is already the case in the state of Georgia and
> there is talk about adopting it in California.

It has been true for a decade or more in Toronto and north Texas...
--
(format nil "~S@~S" "cbbrowne" "ntlug.org")
http://www.ntlug.org/~cbbrowne/
Signs  of a Klingon Programmer  -  13. "Our  users will  know fear and
cower before our software! Ship it! Ship it and let them flee like the
dogs they are!"

Re: US Telephone Number Type

From
Chris Browne
Date:
SCassidy@overlandstorage.com writes:
> In California, we definitely care about the area code, as there are several
> area codes (at least 4) in San Diego County.  I have to use 1+area code to
> dial home from work, and vice-versa.

In what way do you care about them?

The area code is NOT an accurate way of determining "locality," as
there are frequently cases where depending on where you are, different
sets of numbers roll in and out of one's "local calling area."

You can't necessarily determine, based on area code, whether another
number will be deemed "local" or not.  And that tendancy is getting
worse, over time.  Fortunately LD rates have been tending to fall...
--
output = reverse("gro.mca" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/languages.html
"Microsoft   builds   product  loyalty   on   the   part  of   network
administrators and consultants, [these are] the only people who really
count  in the  Microsoft scheme  of  things. Users  are an  expendable
commodity."  -- Mitch Stone 1997

Re: US Telephone Number Type

From
Tom Lane
Date:
"Karen Hill" <karen_hill22@yahoo.com> writes:
> I did a quick google and someone mentioned that input and output
> functions need to be written in C.  Is that still the case?

Yeah, pretty much.  The main problem is that such functions need to deal
with whatever physical on-disk format you've chosen for the datatype,
and most of our PLs don't offer bit-level memory access.

If C functions seem like more work than you want to go to for this
problem, I concur with using a domain over text with a regex check
constraint ...

            regards, tom lane

Re: US Telephone Number Type

From
Alvaro Herrera
Date:
Tom Lane wrote:
> "Karen Hill" <karen_hill22@yahoo.com> writes:
> > I did a quick google and someone mentioned that input and output
> > functions need to be written in C.  Is that still the case?
>
> Yeah, pretty much.  The main problem is that such functions need to deal
> with whatever physical on-disk format you've chosen for the datatype,
> and most of our PLs don't offer bit-level memory access.

Is bit-level memory access the actual problem?  I would think that at
least PL/Perl can offer that pretty easily using pack() or something
like that.  Not that I've actually tried using it.

But I think the main problem may be getting our calling conventions
right.  I mean, how would you do a PG_GETARG_BOOL() or stuff like that?
Maybe if we offered PG_GETARG_DATUM and PG_RETURN_DATUM equivalents in
PL/Perl we could offer I/O functions there.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: US Telephone Number Type

From
Martijn van Oosterhout
Date:
On Tue, Jul 11, 2006 at 01:27:49AM -0400, Alvaro Herrera wrote:
> But I think the main problem may be getting our calling conventions
> right.  I mean, how would you do a PG_GETARG_BOOL() or stuff like that?
> Maybe if we offered PG_GETARG_DATUM and PG_RETURN_DATUM equivalents in
> PL/Perl we could offer I/O functions there.

Not sure what you're getting at, type input/output functions are no
different than other functions.

Historically I beleive the issue was that languages couldn't handle the
"cstring" type because it was special. As of recent releases that's not
a problem anymore (though pl/pgsql still doesn't understand it for
example).

Another issue was that there was a special hack to create type
input/output functions because of the chicken/egg issue of type
creation. With explicit shell types this is fixed also (in -HEAD).

AIUI, pl/java can do it. For the others I just don't think people have
really tried...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

Re: US Telephone Number Type

From
"Merlin Moncure"
Date:
On 10 Jul 2006 10:33:52 -0700, Karen Hill <karen_hill22@yahoo.com> wrote:
> Hello,
>
> How would one go about creating a US telephone type in the format of
> "(555)-555-5555" ?  I am at a loss on how it could be accomplished in
> the most correct way possible while not going into the various
> different country styles e.g. +01 (555) 555-5555.
>
> Is the difficulty of creating a telephone type the reason it is not in
> postgresql already?

if it was me, i would keep a telephone type to simple text field.
while there is some merit to throwing a domain constraint on it,
history tells me this is more troulbe than it's worth :).

otoh, you could make a small immutable sql based regex function to
attempt to extract the area code or some other number from the text
field.  You could then index this if desired.

merlin

Re: US Telephone Number Type

From
Bruno Wolff III
Date:
On Mon, Jul 10, 2006 at 20:05:13 -0400,
  Chris Browne <cbbrowne@acm.org> wrote:
> worse, over time.  Fortunately LD rates have been tending to fall...

Unless you call a country where the local phone company is charging userous
rates andmay be giving kickbacks to people who can get people to call them
thinking that they will be charged their normal long distance rate because
the number looks like a "normal" (not international) phone number.

Re: US Telephone Number Type

From
Guy Fraser
Date:
On Mon, 2006-10-07 at 10:33 -0700, Karen Hill wrote:
> Hello,
>
> How would one go about creating a US telephone type in the format of
> "(555)-555-5555" ?  I am at a loss on how it could be accomplished in
> the most correct way possible while not going into the various
> different country styles e.g. +01 (555) 555-5555.
>
> Is the difficulty of creating a telephone type the reason it is not in
> postgresql already?
>
> Should the telephone type be able to do something such as:
>
> SELECT * from tableFOO where telephone.areacode = 555;
>
> Or would regex be better?
>
>
> regards,
>

Someone mentioned separating the NPA/NXX, but it is likely better
to store the phone number as formatted text.

"(123) 456-7890" -> "123-456-7890"

or if you need international/North America mixes try:

"1 (123) 456-7890" -> "1-123-456-7890"
"01 (555) 333-1212" -> "01-555-333-1212"

It is fairly simple to extract the Country Code/NPA/NXX/Line from
that format using split_part(ph,'-',#) where # is a digit from 1 to
4. It is also fairly simple to add an extension using a decimal
point, which can be extracted using split_part(ph,'.',2).

I normally determine the allowed number formats using data entry
filters in the front end, then store the data as a formatted
string :
ccc-npa-nxx-line.ext
Where ccc is the Country Code.

Depending on your needs you may want to store the raw number
and the formatted number separately.

In many jurisdictions it is now necessary to dial 10 digit
phone numbers so that should be the minimum used.

Although the NPA/NXX can be used in many cases to determine a
local, there are changes to these assignments on a regular basis
and access to that information is quite expensive. I looked
into accessing the data for a VOIP project I was working on
and was quite surprised when I discovered the access costs.
there can be other reasons why the data is unreliable as well
since many jurisdictions have legislated that phone companies
make there numbers available using LNP {Local Number Portability}
to other local phone providers. Using LNP and VOIP combinations
can allow someone to use their local phone number anywhere
in the world, just as they can with a Satellite phone.

Best of Luck