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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Jules Alberts | 2002-10-17 09:23:30 | Re: char() or varchar() for frequently used column |
Previous Message | Jules Alberts | 2002-10-17 07:23:14 | char() or varchar() for frequently used column |