Thread: when to use char, varchar or text

when to use char, varchar or text

From

Rory Campbell-Lange

Date:

23 May 2002, 11:05:21

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

Should I make the columns all of type text so that large numbers of 'to'
recipients can be supported?

Some general advice greatly appreciated.

p.s. I expect the number of rows in this table to be less than 5000.

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

Re: when to use char, varchar or text

From

"Joshua b. Jore"

Date:

23 May 2002, 11:21:08

I'm not sure what you are implementing but as I recall any single address
may be longer than 200 characters. That may not be terribly likely in
practice but it is allowed and your mail program would be expected to
allow it. I also wonder whether you mean to concatenate the addresses via
', ' or how you are going to store multiple To, CC, etc recipients.

The nature of where you are working advocates for the text datatype.

Joshua b. Jore
http://www.greentechnologist.org

On Thu, 23 May 2002, Rory Campbell-Lange wrote:

> 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
>
> Should I make the columns all of type text so that large numbers of 'to'
> recipients can be supported?
>
> Some general advice greatly appreciated.
>
> p.s. I expect the number of rows in this table to be less than 5000.
>
> --
> Rory Campbell-Lange
> <rory@campbell-lange.net>
> <www.campbell-lange.net>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Re: when to use char, varchar or text

From

"Adam Erickson"

Date:

23 May 2002, 18:33:14

> 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