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

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)