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

char() or varchar() for frequently used column

From: "Jules Alberts" <jules(dot)alberts(at)arbodienst-limburg(dot)nl>
To: pgsql-novice(at)postgresql(dot)org
Subject: char() or varchar() for frequently used column
Date: 2002-10-17 07:23:14
Message-ID: 200210170724.g9H7OQTY018412@artemis.cuci.nl (view raw or flat)
Thread:
Lists: pgsql-novice
Hello everyone,

A db I'm designing will have a lot of tables with codes in them, like

create table country (
	id serial primary key,
	code char(2) not null unique,
	name varchar(100) not null unique);
insert into country (code, name) values ('NL', 'Nederland');
insert into country (code, name) values ('BE', 'Belgie');
-- etc

create table gender (
	id serial primary key,
	code char(1) not null unique,
	name varchar(100) not null unique);
insert into gender (code, name) values ('M', 'male');
insert into gender (code, name) values ('F', 'female');

The 'code' columns will be used as foreign keys in other tables. My 
question is about the datatype and length of the 'code' columns. 
Allthough the codes length will probably not change over the years, it 
might happen anyway. I've seen this before and had some headaches over 
it. So in the new db I want to be prepared and make the referenced 
columns longer than would appear necessary at first sight. So instead 
of "code char(2)" I plan to do "code varchar(25)". The idea is that I:

- give myself space for future and unforeseeable change of the length
- don't waste space by using varchar() instead of char()

Are there any flaws in this approach? Will I get in trouble when using 
indexes. Will performance be hampered severely? (we're not talking 
about huge amounts of transactions)

Thanks for any insight!

Responses

pgsql-novice by date

Next:From: paul butlerDate: 2002-10-17 08:24:06
Subject: Re: char() or varchar() for frequently used column
Previous:From: Jules AlbertsDate: 2002-10-17 06:11:09
Subject: Re: db design question

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