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

Re: char() or varchar() for frequently used column

From: "paul butler" <paul(at)entropia(dot)co(dot)uk>
To: "Jules Alberts" <jules(dot)alberts(at)arbodienst-limburg(dot)nl>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: char() or varchar() for frequently used column
Date: 2002-10-17 08:24:06
Message-ID: T5dfe11b238ac1785b30c3@pcow057o.blueyonder.co.uk (view raw or flat)
Thread:
Lists: pgsql-novice
From:           	"Jules Alberts" <jules(dot)alberts(at)arbodienst-limburg(dot)nl>
Organization:   	ARBOdienst Limburg BV
To:             	pgsql-novice(at)postgresql(dot)org
Date sent:      	Thu, 17 Oct 2002 09:23:14 +0200
Subject:        	[NOVICE] char() or varchar() for frequently used column
Send reply to:  	jules(dot)alberts(at)arbodienst-limburg(dot)nl

From the online docs:

There are no performance differences between these three types, apart from 
the increased storage size when using the blank-padded type. (character)

So I would suggest
 create table country (
 	id serial primary key,
	code varchar not null unique,
	name varchar not null unique);
insert into country (code, name) values ('NL', 'Nederland');
 insert into country (code, name) values ('BE', 'Belgie');

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

That way your future proofed, varchar without brackets is unlimited
and while I know you didn't ask

 create table country (
	code varchar primary key,
	name varchar not null unique;
insert into country (code, name) values ('NL', 'Nederland');
 insert into country (code, name) values ('BE', 'Belgie');

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

might serve just as well
Hope this helps

Paul Butler


> 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!
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)



In response to

Responses

pgsql-novice by date

Next:From: Jules AlbertsDate: 2002-10-17 09:23:30
Subject: Re: char() or varchar() for frequently used column
Previous:From: Jules AlbertsDate: 2002-10-17 07:23:14
Subject: char() or varchar() for frequently used column

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