X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2D3DBD1E95D for ; Mon, 1 Mar 2004 21:16:55 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 03604-08 for ; Mon, 1 Mar 2004 17:16:53 -0400 (AST) Received: from mail.michaelchaney.com (mail.michaelchaney.com [207.65.84.68]) by svr1.postgresql.org (Postfix) with ESMTP id 49655D1E923 for ; Mon, 1 Mar 2004 17:16:51 -0400 (AST) Received: from pcp08698233pcs.500ash01.tn.comcast.net (fractal.trailer) [69.137.114.17] by mail.michaelchaney.com with esmtp (Exim 4.20 #1 (FreeBSD)) id 1Axuj8-0007Cc-QC; Mon, 01 Mar 2004 15:13:26 -0600 Received: from mdchaney by fractal.trailer with local (Exim 3.35 #1 (Debian)) id 1AxuoF-0004yY-00 for ; Mon, 01 Mar 2004 15:18:43 -0600 Date: Mon, 1 Mar 2004 15:18:43 -0600 From: Michael Chaney To: pgsql-sql@postgresql.org Subject: Re: User defined types -- Social Security number... Message-ID: <20040301211843.GB19105@michaelchaney.com> References: <200403010942.48760.scrawford@pinpointresearch.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200403010942.48760.scrawford@pinpointresearch.com> User-Agent: Mutt/1.3.28i Organization: Michael Chaney Consulting Corporation X-URL: http://www.michaelchaney.com/ X-MailScanner-Information: Please see http://www.michaelchaney.com/mail/spam.php for more information X-MailScanner: Found to be clean X-MailScanner-SpamCheck: not spam, SpamAssassin (score=1, required 5, IN_REP_TO, SOCIAL_SEC_NUMBER, DOUBLE_CAPSWORD) X-MailScanner-SpamScore: s X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/10 X-Sequence-Number: 17109 On Mon, Mar 01, 2004 at 09:42:48AM -0800, Steve Crawford wrote: > I missed the start of this thread but will chime in with a comment > anyway. > > My rule is to select an appropriate numeric type of data if you will > be doing numeric types of things to it, character types if you will > be doing character manipulations, etc. > > I don't know of any good reasons to need to know SSN/6.9, sqrt(SSN), > SSN+7.86 but there are plenty of good reasons to need the first three > characters (the "area number"), the middle two characters (the "group > number", and the last 4 characters (the "serial number", often > (ab)used as a password for banking and other purposes). Another excellent point. I often store zip codes as text for this reason. The only other thing that I would mention is that if the SSN field in the db will be a key of some sort, which is often the case, then it might be more efficient to store it as an integer. It might be more efficient to store it as a character string. The author should test in this case to determine the most efficient way. As for character vs. integer manipulations, in most scripting style languages, which is pretty much exlusively what I use, there's no need to think about types, and something like an SSN will silently change between being character or integer depending on what operations are being performed on it. Michael -- Michael Darrin Chaney mdchaney@michaelchaney.com http://www.michaelchaney.com/