From: scott.marlowe <scott.marlowe@ihs.com>
To: Steve Crawford <scrawford@pinpointresearch.com>
Cc: Bruce Young <hbrucey@yahoo.com>
Cc: PostgresPHP <pgsql-php@postgresql.org>
Subject: Re: updating integer column
Date: Mon, 24 Feb 2003 17:49:38 -0700 (MST)
Message-ID: <Pine.LNX.4.33.0302241748390.14765-100000@css120.ihs.com> (raw)
In-Reply-To: <20030225002301.A02BA103C2@polaris.pinpointresearch.com>
What sucks is when you go through all the hoops to do this to make sure
your data will be nice and pretty, and the day after you put it online,
you have to rip it out to accomodate international numbers.
Not that anything like that's ever happened to me. Oh no, definitely not.
:-)
On Mon, 24 Feb 2003, Steve Crawford wrote:
> True comments, all - and we haven't even gotten into the problem of telephone
> extensions. But the original question was about 10 digit numbers so I assumed
> vanilla US area+prefix+number.
>
> Cheers,
> Steve
>
> On Monday 24 February 2003 11:33 am, scott.marlowe wrote:
> > 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.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: scott.marlowe@ihs.com, scrawford@pinpointresearch.com, hbrucey@yahoo.com
Subject: Re: updating integer column
In-Reply-To: <Pine.LNX.4.33.0302241748390.14765-100000@css120.ihs.com>
References: <20030225002301.A02BA103C2@polaris.pinpointresearch.com> <Pine.LNX.4.33.0302241748390.14765-100000@css120.ihs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox