Thread: underlying structure: varchar vs. text

I'm trying to store anywhere from a few words to a half page of text as a
field in my table. A fellow db programmer told me that better than using a
varchar is to break the message up into n pieces of size m (let's say m=100),


and make n (or n+1) varchar(m)s, and relate them together.

He is worried that varchars allocate and hold space.
I'm not sure if that is actually more efficient, nor do I know the limit
of how big you can make a varchar.

Additionally, at what size is it practical to use a text type?

I'm running postgreql 7.0.3

Thank you,
b





 -----  Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web  -----
  http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups
   NewsOne.Net prohibits users from posting spam.  If this or other posts
made through NewsOne.Net violate posting guidelines, email abuse@newsone.net

> I'm trying to store anywhere from a few words to a half page of text as a
> field in my table. A fellow db programmer told me that better than using a
> varchar is to break the message up into n pieces of size m (let's say
m=100),
>
> and make n (or n+1) varchar(m)s, and relate them together.

If the text needs to be stored together, there isn't anything wrong with
using a text type field. Keep in mind that in 7.0.3 there is an 8k limit on
text fields (unless you increased it at compile time) and that in any
PostgreSQL you can't directly index text fields (though there is some stuff
in contrib that can help)..

> He is worried that varchars allocate and hold space.

Do you mean many varchar fields will take up more space than a single text
field both holding the same information? I don't know for sure but I'd say
that's true -- it's probably a pretty small different though (well,
depending on what the total amount of data we're talking about is).

> I'm not sure if that is actually more efficient, nor do I know the limit
> of how big you can make a varchar.

I think I remember Tom saying it was around 2100 bytes. I could be pretty
far off on that though.

Oh -- Update to 7.1 if you can, it's *really* nice...

-Mitch

"Mitch Vincent" <mitch@venux.net> writes:
>> I'm not sure if that is actually more efficient, nor do I know the limit
>> of how big you can make a varchar.

> I think I remember Tom saying it was around 2100 bytes. I could be pretty
> far off on that though.

In 7.1 there's no real limit.  (I think the parser arbitrarily insists
that the N in varchar(N) be no more than 10 million, just on the theory
that it's probably a typo if you wrote more.  That's easily changed if
you really have a use for "varchar(100000000)" as opposed to just plain
"text" ... but offhand I can't see what it would be.  Text is the
appropriate type to use if you don't have a specific width limit in
mind.)

As far as efficiency goes, the only difference between text and
varchar() is the extra function call involved in applying varchar's
length limit check just before inserting or updating a row.  Physical
storage is the same.

The 2100 number refers to the maximum length of text that can be
indexed.  That hasn't moved much in 7.1, but I can't really see the
value of a direct index on megabyte-sized text values anyway...

            regards, tom lane