Thread: CHAR vs VARCHAR

CHAR vs VARCHAR

From
Derek Neighbors
Date:
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?

Derek Neighbors
GNU Enterprise
http://www.gnue.org
derek@gnue.org


Re: CHAR vs VARCHAR

From
Bruce Momjian
Date:
> 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

Re: CHAR vs VARCHAR

From
Alfonso Peniche
Date:
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