Thread: Difference between char and varchar
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?
On Mon, Aug 28, 2006 at 15:04:26 -0400, Richmond Dyes <rdyes@monroehosp.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? You normally want to use 'text' instead of either 'char' or 'varchar'. Unless there is a business rule limiting the length of a string (and sometimes not even then) you want to use 'text' as it is more flexible.
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@monroehosp.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@postgresql.org so that your > message can get through to the mailing list cleanly >
Bruno Wolff III a écrit : > On Mon, Aug 28, 2006 at 15:04:26 -0400, > Richmond Dyes <rdyes@monroehosp.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? >> > > You normally want to use 'text' instead of either 'char' or 'varchar'. > Unless there is a business rule limiting the length of a string (and sometimes > not even then) you want to use 'text' as it is more flexible. > But it is not 'standard' sql, so it won't be portable to other databases, if needed.
On Tue, Aug 29, 2006 at 12:22:07 +0200, Franck Routier <franck.routier@axege.com> wrote: > Bruno Wolff III a écrit : > >On Mon, Aug 28, 2006 at 15:04:26 -0400, > > Richmond Dyes <rdyes@monroehosp.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? > >> > > > >You normally want to use 'text' instead of either 'char' or 'varchar'. > >Unless there is a business rule limiting the length of a string (and > >sometimes > >not even then) you want to use 'text' as it is more flexible. > > > But it is not 'standard' sql, so it won't be portable to other > databases, if needed. I didn't think of that until after I sent the message. I should have listed portability as a reason to user char or varchar.
How about issues with queries? I have seen where using varchar
slowsdown queries. Is this true?
<blockquote cite="midC11A06F4.4B7B8%25pgsql@007Marketing.com"
type="cite">
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@monroehosp.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@postgresql.org so that your
message can get through to the mailing list cleanly
> > 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@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +