Re: Difference between char and varchar

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.

On 29/8/2006 4:34, "Richmond Dyes" <rdyes(at)monroehosp(dot)org> wrote:

> I know the difference between char and varchar is char is fixed length
> and varchar returns variable length. Which one should be used and why?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>