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

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!

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)

On 17 Oct 2002 at 9:24, paul butler wrote:
<snip>
> That way your future proofed, varchar without brackets is unlimited

Thanks a lot, I didn't know that.

> 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

I have considered this. As a matter of fact, that is the way it is in
our current db but I'm not really happy with it. Theoretically CODE
should never change and is therefore safe to use as primary key. But
having an "extra" serial primary key will make the db more flexible
regarding to unforeseen complications.

> Hope this helps

It did, thanks again.

On Thu, 2002-10-17 at 22:23, Jules Alberts wrote:
>
> I have considered this. As a matter of fact, that is the way it is in
> our current db but I'm not really happy with it. Theoretically CODE
> should never change and is therefore safe to use as primary key. But
> having an "extra" serial primary key will make the db more flexible
> regarding to unforeseen complications.

Yeah, this happens. Later people want to expire particular codes, or
change their meaning, but not for the existing records that refer to
them...

>From my own experience, I would also say that there is value in being
able to sequence the codes in a non-alphabetic order. I add another
"seq" column to such tables, to allow their ordering to be arbitrarily
adjusted as well.

Cheers,
Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Survey for free with http://survey.net.nz/
---------------------------------------------------------------------

Jules,

> 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)

On postgresql, there is no performance difference between CHAR and
VARCHAR. And a VARCHAR(25) which only has 1 character in each row is
no slower than a VARCHAR(2) that has only one character.

-Josh Berkus