Re: char() or varchar() for frequently used column - Mailing list pgsql-novice

From:               "Jules Alberts" <jules.alberts@arbodienst-limburg.nl>
Organization:       ARBOdienst Limburg BV
To:                 pgsql-novice@postgresql.org
Date sent:          Thu, 17 Oct 2002 09:23:14 +0200
Subject:            [NOVICE] char() or varchar() for frequently used column
Send reply to:      jules.alberts@arbodienst-limburg.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@postgresql.org)