Re: when to use char, varchar or text

From: "Adam Erickson" <adamre(at)cox(dot)net>
To: "Rory Campbell-Lange" <rory(at)campbell-lange(dot)net>, "Postgresql Novice List" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: when to use char, varchar or text
Date: 2002-05-23 19:06:35
Message-ID: ALEKKHACBIAEBNBPFKPCCEJNDAAA.adamre@cox.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

> I'm making a table to hold a queue of incoming and outgoing email
> messages. I'm confused about using char, varchar or text fields - I
> imagine they are searchable (without and index) with decreasing speed
> from char to text. Is that right?
>
> The (very simplified) column setup I have includes:
> to varchar(200)
> from varchar(200)
> cc varchar(200)
> subject text
> body text

I have similar tables on my system, they store literally millions of
records. Do you know how long it takes to search this table? Forget it.

You'll find searching a lot easier with something like this:

Email
-----
id SERIAL PRIMARY KEY
body text not null
subject text not null
sent datetime
received datetime
priority int2 not null
errorcode int2 not null
index(sent)
index(received)
index(priority)

Email_Header
------------
id SERIAL PRIMARY KEY
emailid int4 references Email.id
name varchar(64) not null (Probably needs to be larger)
data varchar(200) not null
index(emailid)
index(name)
index(data)

This way, you associate email headers with the message, multiple "To"s can
be subverted into multiple header records. It is a lot easier to search
this kind of structure, IMHO. You can also search on sent / received to
determine which type of email it is, outgoing or incoming. Priority can be
used to get more important outgoing emails out first. Errorcode may be
useful if you're storing the SMTP results of an emailing. (ie. If you want
to remember why an email failed).

Adam

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Ron Johnson 2002-05-23 20:13:03 Re: query problem - get count in related table
Previous Message Rory Campbell-Lange 2002-05-23 17:31:04 query problem - get count in related table