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

char or int vs varchar

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: char or int vs varchar
Date: 2005-04-22 20:20:51
Message-ID: 20050422200053.M57618@narrowpathinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi All,

I am building a new table and I would appreciate your thoughts on a data
storage and performance question

I need to store a (at the moment) tristate variable.  Now this could be done
as a char "L", "S", "T" as an int2 1, 2, 3 or as a varchar(8) "loose",
"standard", "tight" among possibly other ways.

For absolute storage minimum char would be the best at 1 byte but the
documentation indicates that it is an internal type so I do not know whether
or not I can really use it.  If not then character(1) would have to be used
and it looks like that would use up 5 bytes.

For readability varchar(8) obviously wins.  The documentation assures me that
other than using 12 bytes there is "no performance differences between these
three types".

Finally there is the smallint at 2 bytes.  Almost the smallest in terms of
disk usage I wonder how it will affect performance since in most cases I would
probably have to use a CASE statement to convert to something a human would
intuitively understand.

Kind Regards,
Keith

Responses

pgsql-novice by date

Next:From: ruel.cimaDate: 2005-04-22 20:38:29
Subject: SSL use
Previous:From: Keith WorthingtonDate: 2005-04-22 19:29:33
Subject: CHECK vs BEFORE trigger

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