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

Re: Difference between char and varchar

From: Richmond Dyes <rdyes(at)monroehosp(dot)org>
To: Shane Ambler <pgsql(at)007Marketing(dot)com>
Cc: PostgreSQL Mailing lists <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Difference between char and varchar
Date: 2006-08-29 17:57:46
Message-ID: 44F4801A.6020305@monroehosp.org (view raw or flat)
Thread:
Lists: pgsql-novice
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
How about issues with queries?&nbsp; I have seen where using varchar
slowsdown queries.&nbsp; Is this true?<br>
<blockquote cite="midC11A06F4(dot)4B7B8%25pgsql(at)007Marketing(dot)com"
 type="cite">
  <pre wrap="">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" <a class="moz-txt-link-rfc2396E" href="mailto:rdyes(at)monroehosp(dot)org">&lt;rdyes(at)monroehosp(dot)org&gt;</a> wrote:

  </pre>
  <blockquote type="cite">
    <pre wrap="">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 <a class="moz-txt-link-abbreviated" href="mailto:majordomo(at)postgresql(dot)org">majordomo(at)postgresql(dot)org</a> so that your
     message can get through to the mailing list cleanly

    </pre>
  </blockquote>
  <pre wrap=""><!---->

  </pre>
</blockquote>
<br>
</body>
</html>



Attachment: unknown_filename
Description: text/html (2.7 KB)

In response to

Responses

pgsql-novice by date

Next:From: Andrej Ricnik-BayDate: 2006-08-29 18:12:35
Subject: Re: [GENERAL] Shared Objects (Dynamic loading)
Previous:From: Jasbinder BaliDate: 2006-08-29 17:29:28
Subject: Re: Shared Objects (Dynamic loading)

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