From: Chris Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: Optimization of this SQL sentence
Date: Tue, 17 Oct 2006 14:04:47 -0400
Message-ID: <601wp695ts.fsf@dba2.int.libertyrms.com> (raw)
References: <45349CDF.3090002@rentalia.com>
<4534A35E.9030909@rentalia.com>
<181E6D21-8326-4ED6-A9AE-6F9917C1C319@purefiction.net>
<200610171729.19596.mweilguni@sime.com>
<EFB9AB5E-C089-4314-BD32-BCEDDFD3EA02@purefiction.net>
alex@purefiction.net (Alexander Staubo) writes:
> On Oct 17, 2006, at 17:29 , Mario Weilguni wrote:
>
>> Am Dienstag, 17. Oktober 2006 11:52 schrieb Alexander Staubo:
>>> Lastly, note that in PostgreSQL these length declarations are not
>>> necessary:
>>>
>>> contacto varchar(255),
>>> fuente varchar(512),
>>> prefijopais varchar(10)
>>
>> 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?
In the case of a zip code? Sure. US zip codes are integer values
either 5 or 9 characters long.
In the case of some of our internal applications, we need to conform
to some IETF and ITU standards which actually do enforce some maximum
lengths on these sorts of things.
> In almost all cases the limit you invent is arbitrary, and the
> probability of being incompatible with any given input is inversely
> proportional to that arbitrary limit.
I'd be quite inclined to limit things like addresses to somewhat
smaller sizes than you might expect. If addresses are to be used to
generate labels for envelopes, for instance, it's reasonably important
to limit sizes to those that might fit on a label or an envelope.
> Encoding specific length constraints in the database makes sense
> when they relate explicitly to business logic, but I can think of
> only a few cases where it would make sense: restricting the length
> of passwords, user names, and so on. In a few cases you do know with
> 100% certainty the limit of your field, such as with standardized
> abbreviations: ISO 3166 country codes, for example. And sometimes
> you want to cap data due to storage or transmission costs.
There's another reason: Open things up wide, and some people will fill
the space with rubbish.
--
"cbbrowne","@","acm.org"
http://linuxfinances.info/info/internet.html
"The Amiga is proof that if you build a better mousetrap, the rats
will gang up on you." -- Bill Roberts bill.roberts@ensco.com
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: cbbrowne@acm.org
Subject: Re: Optimization of this SQL sentence
In-Reply-To: <601wp695ts.fsf@dba2.int.libertyrms.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