> > How about issues with queries? I have seen where using varchar slowsdown queries. Is this true?
> Performance wise it doesn't matter which you use (according to the
> docs) some db's have performance differences between char, varchar and
> text - postgresql doesn't.
> The choice would mainly depend on what data will be stored and what
> considerations for disk usage you may have. You really only need to
> use char or varchar if you want to limit the amount of data stored,
> although it is considered better practice to use data types that closely
> match the data to be stored. Meaning if you want to store 10-20
> characters use a char(20) not a text field even if it makes no
> difference in the end.
> char and varchar can technically store up to 1GB of text but best/common
> practice is to only use char or varchar for up to about 200 characters
> and text for anything above that.
> eg A char(100) will always store 100 characters even if you only enter
> 5, the remaining 95 chars will be padded with spaces. Storing 5
> characters in a varchar(100) will save 5 characters.
> If this is the main table and you have say 10 char fields and expect
> 200,000 records it will add up to a lot of extra disk usage.
> Of course there is also some overhead to identify/find the data in the
> disk file etc.
> So if you want to allow up to 50 characters and you know that maybe
> 20% could be as little as 5 characters with an average around 30 then
> char(50) would use more disk space than a varchar(50). If you don't
> want to restrict the length entered and it may possibly be lengthier
> then you may want to use a text field instead.
There is an FAQ entry about this. Does it need more information?
Bruce Momjian bruce(at)momjian(dot)us
+ If your life is a hard drive, Christ can be your backup. +
In response to
pgsql-novice by date
|Next:||From: Michael Fuhr||Date: 2006-08-30 01:02:20|
|Subject: Re: (FOR EACH STATEMENT AFTER UPDATE) Triggers & Transactions|
|Previous:||From: Richard Broersma Jr||Date: 2006-08-29 23:14:49|
|Subject: (FOR EACH STATEMENT AFTER UPDATE) Triggers & Transactions|