Re: Re: Examples of using bytea type

On Fri, 20 Jul 2001, ljb wrote:

> rfchurch(at)hotmail(dot)com wrote:
> >
> >The current PostgreSQL documentation is does not discuss the bytea type at
> >all.
> >
> >Does anyone have indepth examples of using this type in SQL?
> >
> >I know expressing BLOb data in SQL is awkward and would generate huge text
> >files, but is there a standard method of using escape sequences to express
> >blobs or something similar to MIME?
> >
> >There surely must be something like that.
>
> The bytea type is undocumented, so I suppose you should use it at your
> own risk, since it may vanish (like lztext did at 7.1).
>
> Keep in mind that unless you use binary cursors, what you get back from a
> query on a bytea type is a string with "\nnn" sequences mixed with text in
> it, which your application would then need to decode to get the actual
> data. For inserting, you must convert your data to \nnn sequences (\\000
> for 0), all in one string, to use in an insert on a bytea (there are no
> binary cursors for insert). So it's a lot of trouble, and might not be
> worth the effort. If you have to encode/decode in your application anyway,
> I wonder if it wouldn't be be easier to do Base64 or Ascii85 coding and use
> the "text" type in PostgreSQL instead of bytea?

Bytea IS documented in 7.2-devel and is definitely not going away. Major
benefit of bytea over base64/whatever encoding is storage space. Need to
escape/unescape input data depends on your frontend client. Perl DBD::Pg
will do it transparently without needing to do \\ escapes.

In 7.2, there will also be functions base64_encode/base64_decode for bytea
types. In 7.1, they exist under contrib/pg_crypto

-alex