From: Michael Chaney <mdchaney@michaelchaney.com>
To: pgsql-sql@postgresql.org
Subject: Re: User defined types -- Social Security number...
Date: Sun, 29 Feb 2004 21:17:16 -0600
Message-ID: <20040301031716.GB4046@michaelchaney.com> (raw)
In-Reply-To: <c1bih1$1r8$1@news.hub.org>
References: <c16fu8$2ro4$1@news.hub.org>
<200402221132.50311.josh@agliodbs.com>
<c1bih1$1r8$1@news.hub.org>
On Sun, Feb 22, 2004 at 04:45:51PM -0800, Greg Patnude wrote:
> Thanks Josh -- I understand that there are valid and invalid SSN's --
> similar rules apply to zip codes and area codes...
>
> I tried this:
>
> SELECT to_char(123456789, '000-00-0000');
> which yields 123-45-6789 -- nicely, I might add...
>
> the trick is getting postgreSQL to do this without having to create an
> ON
> SELECT and ON UPDATE TRIGGER...
>
> an ON UPDATE, SELECT, and INSERT re-write RULE might do the trick...
>
> SSN's CAN in fact start with a leading 0 -- mine does -- "041-xx-xxxx"
> --
>
> I do agree that there are valid ranges -- my main concern is being
> able to
> store any leading zeros - I just need to make sure that something
> "looks"
> like a valid SSN in the formattig
> (nnn-nn-nnnn) and that I can store / retrieve it with the approoriate
> format -- what I am really trying to accomplish is an "input mask"...
Look, you're thinking way too hard on this. An SSN is a 9-digit number,
nothing more. There are some 9-digit numbers which aren't valid SSN's,
and you might want to get fancy and create a constraint for that.
Regardless, you are making a *major* mistake of confusing data storage
with
rendering. It is common to *render* an SSN as xxx-xx-xxxx and its
cousin the FETID (Federal Employers Tax ID) as xx-xxxxxxx. To store the
dashes makes no sense. They're in the same place each time, it's wasted
data.
Store the SSN as an "integer". When you begin to think about this
correctly, the "leading zeros" problem disappears since that is also a
*rendering* issue.
When you pull the data out, either fix it up in your programming
language to the format that you wish, or use the to_char function as
shown above in your select statements.
To help you think about this whole issue, consider the timestamp
datatype. Timestamps are stored as a Julian date internally. I suspect
that they use a double-floating point as the actual format, but
regardless the point is that it's a number. Rather than storing
2004-02-29 21:14:27.030434-06
We store:
2453065.88503472
It's easier to use that as a basic format from which we can render it in
any way we wish.
The same applies to your SSN.
Michael
--
Michael Darrin Chaney
mdchaney@michaelchaney.com
http://www.michaelchaney.com/
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: mdchaney@michaelchaney.com
Subject: Re: User defined types -- Social Security number...
In-Reply-To: <20040301031716.GB4046@michaelchaney.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