I could give an insight on what Informix says about the difference between these
data types, I assume it applies to Postgres as well.
As the FAQ and mailing lists say, the CHAR datatype adds blank spaces to fill the
reserved space.... advantage: is faster when the data changes constantly; VARCHAR
uses less space..... advantage, is faster on insertions and searches involving
such field.
Personally, I use VARCHAR on data I know is not going to change or that may change
a bit and speed is not important (the overhead is not significant), mainly
catalogs, names, and so on. I use CHAR for small data fields (no more than 5, 10
tops) or data I know changes constantly. Of course it's a matter of circumstances
and criteria, but it has worked for me.
One thing I liked about Informix is that you could define a maximum length and a
fixed length, I'll explain myself:
VARCHAR(50,20), means there's a maximum length of 50 for the specified field, but
I'd like to reserve a minimum of 20 (as in CHAR(20)) this speeds up a bit more any
process, because the reserved space equals about 75 or even 80% of the type of
information that will be stored (I wish this could be implemented later on with
Postgres).
Hope this helps. Any question, don't hesitate to ask.
Cheers
Bruce Momjian wrote:
> > I browsed the FAQ and mailing list briefly to try to garner information
> > about the ups and downs of different character based datatypes. I did not
> > see any good document on preference or when to use one over the other.
> >
> > http://postgresql.readysetnet.com/docs/faq-english.html#4.15 noted that
> > some compression happens.
> >
> > http://postgresql.readysetnet.com/users-lounge/docs/7.0/user/datatype1066.htm
> > gives a good description and notes that text is best choice, but it not
> > being SQL compliant is an issue for most.
> >
> > The question is how much of speed difference is there between CHAR and
> > VARCHAR when doing searches etc? or are there any good rules others use
> > to determine when CHAR is better to use than VARCHAR or vice versa?
>
> FAQ item is accurate that compression happens on all variable length
> types.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
