Thread: US Telephone Number Type
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,
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
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
"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>
> > 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.
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
"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
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
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
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
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.
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.
-----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-----
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!"
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
"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
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.
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
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
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.
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