Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group