From: Greg Patnude <gpatnude@hotmail.com>
To: " <pgsql-hackers@postgresql.org.pgsql-sql>
Subject: Re: User defined types -- Social Security number...
Date: Tue, 2 Mar 2004 08:23:16 -0800
Message-ID: <c22ceg$s1a$1@news.hub.org> (raw)
References: <c16fu8$2ro4$1@news.hub.org>
<m37jy5ryto.fsf@wolfe.cbbrowne.com>
<Pine.LNX.4.58.0403011151340.17082@screamer.tcp-ip.info>
<200403010942.48760.scrawford@pinpointresearch.com>
<20040301211843.GB19105@michaelchaney.com>
"Michael Chaney" <mdchaney@michaelchaney.com> wrote in message
news:20040301211843.GB19105@michaelchaney.com...
> 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/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
Ther are some other points I'd like to make --
If I store the SSN as an integer -- theoretically -- leading zeroes will be
stripped (041-99-9999) -- my OWN ssn is a perfect example of this as it
starts with a leading zero...
This would cause a problem in that one of the requirements of an SSN is that
the length be exactly 9 digits or 9 chars WITHOUT the dashes.... so a CHECK
CONSTRAINT would be useful...
But if the SSN is stored as an integer -- there is no check constraint that
wouldn't fail for SSNs that start with one or more zeroes.... So I thought
how about a varchar(9) field and insert/update triggers that do the
formatting (adding the dashes on insert/update --) and validate the check
contraints (9 chars + the dashes)...
The two extra characters making a varchar(11) field are not a concern in the
normalization or schema... I simply wanted a formatting function so that I
dont have to do it in my scripting language or use the same CAST over and
over and over in my select/insert/update statements....
I am mainly looking to do the formatting automatically rather than having to
constantly format such a simple piece of data...
It would be really sweet in postgreSQL if we could apply the equivalent of a
printf(columnname) to the table definition -- MS Access has what they call
an "input mask" and it comes in really handy -- however -- I havent used
Access for anthing serious for about 4 years...
--
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gpatnude@hotmail.com, pgsql-hackers@postgresql.org.pgsql-sql
Subject: Re: User defined types -- Social Security number...
In-Reply-To: <c22ceg$s1a$1@news.hub.org>
* 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