From: Mario Weilguni <mweilguni@sime.com>
To: Alexander Staubo <alex@purefiction.net>
Cc: pgsql-performance@postgresql.org, Ruben Rubio <ruben@rentalia.com>
Subject: Re: Optimization of this SQL sentence
Date: Wed, 18 Oct 2006 11:31:44 +0200
Message-ID: <200610181131.44840.mweilguni@sime.com> (raw)
In-Reply-To: <EFB9AB5E-C089-4314-BD32-BCEDDFD3EA02@purefiction.net>
References: <45349CDF.3090002@rentalia.com>
<200610171729.19596.mweilguni@sime.com>
<EFB9AB5E-C089-4314-BD32-BCEDDFD3EA02@purefiction.net>
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.
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mweilguni@sime.com, alex@purefiction.net, ruben@rentalia.com
Subject: Re: Optimization of this SQL sentence
In-Reply-To: <200610181131.44840.mweilguni@sime.com>
* 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