Re: User defined types -- Social Security number... - Mailing list pgsql-sql

From Christopher Browne
Subject Re: User defined types -- Social Security number...
Date
Msg-id m33c92o2vi.fsf@wolfe.cbbrowne.com
Whole thread Raw
In response to User defined types -- Social Security number...  ("Greg Patnude" <gpatnude@hotmail.com>)
List pgsql-sql
In the last exciting episode, "Greg Patnude" <gpatnude@hotmail.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


pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: Postgres DB
Next
From: "George A.J"
Date:
Subject: Enterprice support in PostgreSQL