Skip site navigation (1) Skip section navigation (2)

Re: Optimization of this SQL sentence

From: Mario Weilguni <mweilguni(at)sime(dot)com>
To: Alexander Staubo <alex(at)purefiction(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org,Ruben Rubio <ruben(at)rentalia(dot)com>
Subject: Re: Optimization of this SQL sentence
Date: 2006-10-18 09:31:44
Message-ID: 200610181131.44840.mweilguni@sime.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.






In response to

Responses

pgsql-performance by date

Next:From: Rohit_BehlDate: 2006-10-18 10:10:01
Subject: Re: Jdbc/postgres performance
Previous:From: Ruben RubioDate: 2006-10-18 07:06:20
Subject: Re: Optimization of this SQL sentence

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group