Hi Jan,
We had something similiar to this in the past. The entire text was broken
down into lines (80 characters each) and stored as individual rows in a
table. The problem with this approach was that you can't search for phrases
that span rows, so I'd like to be able to store the entire text inside 1 row
and be able to search it. Oracle has a LONG datatype which provides this kind
of funcionality. I was hoping that PostgreSQL had something similiar. We are
rewriting portions of our legacy system using Java and trying to provide as
much portability to RDBMSs as possible. Although our main production machine
is Oracle, we'd like to make this run under PostgreSQL as well. I guess our
only option is to go back to breaking things up
into lines again (atleast for the PostgreSQL implementation) and providing
special code that works for this database. I just hate cluttering code with
special conditions (i.e., if
System.getProperty("DATABASE").equals("postgreSQL") then
do the following).
Thanks for your response.
Chris
JT Kirkpatrick wrote:
>
> 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) #
>
>
--
Christopher D. Gokey, Raytheon ITSS, NASA/GCMD
18 Martin Road, Shelburne Falls, MA 01370
Phone: Voice (413) 625-8129 / FAX 208-248-9055
[EMAIL PROTECTED] / http://gcmd.nasa.gov
