From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: User defined types -- Social Security number...
Date: Sun, 22 Feb 2004 23:07:13 -0500
Message-ID: <m33c92o2vi.fsf@wolfe.cbbrowne.com> (raw)
References: <c16fu8$2ro4$1@news.hub.org>
<200402221132.50311.josh@agliodbs.com>
<c1bih1$1r8$1@news.hub.org>
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
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: cbbrowne@acm.org
Subject: Re: User defined types -- Social Security number...
In-Reply-To: <m33c92o2vi.fsf@wolfe.cbbrowne.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