From: Mario Weilguni <mweilguni@sime.com>
To: Alexander Staubo <alex@purefiction.net>
Subject: Re: Optimization of this SQL sentence
Date: Wed, 18 Oct 2006 11:31:44 +0200
User-Agent: KMail/1.9.4
Cc: pgsql-performance@postgresql.org,
	Ruben Rubio <ruben@rentalia.com>
References: <45349CDF.3090002@rentalia.com>
	<200610171729.19596.mweilguni@sime.com>
	<EFB9AB5E-C089-4314-BD32-BCEDDFD3EA02@purefiction.net>
In-Reply-To: <EFB9AB5E-C089-4314-BD32-BCEDDFD3EA02@purefiction.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200610181131.44840.mweilguni@sime.com>

Am Dienstag, 17. Oktober 2006 17:50 schrieb Alexander Staubo:
> On Oct 17, 2006, at 17:29 , Mario Weilguni wrote:
> >
> > Enforcing length constraints with varchar(xyz) is good database
> > design, not a
> > bad one. Using text everywhere might be tempting because it works,
> > but it's
> > not a good idea.
>
> Enforcing length constraints is generally a bad idea because it
> assumes you know the data domain as expressed in a quantity of
> characters. Off the top of your head, do you know the maximum length
> of a zip code? A street address? The name of a city?

It's not a bad idea. Usually I use postal codes with 25 chars, and never had 
any problem. With text, the limit would be ~1 GB. No matter how much testing 
in the application happens, the varchar(25) as last resort is a good idea.

And in most cases, the application itself limits the length, and thus it's 
good to reflect this in the database design.

Feel free to use text anywhere for your application, and feel free to use 
numeric(1000) instead of numeric(4) if you want to be prepared for really 
long numbers, but don't tell other people it's bad database design - it 
isn't.