Re: [GENERAL] US Telephone Number Type
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- ? 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-.
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
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] US Telephone Number Type
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. signature.asc Description: Digital signature
Re: [GENERAL] US Telephone Number Type
On 10 Jul 2006 10:33:52 -0700, Karen Hill [EMAIL PROTECTED] wrote: Hello, How would one go about creating a US telephone type in the format of (555)-555- ? 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-. 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] US Telephone Number Type
On Mon, Jul 10, 2006 at 20:05:13 -0400, Chris Browne [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] US Telephone Number Type
Hello, How would one go about creating a US telephone type in the format of (555)-555- ? 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-. 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, ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] US Telephone Number Type
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- ? 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-. How do you mean styles. The above number is, when printed in standard international format +155. 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. signature.asc Description: Digital signature
Re: [GENERAL] US Telephone Number Type
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. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] US Telephone Number Type
Karen Hill [EMAIL PROTECTED] writes: Hello, How would one go about creating a US telephone type in the format of (555)-555- ? 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-. 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) -. 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] US Telephone Number Type
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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] US Telephone Number Type
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 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] US Telephone Number Type
Karen Hill [EMAIL PROTECTED] writes: How would one go about creating a US telephone type in the format of (555)-555- ? 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] US Telephone Number Type
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- ? 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-. How do you mean styles. The above number is, when printed in standard international format +155. 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] US Telephone Number Type
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 [EMAIL PROTECTED] To gKaren Hill [EMAIL PROTECTED] Sent by: cc pgsql-general-own pgsql-general@postgresql.org [EMAIL PROTECTED] Subject Re: [GENERAL] US Telephone Number Type 07/10/2006 11:07 AM |---| | [ ] Expand Groups | Please respond to|---| Martijn van Oosterhout [EMAIL PROTECTED] 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 -- signature.asc Description: Binary data ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] US Telephone Number Type
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] US Telephone Number Type
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- telephone-us-extention : (555) 555- ext 1234 Other locals (EU, etc) could create their own to their local specifications. This would seem like a nice contrib package. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] US Telephone Number Type
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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] US Telephone Number Type
-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- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] US Telephone Number Type
[EMAIL PROTECTED] (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 [EMAIL PROTECTED] 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! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] US Telephone Number Type
[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] US Telephone Number Type
Karen Hill [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] US Telephone Number Type
Tom Lane wrote: Karen Hill [EMAIL PROTECTED] 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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
