Re: Optimization of this SQL sentence - Mailing list pgsql-performance

From Mark Kirkwood
Subject Re: Optimization of this SQL sentence
Date
Msg-id 4535FFC0.603@paradise.net.nz
Whole thread Raw
In response to Re: Optimization of this SQL sentence  (Mario Weilguni <mweilguni@sime.com>)
List pgsql-performance
Mario Weilguni wrote:

>>
>> Â  Â contacto varchar(255),
>> Â  Â fuente varchar(512),
>> Â  Â prefijopais varchar(10)
>>
>> Instead, use:
>>
>> Â  Â contacto text,
>> Â  Â fuente text,
>> Â  Â prefijopais text
>>
>> See the PostgreSQL manual for an explanation of varchar vs. text.
>
> 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.
>

I've always used the rationale:

If you *know* that the data is length constrained, then it is ok to
reflect this in the domain you use - err, thats why they have length
limits! e.g. if you know that 'prefijopais' can *never* be > 10 chars in
length, then varchar(10) is a good choice.

If the data length is unknown or known to be unlimited, then reflect
that in the domain you use - e.g if 'fuente' or 'contacto' have no
reason to be constrained, then just use text.

best wishes

Mark


pgsql-performance by date:

Previous
From: Mario Weilguni
Date:
Subject: Re: Optimization of this SQL sentence
Next
From: Arnaud Lesauvage
Date:
Subject: Index on two columns not used