i'm kind'a jumping in here, after being gone for a week and reading my
emails from the bottom up. . .
just fyi, in our application we needed a field to record internal notes.
we are a call center tracking service calls on computers of all things. .
. these internal notes are recorded by our phone support reps in each
service call as activity happens. i had initially used TEXT, because i
would never know the size the field could ultimately be. but after moving
the data into postgres, i realized the limitations of TEXT (yep, there WAS
a limit!). so i re-designed the table to use VARCHAR(240). now, when we
input notes, we have a "limit" of 240 (roughly) characters. when we fill
one field, the front-end just quickly adds another, and automatically
tracks the user name and time input (a nifty feature we didn't have before.
. .). so now if we look at notes for one service call, there may be 30,000
characters -- but they will be displayed in simultaneous boxes of 240. and
sorting through the varchar fields is lightning fast -- even searching for
a particular text string! we now have over 250,000 records in that table.
maybe i have helped provide some ideas?
jt
-----Original Message-----
From: Jan Wieck [SMTP:[EMAIL PROTECTED]]
Sent: Saturday, March 11, 2000 9:49 PM
To: Chris Gokey
Cc: Paul Dlug; [EMAIL PROTECTED]
Subject: Re: [GENERAL] Max Length for VARCHAR?
> Thanks for your reply.
>
> So it there any performance gained by using varchar as opposed to text?
> If so, then I prefer to use varchar and give it a upper limit, but I
can't
> find this upper limit in the documentation. Does anyone know what this
is?
There is no noticeable speed difference between VARCHAR and
TEXT. They are binary compatible types and handled nearly
identically by the system.
VARCHAR is actually limited to 8104 bytes. This is because it
is the absolute maximum size for a SINGLE COLUMN TABLE in a
standard 8K-blocksize Postgres installation.
TEXT cannot exceed the blocksize limit too, so it silently
will have the same limitations as VARCHAR, and you'll see
your queries fail if the value to be stored beeing longer.
There is no clean solution available at this time, except
splitting the text to be stored into several smaller chunks
at application level. Increasing the default blocksize will
have side effects you definitely don't want to have, so keep
your hands off please!
After 7.0 is out, I'll continue the TOAST project. This will
break the size limits for variable size attributes and make
them virtually unlimited (new limits will be based on
available per process memory, so U can create more swap to
increase it). Stay tuned!
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= [EMAIL PROTECTED] (Jan Wieck) #
