Re: Toast,bytea, Text -blob all confusing

On Thu, Aug 23, 2001 at 01:09:14PM -0400, jason.ory@ndchealth.com wrote:
> I'm trying my best to convert from MySQL to PgSQL but I cant get a good
> answer about 
> certian questions. It was an easy task in mysql but all this talk about
> , text , toast and bytea is just confusing me.
> I cant get a clear picture of any of this,from the book from Bruce, the
> e-mail archives. Ive looked all i can,
> with every keyword i can think of from years past. Here is my situation.
> 
> 
> WHAT I WAS DOING IN MYSQL
> Via the web my clients are uploading basic  text/data files, sometimes >
> than 30MB. In the past ,via CGI I have been parsing the file
> into one STL string, using mysql_escape_string to escape it and then using
> an INSERT  to place the 
>    ,\'+"stlstring+"\' ,    into a BLOB column. 
> I dont want to use a temp. file anywhere. The data will always be passed via
> the database and buffers for certian reasons.
> 
> 
> THIS IS WHAT I CANT SEEM TO FIGURE OUT IN POSTGRESQL
> 1. I cant get a clear answer on what kind of data type to use for my large
> text string?  TEXT, ???, ??? or something about TOAST
> I have seen in the e-mail archive but cant find any documentaion?

TOAST is just a name for the mechanism/feature that is used in Postgres
>= 7.1 to overcome the (32 KB if I recall correctly) limit on the row
size in previous versions. It's completely transparent to the
programmer, i.e. if you use TEXT, for instance, you can have a row up to
1 GB in size (which is probably not practical) theoretically. The
advantage over using BLOBs is that you can search this field (with 30 MB
fieldis, if you have a few of them, this is probably not practical
either so you'd probably want to consider some full text indexing
mechanism). I'd use TEXT for this reason.

> 
> 2. I've written my own escape method ,("cant find one for Pgsql") , BUT i
> don't know what 
> to escape and not to escape. So it keeps failing. I cand find any docs. on
> what to escape either?

Hm. I don't understand why the database (using MySQL or Postgres) would
make any difference there.

Hope it helps,

Frank