"Karen Hill" <karen_hill22@xxxxxxxxx> 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@xxxxxxxxx>
