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 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.
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.
The length constraint on text fields is primarily a historical
artifact stemming from the way databases have traditionally been
implemented, as fixed-length fields in fixed-length row structures.
The inexplicable, improbable space-padded (!) "character" data type
in ANSI SQL is a vestige of this legacy. PostgreSQL's variable-length
rows and TOAST mechanism makes the point moot.
Quoth the PostgreSQL manual, section 8.3:
> There are no performance differences between these three types,
> apart from the increased storage size when using the blank-padded
> type. While character(n) has performance advantages in some other
> database systems, it has no such advantages in PostgreSQL. In most
> situations text or character varying should be used instead.
Alexander.