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

In the last exciting episode, "Greg Patnude" <gpatnude(at)hotmail(dot)com> 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"...
>
> I hadn't considered using a Domain.... have to look at that....

Strongly recommended; that allows applying the validation in many
places without having to repeat validation "code."

If you will be using really a lot of these values, and indexing on
them, it even may be worth looking at a custom type.

A performance "win" would come in using a compact data type. For
instance, for 9 digit national ID numbers, you can do a LOT better
than an 11 byte string. (Aside: Anything bigger than 34 bits would
do, demonstrating that it is a regrettable loss that 36 bit computer
systems went the way of the dodo...)
--
output = reverse("gro.gultn" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/internet.html
I found out why cats drink out of the toilet. My mother told me it's
because it's cold in there. And I'm like: How did my mother know THAT?
--Wendy Liebman