Skip site navigation (1) Skip section navigation (2)

Re: updating integer column

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
Cc: Bruce Young <hbrucey(at)yahoo(dot)com>,PostgresPHP <pgsql-php(at)postgresql(dot)org>
Subject: Re: updating integer column
Date: 2003-02-24 19:33:24
Message-ID: Pine.LNX.4.33.0302241228590.14019-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-php
On Mon, 24 Feb 2003, Steve Crawford wrote:

> > > oh and what is the best datatype to use for a 10 digit phone number?.
> -snip-
> > Secondly, for a phone number, ask yourself how you're going to treat it.
> > Are you going to do a sum() across the numbers?  Or maybe multiply them
> > together?
> >
> > If yes, then you should store them as some kind of numeric, int, or as a
> > float.
> >
> > If, however, the numbers are not going to be used for math but for
> > identification, then it is likely that a text / varchar type would be a
> > better choice.
> 
> Don't use int:
> create table foo (ph int);
> insert into foo values (5105551212);
> ERROR:  dtoi4: integer out of range
> 
> Use char(10). 

Actually, I'd use text or something, because I store international 
and US phone numbers.  Some are easily 15 or more characters long.

> Better yet, "properly" normalize phone numbers into area-code (char(3)), 
> prefix (char(3)) and number (char(4)) fields. This way you can error-check 
> your phones against the area-code table, determine approximate geographical 
> areas/time-zones, flag dangerous numbers (very high cost off-shore versions 
> of 900/976 numbers that look like ordinary phone numbers), etc.

Yes, but then absolutely nothing but US phone numbers will fit.  While 
that's a great idea if all you're storing are US numbers, it doesn't fit 
all models.  I don't think it's possible to come up with a regex that 
will qualify all the goofy phone numbers my company's database stores.

> If you really want to you can even include a prefix table to do the same 
> thing at the exchange level using NANPA data.

We actually do something similar.  On campus we have 4 digit numbers, but 
we have four different prefixes depending on range.  i.e. prefix 123 is 
used for say 0001 through 2999, while prefix 456 is used on 3000 through 
4499, then 879 for 4500 through 7999 and so on.  So we join them based on 
range.  Works pretty well, but it's ugly.


In response to

Responses

pgsql-php by date

Next:From: Steve CrawfordDate: 2003-02-25 00:23:01
Subject: Re: updating integer column
Previous:From: Steve CrawfordDate: 2003-02-24 19:27:04
Subject: Re: updating integer column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group