X-Original-To: pgsql-php@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A4F25475FDD for ; Mon, 24 Feb 2003 22:50:45 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 71569-01 for ; Mon, 24 Feb 2003 22:50:17 -0500 (EST) Received: from polaris.pinpointresearch.com (66-7-238-176.cust.telepacific.net [66.7.238.176]) by postgresql.org (Postfix) with ESMTP id 04DBA4760F0 for ; Mon, 24 Feb 2003 19:22:59 -0500 (EST) Received: from there (66-7-238-179.cust.telepacific.net [66.7.238.179]) by polaris.pinpointresearch.com (Postfix) with SMTP id A02BA103C2; Mon, 24 Feb 2003 16:23:01 -0800 (PST) Content-Type: text/plain; charset="iso-8859-1" From: Steve Crawford Organization: Pinpoint Research To: "scott.marlowe" Subject: Re: updating integer column Date: Mon, 24 Feb 2003 16:23:01 -0800 X-Mailer: KMail [version 1.3.1] Cc: Bruce Young , PostgresPHP References: In-Reply-To: MIME-Version: 1.0 Message-Id: <20030225002301.A02BA103C2@polaris.pinpointresearch.com> Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/52 X-Sequence-Number: 1903 True comments, all - and we haven't even gotten into the problem of telepho= ne=20 extensions. But the original question was about 10 digit numbers so I assum= ed=20 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