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

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

From: "Greg Patnude" <gpatnude(at)hotmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: User defined types -- Social Security number...
Date: 2004-02-23 00:45:51
Message-ID: c1bih1$1r8$ (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-sql
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

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....

Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762

"Josh Berkus" <josh(at)agliodbs(dot)com> wrote in message
> Greg,
> > Anyone have a good pre-built user-defined type definition for creating /
> > maintaining / manipulating a SSN ... where valid chars are in the range
> > 000-00-0000 through 999-99-9999.
> Actually, the range is more narrowly defined than that.  I'm not sure of
> exact rules, but you will never see a leading 0 or a -00- in an SSN.
> > I imagine that the storage column is probably varchar(11) -- I am
> > for a type definition that
> Use DOMAINs, not a custom type.  It's less work.
> > Either that or the question is: How can I coerce postgreSQL into using
> > input / output "mask"...
> After you've created your DOMAIN, based on the TEXT type, you can overload
> input and output functions to format correctly.   Beware, though: input &
> output functions pretty much have to be written in C.
> -- 
> Josh Berkus
> Aglio Database Solutions
> San Francisco
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

In response to


pgsql-hackers by date

Next:From: Kevin BrownDate: 2004-02-23 00:59:34
Subject: Re: Too-many-files errors on OS X
Previous:From: Tom LaneDate: 2004-02-22 23:41:40
Subject: Re: [HACKERS] Mac OS X, PostgreSQL, PL/Tcl

pgsql-sql by date

Next:From: Sumita BiswasDate: 2004-02-23 02:54:17
Subject: Re: Postgres DB
Previous:From: Josh BerkusDate: 2004-02-22 19:34:47
Subject: Re: Compiling pl/pgsql functions

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