pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dana Hudes <dhudes@tcp-ip.info>
To: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: User defined types -- Social Security number...
Date: Mon, 1 Mar 2004 11:54:01 -0500 (EST)
Message-ID: <Pine.LNX.4.58.0403011151340.17082@screamer.tcp-ip.info> (raw)
In-Reply-To: <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>
	<m37jy5ryto.fsf@wolfe.cbbrowne.com>

I would represent an SSN as numeric(9,0).
an int 32 would work though.
2**31 is > 999999999

On Sun, 29 Feb 2004, Christopher Browne wrote:

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



view thread (14+ messages)  latest in thread

Message-ID: <Pine.LNX.4.58.0403011151340.17082@screamer.tcp-ip.info>
Permalink:  ../Pine.LNX.4.58.0403011151340.17082@screamer.tcp-ip.info/
Also on:    postgresql.org/message-id/Pine.LNX.4.58.0403011151340.17082@screamer.tcp-ip.info

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: dhudes@tcp-ip.info, cbbrowne@acm.org
  Subject: Re: User defined types -- Social Security number...
  In-Reply-To: <Pine.LNX.4.58.0403011151340.17082@screamer.tcp-ip.info>
  References: <c16fu8$2ro4$1@news.hub.org> <200402221132.50311.josh@agliodbs.com> <c1bih1$1r8$1@news.hub.org> <20040301031716.GB4046@michaelchaney.com> <m37jy5ryto.fsf@wolfe.cbbrowne.com> <Pine.LNX.4.58.0403011151340.17082@screamer.tcp-ip.info>

* 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