Skip site navigation (1) Skip section navigation (2)

Re: Difference between char and varchar

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Richmond(dot)Dyes(at)monroehosp(dot)org
Cc: Shane Ambler <pgsql(at)007Marketing(dot)com>, PostgreSQL Mailing lists <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Difference between char and varchar
Date: 2006-08-29 23:42:16
Message-ID: 200608292342.k7TNgGC04127@momjian.us (view raw or flat)
Thread:
Lists: pgsql-novice
> >  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
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

In response to

pgsql-novice by date

Next:From: Michael FuhrDate: 2006-08-30 01:02:20
Subject: Re: (FOR EACH STATEMENT AFTER UPDATE) Triggers & Transactions
Previous:From: Richard Broersma JrDate: 2006-08-29 23:14:49
Subject: (FOR EACH STATEMENT AFTER UPDATE) Triggers & Transactions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group