From: Christopher Browne <cbbrowne@acm.org>
Subject: Re: User defined types -- Social Security number...
Date: Sun, 29 Feb 2004 23:11:31 -0500
Organization: cbbrowne Computing Inc
Lines: 47
Message-ID: <m37jy5ryto.fsf@wolfe.cbbrowne.com>
References: <c16fu8$2ro4$1@news.hub.org>
 <200402221132.50311.josh@agliodbs.com>
	<c1bih1$1r8$1@news.hub.org>
	<20040301031716.GB4046@michaelchaney.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Security Through
 Obscurity,
	linux)
Cancel-Lock: sha1:GTblkscCnK7GnJnqYD5x/ECSmLc=
To: pgsql-sql@postgresql.org

The world rejoiced as mdchaney@michaelchaney.com (Michael Chaney) wrote:
> Look, you're thinking way too hard on this.  An SSN is a 9-digit number,
> nothing more.  There are some 9-digit numbers which aren't valid SSN's,
> and you might want to get fancy and create a constraint for that.
>
> Regardless, you are making a *major* mistake of confusing data
> storage with rendering.  It is common to *render* an SSN as
> xxx-xx-xxxx and its cousin the FETID (Federal Employers Tax ID) as
> xx-xxxxxxx.  To store the dashes makes no sense.  They're in the
> same place each time, it's wasted data.
>
> Store the SSN as an "integer".  When you begin to think about this
> correctly, the "leading zeros" problem disappears since that is also a
> *rendering* issue.

Well put.

The one thing that is a bit unfortunate is that 32 bit ints aren't
quite big enough for this.  You need 1 extra digit :-(.

> When you pull the data out, either fix it up in your programming
> language to the format that you wish, or use the to_char function as
> shown above in your select statements.

Using a view to hide the "physical" representation is also an idea.

A full scale type definition could make for an even more efficient
approach that makes the implementation appear invisible.

> To help you think about this whole issue, consider the timestamp
> datatype.  Timestamps are stored as a Julian date internally.  I
> suspect that they use a double-floating point as the actual format,
> but regardless the point is that it's a number.  Rather than storing

Actually, it's an "int64"; a 64 bit integer, on platforms that support
that type.  It's a "double" only on platforms that do not support that
type.

> It's easier to use that as a basic format from which we can render
> it in any way we wish.

Indeed.
-- 
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://cbbrowne.com/info/spiritual.html
"I owe the government $3400 in  taxes.  So I sent them two hammers and
a toilet seat."  -- Michael McShane