Re: Toast,bytea, Text -blob all confusing

On Thu, Aug 23, 2001 at 01:09:14PM -0400, jason(dot)ory(at)ndchealth(dot)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