Re: User defined types -- Social Security number...

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(at)michaelchaney(dot)com
http://www.michaelchaney.com/