Skip site navigation (1) Skip section navigation (2)

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

From: Christopher Browne <cbbrowne(at)acm(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: User defined types -- Social Security number...
Date: 2004-02-23 04:07:13
Message-ID: m33c92o2vi.fsf@wolfe.cbbrowne.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
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

In response to

pgsql-hackers by date

Next:From: Joe ConwayDate: 2004-02-23 05:28:02
Subject: Re: Too-many-files errors on OS X
Previous:From: Claudio NatoliDate: 2004-02-23 04:04:34
Subject: Re: Too-many-files errors on OS X

pgsql-sql by date

Next:From: Sumita BiswasDate: 2004-02-23 06:21:16
Subject: Input Arguments
Previous:From: Sumita BiswasDate: 2004-02-23 03:33:41
Subject: Re: Error Number in Function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group