
Is there any good reason to use VARCHAR over TEXT for a string field? ie.
performance hits, etc.

Other than running into the row size limit problem, are there any large
storage / performance penalties of using TEXT for virtually all strings?

For ex. A phone number. This field probably wouldn't be bigger that 40
characters, but I can use TEXT and be sure that nothing gets truncated. Same
with a "name" field.

-rchit

Tue, 10 Jul 2001 14:37:22 -0700
Rachit Siamwalla <rachit@ensim.com>
varchar vs. text
Rachit Siamwalla <rachit@ensim.com> writes:
> Is there any good reason to use VARCHAR over TEXT for a string field?

The only reason to use VARCHAR is if you *want* the data to be truncated
at a specific length.  If you don't have a well-defined upper limit in
mind, I'd recommend TEXT.

			regards, tom lane

Tue, 10 Jul 2001 19:37:59 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: varchar vs. text 
Rachit Siamwalla wrote:
>
> Is there any good reason to use VARCHAR over TEXT for a string field? ie.
> performance hits, etc.
>
> Other than running into the row size limit problem, are there any large
> storage / performance penalties of using TEXT for virtually all strings?

    Er  -  what kind of "row size limit"? I remember vaguely that
    there was something the like in ancient releases, but  forgot
    the specific restrictions.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Wed, 11 Jul 2001 09:56:27 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: varchar vs. text
On Wed, Jul 11, 2001 at 09:56:27AM -0400, Jan Wieck wrote:
> Rachit Siamwalla wrote:
> >
> > Is there any good reason to use VARCHAR over TEXT for a string field? ie.
> > performance hits, etc.
> >
> > Other than running into the row size limit problem, are there any large
> > storage / performance penalties of using TEXT for virtually all strings?
> 
>     Er  -  what kind of "row size limit"? I remember vaguely that
>     there was something the like in ancient releases, but  forgot
>     the specific restrictions.

<FX: Sound of Jan whistling, looking around innocently>

Very good Jan. Yes, PostgreSQL certainly develops on Internet time, and
while TOAST may seem ancient news to you, it was only in the 7.1 release
(2001-04-13). Three months is a little early to start the 'Problem? What
problem?' campaign. Especially since some of the client libs (OBDC)
just caught up, last week. :-)

What Jan is so innocently not saying is described here:

http://www.ca.postgresql.org/projects/devel-toast.html

Jan not only solved the 'row size limit', he did it in a more general
way, solving lots of the follow on problems that come from putting large
fields into a table. Details at the above URL.

Ross

Wed, 11 Jul 2001 09:56:29 -0500
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: varchar vs. text
Still can't index those large toasted items -- not that I want to.
One interesting aspect is versioning of text documents where you want
them to be UNIQUE in regards to book development otherwise you have
the same document with 2 or more entries (more than a single version
number).  Poor example;  I know.
--
Rod Taylor

Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. You cannot imagine why you ever felt
otherwise.

----- Original Message -----
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: "Jan Wieck" <JanWieck@Yahoo.com>
Cc: "Rachit Siamwalla" <rachit@ensim.com>;
<pgsql-hackers@postgresql.org>
Sent: Wednesday, July 11, 2001 10:56 AM
Subject: Re: [HACKERS] varchar vs. text


> On Wed, Jul 11, 2001 at 09:56:27AM -0400, Jan Wieck wrote:
> > Rachit Siamwalla wrote:
> > >
> > > Is there any good reason to use VARCHAR over TEXT for a string
field? ie.
> > > performance hits, etc.
> > >
> > > Other than running into the row size limit problem, are there
any large
> > > storage / performance penalties of using TEXT for virtually all
strings?
> >
> >     Er  -  what kind of "row size limit"? I remember vaguely that
> >     there was something the like in ancient releases, but  forgot
> >     the specific restrictions.
>
> <FX: Sound of Jan whistling, looking around innocently>
>
> Very good Jan. Yes, PostgreSQL certainly develops on Internet time,
and
> while TOAST may seem ancient news to you, it was only in the 7.1
release
> (2001-04-13). Three months is a little early to start the 'Problem?
What
> problem?' campaign. Especially since some of the client libs (OBDC)
> just caught up, last week. :-)
>
> What Jan is so innocently not saying is described here:
>
> http://www.ca.postgresql.org/projects/devel-toast.html
>
> Jan not only solved the 'row size limit', he did it in a more
general
> way, solving lots of the follow on problems that come from putting
large
> fields into a table. Details at the above URL.
>
> Ross
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


Wed, 11 Jul 2001 11:14:32 -0400
"Rod Taylor" <rbt@barchord.com>
Re: varchar vs. text
Ross J. Reedstrom wrote:
> On Wed, Jul 11, 2001 at 09:56:27AM -0400, Jan Wieck wrote:
> > Rachit Siamwalla wrote:
> > >
> > > Is there any good reason to use VARCHAR over TEXT for a string field? ie.
> > > performance hits, etc.
> > >
> > > Other than running into the row size limit problem, are there any large
> > > storage / performance penalties of using TEXT for virtually all strings?
> > 
> >     Er  -  what kind of "row size limit"? I remember vaguely that
> >     there was something the like in ancient releases, but  forgot
> >     the specific restrictions.
> 
> <FX: Sound of Jan whistling, looking around innocently>
> 
> Very good Jan. Yes, PostgreSQL certainly develops on Internet time, and
> while TOAST may seem ancient news to you, it was only in the 7.1 release
> (2001-04-13). Three months is a little early to start the 'Problem? What
> problem?' campaign. Especially since some of the client libs (OBDC)
> just caught up, last week. :-)

    You're absotulely right, including the whistling ;-)

    I just couldn't resist, was too temping. And since I was sure
    there'll be more informative responses either way, why not?


Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Wed, 11 Jul 2001 12:17:12 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: varchar vs. text